Fixed Field Length Export File (EXCEL XP [2002])

Topic

New Reply

I have been requested to create a fixed field text file from EXCEL. For example, if there are three columns of data, the first column is 20 characters, the second column is 35 characters and the last column is 10 characters in length. I need to generate a text file where the first record is 20 characters regardless of the actual length of the data in this field (i.e., by default the data must NOT exceed the 20 character length). The data is to be left justified [so if the first data field is actually 9 characters in length it will be followed by 11 characters of spacing in the text file]. This same logic applies to the data in next two columns. The next EXCEL row then creates a “new” line in the text file and the three fields in the “new” line follow the same logic.

I have spent a couple of hours experimenting with the EXCEL file save options for txt and csv files—but can’t generate a “fix field length text file with the data left justified in the field. I am wondering if I need to use one of the csv options and then use some sort of high powered text file editor to create the required export file with the left justified, fixed field length data elements.

Guidance and suggestions needed!! Thank you.

Reply | Quote

Replies

If your data is in Sheet1 starting in A1:C1 and going to row whatever, one relatively simple way is, on another sheet add the formula in Cell A1

=LEFT(Sheet1!A1&REPT(” “,35),35)&LEFT(Sheet1!B1&REPT(” “,20),20)&LEFT(Sheet1!C1&REPT(” “,10),10)

Then copy this down the column as many rows as is in Sheet 1

Then you can save this sheet as a text file. Of course if you want any delimiters between columns you will have to add them:

It could be automated if desired…
Steve

Reply | Quote

Steve,
This works great. Two additional questions, I will not always be creating the initial data file {sheet1 in your formula} but may be inheriting it via an EXCEL file e-mailed to me. For edit control purposes, say the one of the fields is too long on Sheet1, will your formula automatically truncate it and only capture the left justified maximum number of characters [say the field in Sheet1 is 37 characters, only the 35 left most justified characters will be captured by your formula]? I am uncertain as this point whether I should try to error trap in the event one of the fields on Sheet1 is outside the maximum field length established by the data processor.

Finally, I may be asked to create a field that contains one blank space to separate these 3 fields. Can this be done with your formula do I need another formula in another column with some sort of concatenation to insert to single space fields between Field 1 and 2 and then Field 2 and 3? THANKS.

Reply | Quote

Steve’s formula will truncate fields that are too long to the required number of characters. The modification below will add a space between the fields.


=LEFT(Sheet1!A1&REPT(" ",35),35)&" "&LEFT(Sheet1!B1&REPT(" ",20),20)&" "&LEFT(Sheet1!C1&REPT(" ",10),10)


Reply | Quote

Legare,
Thank you for prompt reply.

Reply | Quote