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?
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.
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.
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.
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!
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.
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.
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?
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.