Converting an excel file to a flat file

[dmurray]

I've been trying to turn one of my excel spreadsheets into a flat file with no luck. Here are the steps I took:

1. I formatted the columns so they should all be right justified
2. I renamed the file as a prn (space delimited file)

When I bring up the new file in multi-edit, it cuts off after half the columns. The other half are wrapped underneath. Is there an easy way to turn an excel spreadsheet into a flat file?

 

[tactps]

I find that csv files work better than space delimited ones. Can you use csv for what you want?

 

[dmurray]

I can't use a csv file b/c I need the columns to be the same width everytime I convert an excel file to a flat file. It needs to be this way becasue I am importing data from the flat file to a database and the importer works off the column length.

 

[tactps]

Perhaps you need to convert each column to fixed width text.

Try using formulas like:

=text(a1,"000000") to convert numbers to a six digit text figure before saving as the flat file.

 

[MarkBoy]

Ok - I may be misunderstanding what you are saying, but have you actually saved it as a space-delimited file, or are you just renaming? Because I dont think just renaming it will work. You need to go to File - Save As, and change the Save As Type to the appropriate type.

If I misunderstood, my apologies.

 

[dmurray]

I've actually been going to "File/Save As, then I select the file to be saved as a .PRN.

 

[Oorang]

The Formatted Text (Space delimited) (*.prn) format saves only the text and values as they are displayed in cells of the active worksheet.
All rows are saved. If a row of cells contains more than 240 characters, characters beyond 240 wrap to a new line at the end of the converted file.

Cheap trick:

Convert to CSV
Open in notepad
Cut and paste into word
Use find/replace to change all commas to spaces.
Cut and paste back into notepad.
Save.

 

[dmurray]

Thanks, Changing it to a .csv file worked.