Excel to formatted text

Factual Questions
1

I need to turn Excel files into text files. I can save as tab delimited text, only that only keeps the columns if the fields in each column are the same size. Or I can save as space delimited Formatted Text. This is exactly what I need but for one thing: There is a 240 character limit. If you have more than 240 characters the excess characters go to the end of the file. They don’t wrap around to the next line (which isn’t desirable, but workable); they go to the end.

Is there any way in Excel 2003 to save a file as text such that each column remains in a column (unlike tab delimited text), and does not have a limit (or at least has a more reasonable limit) to the length of the record?

2

Is there a reason you’re not using comma as a delimiter? Save as CSV?

3

That’s what I am wondering. I think you can use any delimiter you want if a comma is a problem. That is the right way to do it.

4

I need each field to be in a fixed position so that I can say, for example, the address begins in position 60 for 30 characters.

Can I change a CSV into a fixed-format text file?

5

Yes. You may need another tool in the middle though. Access can take in a comma delimited file and the export it again as a fixed width file. Access has more features for that sort of thing.

6

Aha. I haven’t used Access. (Well, only superficially; and that was years ago.) But I do see it’s on the computer. So what I’ll need to do is open a file in Excel, save it as CSV, open it in Access, and then I can save it as fixed-format text so I can use the file as input to my program.

7

You can import an Excel file directly into Access.

8

You could, but not in Excel that I know of; I would pursue Shagnasty’s suggestion first if you have Access.

9

Thanks, All. I’ll do some experimenting with Access.

10

Okay, I finally had a chance to try Access. I thought I got it with Office for Macs, but I didn’t so I’m trying on the work computer. It’s a bit of a process importing an Excel file. I’d hoped it would be a matter of ‘Open Excel’ and ‘Save As’. Nope. I had to open a new database, Import the Excel file, start up the Wizard, type in the starting position and length of each field (it didn’t put the arrows in the obvious places, but made each field 255 bytes long), and then export it as text. It’s going to take some practice (I just tried another file and I didn’t get the option to define the fields), and I have a feeling I’ll have to go through the whole process for every file. (Unless there’s a way to streamline it?)

Another issue is that I want most fields to be left-justified but other fields to be right-justified. I didn’t see how to make that happen.

11

I’d look into whatever program you’re using that only reads fixed format.

I have no idea what it might be, but I’d suspect that either

  1. there’s a way to read csv or tab-delimited files

OR

  1. it can be rewritten to do such a thing.

But, then again, maybe not. Is it old software, written in Fortran or something?

12

[QUOTE=Trunk]
But, then again, maybe not. Is it old software, written in Fortran or something?
[/QUOTE]

Yeah, it’s pretty old. Easytrieve Plus. Basically it’s like this:



FILEA
NAME            1       40    A
ADDRESS1       41       30    A
ADDRESS2       71       30    A
.
.
.

You tell Easytrieve the parameter name, its starting position, its length, and whether it’s alpha or numeric. In the above example NAME starts in position 1 and is 40 bytes long and is alpha (not numeric).

I’ve tried again to import an Excel spreadsheet and get to the point where I can define the width of the fields, but Access isn’t giving me that option again. I must have done something differently the first time, but darned if I know what it is!

13

[QUOTE=Johnny L.A.]
I’ve tried again to import an Excel spreadsheet and get to the point where I can define the width of the fields, but Access isn’t giving me that option again. I must have done something differently the first time, but darned if I know what it is!
[/QUOTE]

Ignore that. You get to define field widths when you export the file. (I said I’d need to practice.)

I still need to find out how to justify fields.

14

Can you import an .xls file into Access instead of converting the .xls into a .csv first? If so, you might be able to automatically keep the justifications and it might automatically parse the excel columns into separate database columns without all the work defining it. I have no idea if you can or not, I’m just saying that it would be worth looking into.

15

[QUOTE=StinkyBurrito]
Can you import an .xls file into Access instead of converting the .xls into a .csv first? If so, you might be able to automatically keep the justifications and it might automatically parse the excel columns into separate database columns without all the work defining it. I have no idea if you can or not, I’m just saying that it would be worth looking into.
[/QUOTE]

Yes, I was importing the .xls files into Access. Unfortunately the justifications were not kept.

16

That’s because “justification” is not a concept that is understood by a database. Databases store data, justification is a formatting issue. If you want things “justified” a certain way, you’re going to have to write a query which will pad the field in some way.

For example, I’ve got a field with width 10 and I want it right justified, I would concatenate 10 spaces to the left of the field, and then use the rightmost 10 spaces as the output. Make sense? Let’s try it with 0s (since spaces won’t work here).

Number is 123. Field is 10 characters wide. I append 0000000000 to 123, making a total of 13 characters (0000000000123). I then take the rightmost 10 of those (0000000123) to put in my output.

It’s not quick and easy, but it does the trick if you need your DB to format things for you.

17

[QUOTE=crazyjoe]
Number is 123. Field is 10 characters wide. I append 0000000000 to 123, making a total of 13 characters (0000000000123). I then take the rightmost 10 of those (0000000123) to put in my output.

It’s not quick and easy, but it does the trick if you need your DB to format things for you.
[/QUOTE]

That’s exactly what I need: 00000123 (or 0000123, depending on which column it’s in).

If the field is numeric, then it’s easy. If it’s not numeric (i.e., spaces, dots, commas, etc.) then I’ll have to write Easytrieve code (or, more truthfully, use the code I’ve already written – but that’s a lot of code) to parse each field from the right and only move numeric characters. I’ve got that in a Proc that I wrote years ago, but it’s quicker and easier if the fields are numeric already.

So how do I zero-fill the values in Access, keeping in mind that I’ve only used Access infrequently, years ago, and not to d othis sort of thing?

18

[QUOTE=Johnny L.A.]
That’s exactly what I need: 00000123 (or 0000123, depending on which column it’s in).

If the field is numeric, then it’s easy. If it’s not numeric (i.e., spaces, dots, commas, etc.) then I’ll have to write Easytrieve code (or, more truthfully, use the code I’ve already written – but that’s a lot of code) to parse each field from the right and only move numeric characters. I’ve got that in a Proc that I wrote years ago, but it’s quicker and easier if the fields are numeric already.

So how do I zero-fill the values in Access, keeping in mind that I’ve only used Access infrequently, years ago, and not to d othis sort of thing?
[/QUOTE]

Well, I don’t do the GUI version of things, I use the SQL window. And ACCESS isn’t completly ANSI compliant when it coems to things like this, but here goes (lowercase indicates your table, column, or whatever, all keywords are in uppercase) Also, this is going to require that you set your field properties to character fields instead of numeric, as numeric will simply drop those leading zeros:

UPDATE table SET column = RIGHT(“0000000000”+column,10)

This will accomplish my example above. Alter it to suit your purposes. Looking up the access syntax should be as simple as checking string functions in the help. IIRC, Access might use | instead of + to concatenate strings, and might make you use RIGHT$ instead of RIGHT or something like that.

Hope this helps…

ETA it might be better if you create an empty table for your new records and INSERT into it from the original table. To do that you would say:

INSERT INTO newtable SELECT RIGHT(“0000000000”+column,10)
FROM oldtable

To do this you will have to make sure you specify a value in your insert statement for each column in the new table, so either just move the fields as is or convert them using the syntax above.

19

[QUOTE=crazyjoe]
Hope this helps…
[/QUOTE]

Eight hours into a ten-hour day on ‘my Friday’. I’ll have to look at this next week. Thanks!

20

search for “ask the excel” guy and post in that thread.