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.

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/groups?threadm=015...0a% 40phx.gbl

============================
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 wrote:

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

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" wrote:

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/groups?threadm=015...0a% 40phx.gbl

============================
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 wrote:

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

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 wrote:

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" wrote:

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/groups?threadm=015...0a% 40phx.gbl

============================
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 wrote:

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


--

Dave Peterson

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" wrote:

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 wrote:

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" wrote:

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/groups?threadm=015...0a% 40phx.gbl

============================
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 wrote:

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


--

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.



Dave wrote:

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" wrote:

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 wrote:

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" wrote:

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/groups?threadm=015...0a% 40phx.gbl

============================
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 wrote:

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


--

Dave Peterson


--

Dave Peterson