Text data imports, linefeed problems.

Wade G. Pemberton

Help!:

I use javascript to format repetitive data from a web page input FORM
into a long string , and save it as lines of comma delimited data in a
text file on a Unix server. The text file is meant to be downloaded
to MS Excel on the users' computers. Several such files will be
downloaded and appended into the Excel file. Problem: The text file
looks good when viewed offline by any text reader, but Excel will not
import it without problems with the lines.

Sample data, stored as one string:

Blue Angelfish,Holacanthus bermdensis,n/a,1,PC,1900,n/a,4/3/2004,Palm
Beach,2645.702,8001.279,80,60,1,40,40,yes,Cross Current Barge
French Angelfish,Pomacanthus paru,n/a,2,PC,1900,n/a,4/3/2004,Palm
Beach,2645.702,8001.279,80,60,1,40,40,yes,Cross Current Barge

etc. for approximately 40 more lines.

MS EXCEL will import the data into the correct columns, but each row
(line) will be followed by a blank row, then another row of data
then another blank row. Additionally, while in this funk of blank
lines, Excel won't allow a 2nd text similar file to be imported.

The problem appears to be linefeeds. The long data string above is
broken into seperate lines of output text in the javascript by use of
the special character "\n". The server seems to handle this
successfully, as the output file looks like neat lines of text, with
commas between the data entries. However, in fact, the last ascii
character in each line is followed by a single blank whitespace (the
linefeed?). EXCEL for some reason makes this into an entire new row,
completely blank, and stops further importing of additional text files.

I've asked the Excel Forums what to do and they say "just fix the text",
so I'm back to javascript for ideas. I can't change the server's
habits, so I back to the javascript.

I've also tried the special javascript character "\r" when formatting
the linebreaks, and even tried the windows linefeed of "\r\n", all to
no avail. The serverformats the text into seperate lines (rows) with
a blank whitespace at the end of each line, and EXCEL for some reason
makes this into an entire blank row. I can't use the escape function
on the special characters, since escape also affects the white spaces,
commas, apostrophes, etc, and besides, Excel cannot UnEscape them.

This linefeed on Unix/Windows must be a common problem. Just point me
toward the solution.

Wade

Jul 23 '05 #1

mscir

Wade G. Pemberton wrote:

Help!:

I use javascript to format repetitive data from a web page input FORM
into a long string , and save it as lines of comma delimited data in a
text file on a Unix server. The text file is meant to be downloaded
to MS Excel on the users' computers. Several such files will be
downloaded and appended into the Excel file. Problem: The text file
looks good when viewed offline by any text reader, but Excel will not
import it without problems with the lines.

Sample data, stored as one string:

Blue Angelfish,Holacanthus bermdensis,n/a,1,PC,1900,n/a,4/3/2004,Palm
Beach,2645.702,8001.279,80,60,1,40,40,yes,Cross Current Barge
French Angelfish,Pomacanthus paru,n/a,2,PC,1900,n/a,4/3/2004,Palm
Beach,2645.702,8001.279,80,60,1,40,40,yes,Cross Current Barge

etc. for approximately 40 more lines.

MS EXCEL will import the data into the correct columns, but each row
(line) will be followed by a blank row, then another row of data
then another blank row. Additionally, while in this funk of blank
lines, Excel won't allow a 2nd text similar file to be imported.

The problem appears to be linefeeds. The long data string above is
broken into seperate lines of output text in the javascript by use of
the special character "\n". The server seems to handle this
successfully, as the output file looks like neat lines of text, with
commas between the data entries. However, in fact, the last ascii
character in each line is followed by a single blank whitespace (the
linefeed?). EXCEL for some reason makes this into an entire new row,
completely blank, and stops further importing of additional text files.

I've asked the Excel Forums what to do and they say "just fix the text",
so I'm back to javascript for ideas. I can't change the server's
habits, so I back to the javascript.

I've also tried the special javascript character "\r" when formatting
the linebreaks, and even tried the windows linefeed of "\r\n", all to
no avail. The serverformats the text into seperate lines (rows) with
a blank whitespace at the end of each line, and EXCEL for some reason
makes this into an entire blank row. I can't use the escape function
on the special characters, since escape also affects the white spaces,
commas, apostrophes, etc, and besides, Excel cannot UnEscape them.

This linefeed on Unix/Windows must be a common problem. Just point me
toward the solution.

Here are 2 more things to try:

- use a form feed: \f
- use writeln instead of write

MIke

Jul 23 '05 #2

Randy Webb

Wade G. Pemberton wrote:

<--snip-->

I've also tried the special javascript character "\r" when formatting
the linebreaks, and even tried the windows linefeed of "\r\n", all to
no avail. The serverformats the text into seperate lines (rows) with
a blank whitespace at the end of each line, and EXCEL for some reason
makes this into an entire blank row. I can't use the escape function
on the special characters, since escape also affects the white spaces,
commas, apostrophes, etc, and besides, Excel cannot UnEscape them.

If the server is inserting the extra line feed, nothing you do in
Javascript, save not putting any at all and let the server add them, is
going to make any difference. The simple solution is to fix it on the
server instead of attempting to put a javascript band-aid on it.

--
Randy
Chance Favors The Prepared Mind
comp.lang.javascript FAQ - http://jibbering.com/faq/

Jul 23 '05 #3

Wade G. Pemberton

Randy Webb wrote:

Wade G. Pemberton wrote:

<--snip-->

<snip>

If the server is inserting the extra line feed, nothing you do in
Javascript, save not putting any at all and let the server add them, is
going to make any difference.
I'm not sure it's putting in an extra line feed. I've taken a long string
and broken it into "lines" of comma delimited data. Excel may be seeing it
as one big string. Because of the linefeed(s), it's at least wrapping the
text into rows, and correctly columnizing it. However, it may be still
seeing it as one long string, with whatever issues that brings, rather than
multiple records.
The simple solution is to fix it on the
server instead of attempting to put a javascript band-aid on it.

I'd really have liked it to work in Excel. It looks

As for fixing it on the server, I'm not serverside literate. Perl is not a
language I can program, I'm unsure if running a javascript or Vbasic is
allowed, and I'm unsure how to have the users fire it if it was permissable.

Ideas? Links to someone who's solved this before?

Wade

Jul 23 '05 #4