Opening then saving a CSV file looses Commas in Excel 2003

If i have a CSV file that has a number a fields, some at the end which
may not be filled in for some rows, after a few lines, Excel will omit
the trailing commas for the empty fields. This causes some problems when
trying to import the editied file into other applications.

This is repeatable for me on many machines.

also take a look at this issue,
http://groups-beta.google.com/group/...624ccd84bb48a6

I've included a sample CSV file that exibits this particular behavior.
Take the attached file, and open it in Notepad. Notice the trailing
commas. Now open the file in Excel, then Just click the save button. It
will wanr you about some compatibilty issues and answering either Yes or
no makes no difference. Answering no just makes you do an additional
step of saving a separate workbook. Now open the same file again in
Notepad. Noice after around line 17, the trailing commas dissapear and
then reappear somewhere around line 81.

I've been able to reproduce this on 3 different machines now, so I know
it's not just my system that is exibiting this behavior.

I tried searching the knowledge base extensively for this but was unable
to find any answer.

Sean

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




Sean Wolfe wrote:
>
> If i have a CSV file that has a number a fields, some at the end which
> may not be filled in for some rows, after a few lines, Excel will omit
> the trailing commas for the empty fields. This causes some problems when
> trying to import the editied file into other applications.
>
> This is repeatable for me on many machines.
>
> also take a look at this issue,
> http://groups-beta.google.com/group/...624ccd84bb48a6
>
> I've included a sample CSV file that exibits this particular behavior.
> Take the attached file, and open it in Notepad. Notice the trailing
> commas. Now open the file in Excel, then Just click the save button. It
> will wanr you about some compatibilty issues and answering either Yes or
> no makes no difference. Answering no just makes you do an additional
> step of saving a separate workbook. Now open the same file again in
> Notepad. Noice after around line 17, the trailing commas dissapear and
> then reappear somewhere around line 81.
>
> I've been able to reproduce this on 3 different machines now, so I know
> it's not just my system that is exibiting this behavior.
>
> I tried searching the knowledge base extensively for this but was unable
> to find any answer.
>
> Sean
>
> --------------------------------------------------------------------------------
> Name: Sean'sList_22.csv
> Sean'sList_22.csv Type: Microsoft Excel Worksheet (application/vnd.ms-excel)
> Encoding: base64

--

Dave Peterson

Dave Peterson wrote:
> 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".)
>

Actually this is the actual issue. I don't have extra comma's Excel is
missing them. They are delimiters for fields that are empty.
Unfortunately we have some software that cannot handle a CSV missing
some columns.

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


The response here and at microsoft seems rather discouraging. Are they
going to fix this issue? I find it will be difficult to tell end users
that in order for them to get CSV files to work in Excel correctly they
have to write macros.

Sean

You could contact MS and ask if they'll change this behavior. From an Excel
standpoint, I'd bet that MS thinks that they handle the CSV files (with extra or
missing commas) correctly. (I've never seen it have any effect on a CSV file
imported into excel.)

I was thinking that you'd write one macro and distribute that the user
community. Whenever they needed to dump the data, they could run that one
macro.

Sean Wolfe wrote:
>
> Dave Peterson wrote:
> > 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".)
> >
>
> Actually this is the actual issue. I don't have extra comma's Excel is
> missing them. They are delimiters for fields that are empty.
> Unfortunately we have some software that cannot handle a CSV missing
> some columns.
>
> > 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:
> >
>
> The response here and at microsoft seems rather discouraging. Are they
> going to fix this issue? I find it will be difficult to tell end users
> that in order for them to get CSV files to work in Excel correctly they
> have to write macros.
>
> Sean

--

Dave Peterson

In fact, if you try Earl's workbook, it might do what you want. There are lots
of options in there.

Dave Peterson wrote:
>
> You could contact MS and ask if they'll change this behavior. From an Excel
> standpoint, I'd bet that MS thinks that they handle the CSV files (with extra or
> missing commas) correctly. (I've never seen it have any effect on a CSV file
> imported into excel.)
>
> I was thinking that you'd write one macro and distribute that the user
> community. Whenever they needed to dump the data, they could run that one
> macro.
>
> Sean Wolfe wrote:
> >
> > Dave Peterson wrote:
> > > 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".)
> > >
> >
> > Actually this is the actual issue. I don't have extra comma's Excel is
> > missing them. They are delimiters for fields that are empty.
> > Unfortunately we have some software that cannot handle a CSV missing
> > some columns.
> >
> > > 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:
> > >
> >
> > The response here and at microsoft seems rather discouraging. Are they
> > going to fix this issue? I find it will be difficult to tell end users
> > that in order for them to get CSV files to work in Excel correctly they
> > have to write macros.
> >
> > Sean
>
> --
>
> Dave Peterson

--

Dave Peterson

Sean,

I'm not sure I see a problem. Every record in your file has 23 commas,
hence 24 fields (columns) (since none is in quote marks).

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

"Sean Wolfe" <[email protected]> wrote in message
news:%[email protected]...
> If i have a CSV file that has a number a fields, some at the end which
> may not be filled in for some rows, after a few lines, Excel will omit
> the trailing commas for the empty fields. This causes some problems when
> trying to import the editied file into other applications.
>
> This is repeatable for me on many machines.
>
> also take a look at this issue,
> http://groups-beta.google.com/group/...624ccd84bb48a6
>
> I've included a sample CSV file that exibits this particular behavior.
> Take the attached file, and open it in Notepad. Notice the trailing
> commas. Now open the file in Excel, then Just click the save button. It
> will wanr you about some compatibilty issues and answering either Yes or
> no makes no difference. Answering no just makes you do an additional
> step of saving a separate workbook. Now open the same file again in
> Notepad. Noice after around line 17, the trailing commas dissapear and
> then reappear somewhere around line 81.
>
> I've been able to reproduce this on 3 different machines now, so I know
> it's not just my system that is exibiting this behavior.
>
> I tried searching the knowledge base extensively for this but was unable
> to find any answer.
>
> Sean
>