Re: Save As CSV problem

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" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
news:426E32F8.1B966268@xxxxxxxxxxxxxxxxxxxx
> 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" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
> > news:426D8112.C544CDCB@xxxxxxxxxxxxxxxxxxxx
> > > 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 Spread*** 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 spread*** 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



.