When converting an .xls file to .csv I get too many commas at the.
 

When attempting to convert an .xls format to .csv format using Excel to do
the conversion process, I am getting too many commas at the end of a number
of lines (where there is NO data at all). It may be linked to some preview,
I see some resemblance between 'web page preview' and the resultant output.
Would like to just save a file with variable numbers of (and items of) data
on separate lines of the same file, and for the .csv file to have the correct
number of commas per line. How can this be achieved?

Are the commas separated by one or more spaces? If so, someone probably
"cleared" the cells by hitting the spacebar instead of using Del/Delete.
This leaves a space character in the cell which XL interprets as data to
be saved.



In article ,
One-eyed-Ian wrote:

When attempting to convert an .xls format to .csv format using Excel to do
the conversion process, I am getting too many commas at the end of a number
of lines (where there is NO data at all). It may be linked to some preview,
I see some resemblance between 'web page preview' and the resultant output.
Would like to just save a file with variable numbers of (and items of) data
on separate lines of the same file, and for the .csv file to have the correct
number of commas per line. How can this be achieved?

Be sure you and Excel agree on where the last used cell is. Press CTRL+END to
find out what Excel thinks. If it is too far to the right or down, DELETE the
extra columns and rows using Edit/DELETE, Entire Column and Entire Row (not
the DEL key).


On Mon, 6 Dec 2004 03:09:01 -0800, One-eyed-Ian
wrote:

When attempting to convert an .xls format to .csv format using Excel to do
the conversion process, I am getting too many commas at the end of a number
of lines (where there is NO data at all). It may be linked to some preview,
I see some resemblance between 'web page preview' and the resultant output.
Would like to just save a file with variable numbers of (and items of) data
on separate lines of the same file, and for the .csv file to have the correct
number of commas per line. How can this be achieved?

This might describe the problem of too many commas in CSV files:

http://support.microsoft.com/default.aspx?scid=77295
Column Delimiters Missing in Spreadsheet Saved as Text

(It actually describes missing delimiter, but if some are "missing", maybe the
ones appearing are "extra".)

(But a lot of programs (excel included) don't care about those extra columns.
Maybe you don't have to care, either???)

Maybe you could write your own exporting program that would behave exactly the
way you want:

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(or maybe you could build your own formula and copy|paste into Notepad.)

In G1:
=a1
In G2:
=a2&","&b2&","&c2&","&d2&","&e2&","&f2
then drag down.

You may need to insert additional quotes or formatting:

=a2&","&text(b2,"mm/dd/yyyy")&....


One-eyed-Ian wrote:

When attempting to convert an .xls format to .csv format using Excel to do
the conversion process, I am getting too many commas at the end of a number
of lines (where there is NO data at all). It may be linked to some preview,
I see some resemblance between 'web page preview' and the resultant output.
Would like to just save a file with variable numbers of (and items of) data
on separate lines of the same file, and for the .csv file to have the correct
number of commas per line. How can this be achieved?

--

Dave Peterson