Excel text export limit - 1024 per line (not cell), workaround?

[Dave]

Hi there. In searching for 1024 I found a lot of comments mentioning that
Excel has a cell limitation of 1024 characters. What I'm running into is
that when performing an export from Excel into a delimited text file (csv)
that it has a 1024 characters per line limitation as well. Some of the files
I'm dealing with have lines of data (sheet has 40 columns) that have more
than this many characters, which causes it to wrap, which causes my import of
the data to then fail, as it's not ending/starting as I expected.

Is there any way around this export line limit? I've tested it on MSExcel
2000, 2003, and 2007 and always gotten the same result.

 

[Dave Peterson]

How are you exporting those long strings?

I used xl2003 and filled a bunch of cells (A1:C20) with this formula:
=REPT("a",1023)&"xxx"
Each cell's value is 1026 characters long.

I did a simple File|SaveAs (and used "CSV (MS-DOS)(*.csv)" as the "save as
type:" option.

I opened the .csv file in my favorite text editor (UltraEdit, actually). Each
line in the .csv file was 3080 characters (1026*3 + 2 (for the separating
comma).

===========
I'm guessing that you're not saving/exporting the file as a .CSV file. I'm
guessing that you're actually saving as a .PRN (fixed width fields) file.

If that's the case, ...

Saved from a previous post:

There's a limit of 240 characters per line when you do File|saveas and use .prn
files. So if your data wouldn't create a record that was longer than 240
characters, you can save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, you could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]

============================
If this is completely wrong, you may want to give more details on how you're
exporting the data and the version of excel you're using.

 

[Dave]

When you opened it in UltraEdit, did it show up as one line?

I am saving it as CSV - specifically what I am doing is taking a large
amount of data, which is in columns, and saving it in CSV so I can upload it
as a flat file. Using it for the past few months without issue, however
today I had one field that had 690 characters in it. This ended up pushing
the total export (which has a bunch of other columns with anything from zero
to 100 characters in each) to 1069 characters. Originally when I saw the
export wrapping around, I figured somehow an extra line break had gotten in
there. After review, I could not find it. I then noticed that it was
looping 45 characters over always...a little investigation led me to the 1024
conclusion.

 

[Dave Peterson]

Yes--one long line.

What text editor did you use to view the .csv file? Maybe it's a problem with
that text editor???

And are you sure that there are no linefeeds in any of those cells. Maybe
that's the problem???

If you think you have any, you could use:
select the range to fix (all the cells???)
Edit|replace
what: ctrl-j
with: (space character or hyphen or whatever you want)
replace all

Then do the SaveAs.

(I'd save as a normal workbook before I did this so I could reopen the .xls file
and have my original data available.)

 

[Dave]

I'm reviewing it in Notepad, and it's showing the data failing exactly where
it is in the system taking the text import (Informatica). I have confirmed
it's not a line feed nor carriage return. Replacing the cell contents with
nothing but a string of numbers (012345678901234567890 etc) has the exact
same effect after 1024 characters.

 

[Dave Peterson]

I think it's a limitation of notepad.

You could try importing a small portion of data into your application to see if
works ok.

Or you could search for a different text editor.

I've never used this one (that I recall), but NotePad+ (search google) has
gotten good reviews.

And Wordpad.exe looks like you can see the whole logical line on one physical
line.

I'm reviewing it in Notepad, and it's showing the data failing exactly where
it is in the system taking the text import (Informatica). I have confirmed
it's not a line feed nor carriage return. Replacing the cell contents with
nothing but a string of numbers (012345678901234567890 etc) has the exact
same effect after 1024 characters.

 

[Dave]

Having the same problem in the import of the data though (which uses a
program called Informatica), so it's not just Notepad. Notepad just happens
to show it. Oddly enough, Wordpad does not.

I think it's a limitation of notepad.

You could try importing a small portion of data into your application to see if
works ok.

Or you could search for a different text editor.

I've never used this one (that I recall), but NotePad+ (search google) has
gotten good reviews.

And Wordpad.exe looks like you can see the whole logical line on one physical
line.

<text snip>

 

[Dave Peterson]

Your data seems to follow the CSV rules nicely.

It's informatica that's having trouble. I bet you have no control how that
works. So you'll have to live by its rules.

And I bet that one of its rules is the length of strings it accepts (just a
guess).

 

[Dave]

So, that you're aware of, there's no 1024 per line limit then? It's odd I
see the break in Notepad (before I send the file on) at the exact point that
Informatica also reports the line error.

I'm more than willing to go with Informatica being the break point, but with
Notepad doing the same thing, on multiple computers, it points to Excel as
the error point.

 

[Dave Peterson]

Try viewing the file in WordPad on multiple computers. What do you see?

I think it's a limitation of NotePad and Informatica. But if you don't believe
that, search for a hex editor that will show you what's in those strings.

 

[Dave]

(Sorry on slow response, too many plates, not enough sticks to spin them on)

Thanks, I will give that a try. Maybe I'll find something. I wish I could
find the exact failure so I could mitigate it!

 

[Dave Peterson]

I think the failure is in those programs that won't accept the long lines--but
if you look through the documentation, you may find that it's not a
failure--it's a feature.

(Sorry on slow response, too many plates, not enough sticks to spin them on)

Thanks, I will give that a try. Maybe I'll find something. I wish I could
find the exact failure so I could mitigate it!