Re: Opening then saving a CSV file looses Commas in Excel 2003
- From: Dave Peterson <ec35720@xxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Apr 2005 20:35:17 -0500
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/microsoft.public.excel.misc/browse_frm/thread/b12633abc92c3c73/9f624ccd84bb48a6?q=excel+csv+line+17+drops+commas&rnum=1&hl=en#9f624ccd84bb48a6
>
> 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
.
- Follow-Ups:
- Re: Opening then saving a CSV file looses Commas in Excel 2003
- From: Sean Wolfe
- Re: Opening then saving a CSV file looses Commas in Excel 2003
- References:
- Opening then saving a CSV file looses Commas in Excel 2003
- From: Sean Wolfe
- Opening then saving a CSV file looses Commas in Excel 2003
- Prev by Date: Pasting range to a graphic
- Next by Date: Re: Pasting range to a graphic
- Previous by thread: Opening then saving a CSV file looses Commas in Excel 2003
- Next by thread: Re: Opening then saving a CSV file looses Commas in Excel 2003
- Index(es):
Relevant Pages
|
