Re: Excel text export limit - 1024 per line (not cell), workaround
- From: Dave Peterson <petersod@xxxxxxxxxxxxxxxx>
- Date: Thu, 06 Aug 2009 18:33:28 -0500
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=015b01c32c5f%24b3d398d0%24a501280a%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
.
- Follow-Ups:
- References:
- Prev by Date: How do I resize the scroll bar
- Next by Date: Re: CELL FORMAT
- Previous by thread: Re: Excel text export limit - 1024 per line (not cell), workaround
- Next by thread: Re: Excel text export limit - 1024 per line (not cell), workaround
- Index(es):
Relevant Pages
|
