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?
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?”
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.
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.
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.
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?
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.)
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?