When I save a spreadsheet as CSV, and some of the fields at the end of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for the
empty fields, and some do not. Anyone see/hear of this behavior? I'm using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at 17 do
not. The problem always starts at row 17 for me.

Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the end of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for the
empty fields, and some do not. Anyone see/hear of this behavior? I'm using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at 17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

The Text Write program does not normally write field delimiters (normally
commas) past the last field that contains something, in a given record. For
that, use the "Write rectangular" option, first selecting the range to be
written to. From what I've heard, Excel's CSV file type sometimes does, and
sometimes doesn't.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for the
empty fields, and some do not. Anyone see/hear of this behavior? I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and open
in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at 17
do
not. The problem always starts at row 17 for me.

--

Dave Peterson

I knew you built a lot of options into that workbook!

Earl Kiosterud wrote:

The Text Write program does not normally write field delimiters (normally
commas) past the last field that contains something, in a given record. For
that, use the "Write rectangular" option, first selecting the range to be
written to. From what I've heard, Excel's CSV file type sometimes does, and
sometimes doesn't.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for the
empty fields, and some do not. Anyone see/hear of this behavior? I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and open
in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at 17
do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

I didn't explain the problem correctly, I want (need) the comma's. The
program I wrote is to load data from a CSV into a DB. The first thing I do
is check the number of fields in the record, if there are too few, or too
many, I skip the record. Our client said they were getting a lot of skipped
records, and the skipped records were similar to records that weren't being
skipped. I asked for the file they were using & found this problem. The
article you cited does explain the behavior, thanx.. I will suggest to my
boss that he instructs the clients to put a space in the last field of any
record that doesn't have data (all fields are trimmed before processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for
the
empty fields, and some do not. Anyone see/hear of this behavior? I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

I read that KB article again and it sure sounds like it explains the problem to
me. In fact, it recommends the same technique as you do.



ScotP wrote:

I didn't explain the problem correctly, I want (need) the comma's. The
program I wrote is to load data from a CSV into a DB. The first thing I do
is check the number of fields in the record, if there are too few, or too
many, I skip the record. Our client said they were getting a lot of skipped
records, and the skipped records were similar to records that weren't being
skipped. I asked for the file they were using & found this problem. The
article you cited does explain the behavior, thanx.. I will suggest to my
boss that he instructs the clients to put a space in the last field of any
record that doesn't have data (all fields are trimmed before processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for
the
empty fields, and some do not. Anyone see/hear of this behavior? I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

Dave:
Your message seems to infer that I don't think that the article explains
the problem, I DO think that it does explain exactly what was happening.
Earls macro looks like it will fix more than just the delimiter problem
(they also had some carriage returns in fields), but I don't know if it's
practical to ask the client to use a third party macro to load their data.
I will discuss it with the powers that be, and if the client is receptive
(I'm told that they are "Excel Wizards" all evidence to the contrary), we
will send it to them.

In any event, I will keep all the links you sent so I can steal
code/tips at a later date as necessary.


Many thanx to you & the link contributers,
Scot P


"Dave Peterson" wrote in message
...
I read that KB article again and it sure sounds like it explains the
problem to
me. In fact, it recommends the same technique as you do.



ScotP wrote:

I didn't explain the problem correctly, I want (need) the comma's.
The
program I wrote is to load data from a CSV into a DB. The first thing I
do
is check the number of fields in the record, if there are too few, or
too
many, I skip the record. Our client said they were getting a lot of
skipped
records, and the skipped records were similar to records that weren't
being
skipped. I asked for the file they were using & found this problem.
The
article you cited does explain the behavior, thanx.. I will suggest to
my
boss that he instructs the clients to put a space in the last field of
any
record that doesn't have data (all fields are trimmed before
processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the
end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have
<10
fields), some rows have consecutive comma's at the end of the row
for
the
empty fields, and some do not. Anyone see/hear of this behavior?
I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1,
then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting
at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

Sorry--I misread your response and added a "not" as in "does not explain" (it's
hell getting old!).

Maybe you could just accept their files asis, but then you could convert it to
what you want. Your customers won't have to do anything.

(I don't know what program needs these extra commas, but if it's an in-house
program, maybe you could have it modified??)


ScotP wrote:

Dave:
Your message seems to infer that I don't think that the article explains
the problem, I DO think that it does explain exactly what was happening.
Earls macro looks like it will fix more than just the delimiter problem
(they also had some carriage returns in fields), but I don't know if it's
practical to ask the client to use a third party macro to load their data.
I will discuss it with the powers that be, and if the client is receptive
(I'm told that they are "Excel Wizards" all evidence to the contrary), we
will send it to them.

In any event, I will keep all the links you sent so I can steal
code/tips at a later date as necessary.

Many thanx to you & the link contributers,
Scot P

"Dave Peterson" wrote in message
...
I read that KB article again and it sure sounds like it explains the
problem to
me. In fact, it recommends the same technique as you do.



ScotP wrote:

I didn't explain the problem correctly, I want (need) the comma's.
The
program I wrote is to load data from a CSV into a DB. The first thing I
do
is check the number of fields in the record, if there are too few, or
too
many, I skip the record. Our client said they were getting a lot of
skipped
records, and the skipped records were similar to records that weren't
being
skipped. I asked for the file they were using & found this problem.
The
article you cited does explain the behavior, thanx.. I will suggest to
my
boss that he instructs the clients to put a space in the last field of
any
record that doesn't have data (all fields are trimmed before
processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the
end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have
<10
fields), some rows have consecutive comma's at the end of the row
for
the
empty fields, and some do not. Anyone see/hear of this behavior?
I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1,
then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting
at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

In fact, maybe you could take some of that code and build an automatic routine
that opens each of the .csv files saves them the way you want (just loop through
all those .csv files in a particular folder and process them to make them
nicer).

ScotP wrote:

Dave:
Your message seems to infer that I don't think that the article explains
the problem, I DO think that it does explain exactly what was happening.
Earls macro looks like it will fix more than just the delimiter problem
(they also had some carriage returns in fields), but I don't know if it's
practical to ask the client to use a third party macro to load their data.
I will discuss it with the powers that be, and if the client is receptive
(I'm told that they are "Excel Wizards" all evidence to the contrary), we
will send it to them.

In any event, I will keep all the links you sent so I can steal
code/tips at a later date as necessary.

Many thanx to you & the link contributers,
Scot P

"Dave Peterson" wrote in message
...
I read that KB article again and it sure sounds like it explains the
problem to
me. In fact, it recommends the same technique as you do.



ScotP wrote:

I didn't explain the problem correctly, I want (need) the comma's.
The
program I wrote is to load data from a CSV into a DB. The first thing I
do
is check the number of fields in the record, if there are too few, or
too
many, I skip the record. Our client said they were getting a lot of
skipped
records, and the skipped records were similar to records that weren't
being
skipped. I asked for the file they were using & found this problem.
The
article you cited does explain the behavior, thanx.. I will suggest to
my
boss that he instructs the clients to put a space in the last field of
any
record that doesn't have data (all fields are trimmed before
processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at the
end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows have
<10
fields), some rows have consecutive comma's at the end of the row
for
the
empty fields, and some do not. Anyone see/hear of this behavior?
I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1,
then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting
at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I don't get the files at all (unless there's a problem, & I request
them). They are uploaded by the client through an asp page. The space
solution should be ok for now.

ScotP

"Dave Peterson" wrote in message
...
In fact, maybe you could take some of that code and build an automatic
routine
that opens each of the .csv files saves them the way you want (just loop
through
all those .csv files in a particular folder and process them to make them
nicer).

ScotP wrote:

Dave:
Your message seems to infer that I don't think that the article
explains
the problem, I DO think that it does explain exactly what was happening.
Earls macro looks like it will fix more than just the delimiter problem
(they also had some carriage returns in fields), but I don't know if
it's
practical to ask the client to use a third party macro to load their
data.
I will discuss it with the powers that be, and if the client is
receptive
(I'm told that they are "Excel Wizards" all evidence to the contrary),
we
will send it to them.

In any event, I will keep all the links you sent so I can steal
code/tips at a later date as necessary.

Many thanx to you & the link contributers,
Scot P

"Dave Peterson" wrote in message
...
I read that KB article again and it sure sounds like it explains the
problem to
me. In fact, it recommends the same technique as you do.



ScotP wrote:

I didn't explain the problem correctly, I want (need) the
comma's.
The
program I wrote is to load data from a CSV into a DB. The first
thing I
do
is check the number of fields in the record, if there are too few,
or
too
many, I skip the record. Our client said they were getting a lot of
skipped
records, and the skipped records were similar to records that
weren't
being
skipped. I asked for the file they were using & found this problem.
The
article you cited does explain the behavior, thanx.. I will suggest
to
my
boss that he instructs the clients to put a space in the last field
of
any
record that doesn't have data (all fields are trimmed before
processing).

Thanx Again,
Scot P

"Dave Peterson" wrote in message
...
Saved from a previous post:

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")&....

ScotP wrote:

When I save a spreadsheet as CSV, and some of the fields at
the
end
of
the row are empty (ie row 1 has 10 fields, and subsequent rows
have
<10
fields), some rows have consecutive comma's at the end of the
row
for
the
empty fields, and some do not. Anyone see/hear of this
behavior?
I'm
using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 -
E1,
then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV,
and
open in
notepad, rows 2 - 16 have three comma's at the end, & rows
starting
at
17 do
not. The problem always starts at row 17 for me.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson