Excel 2007 -- Saving file as text

I finally have access to Excel 2007, so I can open files with more than 65,000 records.

What I need to do is save an Excel file, using Excel 2007, as a fixed-format text file. (Right now I’m saving as .csv and importing into Access, then exporting as fixed-format text by defining the starting position of each column, and its length.)

I’ve tried saving as Text (Tab delimited), Unicode text, Formatted text (Space delimited), and Text (MS-DOS). Three of the four do not preserve the column positions. Formatted text does preserve the column positions, but apparently the file is too ‘wide’ and the last five columns are chopped off and appended to the end of the text file.

Is there a way to save a text file from Excel 2007 such that column positions are preserved AND the entire record is on one record instead of being chopped and appended?

I usually do this kind of stuff in VBA.

I don’t know VBA.

Okay, give me a little bit and I’ll get you started.

On second thought, can you give me a little more information about what you’re trying to do? What do you mean, exactly, by “preserve the column positions?”

General information:

I need the file in a fixed-format text file because I use a sequential program (Easytrieve) to process it. The program reads the data from specified positions. (e.g., Field 1 starts in position 6 and is 12 bytes long, Field 2 starts in position 18 and is 40 bytes long, etc.)

I’ve been using Excel 2003, but this particular file is too large. So I’ve been running an Easytrieve to split the carrot-delimited text file into two smaller files. Then I import each text file into Excel, specifying carrot-delimited. After deleting a couple of columns, filling a couple of columns, and getting rid of double-quotes and commas, I save them as .csv files. The .csv files are imported into Access, then exported as fixed-format text. One text file is appended to the other, so I have a single file again. Now that I have a fixed format text file, I can run it through my programs.

If I could import the entire file into Excell 2007 and save it as fixed-format text, it would save a bit of monkey-motion.

Are you padding fields with something similar to this:

=LEFT(A1,15)&REPT(" ",15-LEN(A1))

No, I don’t know how to do that.

Also, I’d have to pad every column. It would be less work to save the file as .csv and do the access thing (though with only one file, rather than splitting it, etc.).

Yeah, true enough. On the plus side, if you’re continually putting things into the same format, you could set set up once and then forget about it.

I’m guessing your major beef is that Excel isn’t preserving the space padding when you import the caret delimited file. Does this input file have the spaces in it? Does it have / can you use a text qualifier? Like: "John Smith "^“whatever ^”…

When I import the carrot-delimited file into Excel, everything is in its proper Excel column. The problem is exporting as text. Formatted text (Space delimited) preserves the column positions in the text file excatly as I want them. Only Excel seems to have a limitation on how long a text record can be. If a column or columns exceeds that limit, then they are chopped off and appended to the bottom of the text file. Example:


1000001     JOHN SMITH                    123 FAKE ST
1000013     BIGGLES AERO SERVICE          1313 AIRPORT RD
1234508     DUCK DUCK GOOSE               29367 S WHATEVER RD
ANYTOWN       CA   90034
HITMAN        NJ   02003
NOWHERE       IA   51378


In the example, pretend that Excel has a record length limit (on text output) such that the City, State, and ZIP fields exceed that limit. When outputting as Formatted text (Space delimited), those columns are shifted to the bottom of the file, instead of being on the same record as the first columns.

Of course if I had shorter records, then it wouldn’t be a problem.

It seems strange that 1024-byte text records have been used since I started writing programs (on a mainframe), but Excel can’t seem to write a text record longer than 230-some bytes. (In the file I’m experimenting with the five columns on the end start at position 237.)

I don’t use 2007 and I don’t have all my notes in front of me, but I believe we use the .prn format for this. IIRC, you have to have the column widths in Excel set to the correct width (that you want your output to have) before you export.

I’ve not tried it with files as large as yours, so I can’t say for sure that it wouldn’t have the same problem, though.

Good luck!

Yes, saving as Formatted text (Space delimited) writes a .prn file. This is the one that has the truncated and appended records.

Check these out:

http://support.microsoft.com/kb/249885

http://www.meadinkent.co.uk/XLexport-text1.htm

Both of those look like they’ll do what I want them to do, but I have no idea how to use them.

FWIW, here is the map I made for this particular file for exporting from Access. The first column is what’s in the column, the second is the starting position in the output file (which will be used as input for the Easytrieve), and the third column is the length. (Other files have different maps.)


ID	1	5
A/R	6	12
Div	18	5
Name1	23	40
Name2	63	40
Addr1	103	30
Addr2	133	30
City	163	20
State	183	20
ZIP   	203	10
Balance	213	12
Current	225	12
01-30	237	12
31-60	249	12
61-90	261	12
91-120	273	12
120+	285	12


The ID column comes from Access. As I said, I import/export using Access, and Access inserts the ID column. That column is not in the original input. I don’t need any ‘padding’ between the columns, because the file is not meant to be read by a human. Everything just needs to be in the stated positions.

But again, I don’t know how to use the information you posted.

Say you have the columns you listed without ID.

Then in column Q ON ROW 1 create this formula:

=A1 & REPT(" “,12-LEN(A1)) & B1 & REPT(” “,5-LEN(B1)) & C1 & REPT(” “,40-LEN(C1)) & D1 & REPT(” “,40-LEN(D1)) & E1 & REPT(” “,30-LEN(E1)) & F1 & REPT(” “,30-LEN(F1)) & G1 & REPT(” “,20-LEN(G1)) & H1 & REPT(” “,20-LEN(H1)) & I1 & REPT(” “,10-LEN(I1)) & J1 & REPT(” “,12-LEN(J1)) & K1 & REPT(” “,12-LEN(K1)) & L1 & REPT(” “,12-LEN(L1)) & M1 & REPT(” “,12-LEN(M1)) & N1 & REPT(” “,12-LEN(N1)) & O1 & REPT(” “,12-LEN(O1)) & P1 & REPT(” ",12-LEN(P1))

In q1 you will have a fixed length filled unlimited text string.

OK, I c&p’d the code (Thanks!) into Q1. It says #VALUE!

Another question:

Q1 should contain all of the rest of the columns, in a single cell. Right? So I would copy and paste the formula for all 90,000 records to get the whole file. Then I can copy all of that and Paste special => Values, and delete columns A through P. Then I’ll have column A with all of the data in it. Is that correct?

But .prn has a 240-byte limit, and the string would be 296 bytes long. Won’t it still truncate and append?

What values are in each column?

I just tried what I posted. It looks like when the formula is copied into the other 90,000+ cells, it works. (But columns J through P need to be formatted as Numeric, no commas, two decimal places, leading negative indicators.)

The #VALUE! cell is Q1. Row 1 is the header record: SOURCE_CUSTOMER_ID, CUST_BU_ID, CUSTOMER_NAME, NAME 2, CUSTOMER_ADDRESS_1, CUSTOMER_ADDRESS_2, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP, AGING_BALANCE, NOT_DUE, DUE_00_30, DUE_31_60, DUE_61_90, DUE_91_120, DUE_OVER_120 (No commas, as they are each in their own cell.)

Yeah the header in A is longer than the field length.

For numbers substitute say & TEXT(j8,“000000000.00”)
for & J8 & REPT(" ",12-LEN(J8))
for each numeric

“000000000.00” is the format

I shortened the headers to fit the fields. (I just truncate them when I export from Access.) That got me the header record.

I copied the formatting and pasted at the end of the formula, changing each column name. I’ve managed to screw something up, and some columns are missing. But I can go back and try again. (But I’m on Remote Desktop Connection from home, attaching to my computer in the office, and using Remote Desktop Connection on my desktop to access where Excel 2007 lives. Plus it’s a 90,000+ record file, so things are a little slow.)

Negative numbers are throwing things off, however. That is, the formula segment & TEXT(j8,“000000000.00”) yields a 12-byte field as it is depicted. When the number is negative, there is a leading negative sign and the amount is shifted. Of course, they can’t be shifted.

But let’s say we get the bugs worked out and I have a 296-byte record in a single cell. Won’t it be truncated and appended upon exporting to .prn?