CSV file has extra commas

SO i think i found an easier solution using office 2007

I was getting the same issue with my CSV file having extra commas this is what i did.

I saved my xls file as a csv and then opened the csv file in excel and then resaved it as a csv file with another name. This got rid of the extra commas and now all is well in the kingdom.

Hope this helps.

Here is a small macro that reads from one text file and writes to another.  Each record in the output file has trailing commas removed:

Sub CommaKiller()

    Dim TextLine As String, comma As String

    comma = ","

    Close #1

    Close #2

    Open "c:\alpha.csv" For Input As #1

    Open "c:\omega.csv" For Output As #2

    Do While Not EOF(1)

        Line Input #1, TextLine

        l = Len(TextLine)

        For i = 1 To l

            If Right(TextLine, 1) = comma Then

                TextLine = Left(TextLine, Len(TextLine) - 1)

            End If

        Next

        Print #2, TextLine

    Loop

    Close #1

    Close #2

End Sub

I had a boatload of extra columns (commas) perhaps out to the maximum supported by excel 2010.  I also believed it was introduced by a new installation of Windows 7 and/or Office 2010.

I fixed it like this.

Close out execl.

reopen execl.

create a very small worksheet 2 columns 2 rows.

save as .csv (MS-DOS).

check with notepad.  All OK! suprprised me!

open the problematic .csv

copy the good columns and rows desired.

paste into the 2X2 worksheet. (now 3 X 120)

Save as whatever.csv

close execl.

rename file as appropriate.  delete the bad one.  I'm clueless but this worked for me.

Excel likes everything to rectangular (or square).  Each comma is viewed as the cell wall and then whats in-between the commas is the inside of the cell.  So in order to keep everything square excel will add in those commas so that those cells can exist and there isn't a void in the spreadsheet.

Now thats why, I to am looking for a remedy for this problem and have yet to find one. I will continue my journey in search for stopping of the extra commas

Lots of programs won't care if there are extra trailing commas at the end.  For

instance, excel won't care.

But if you do...

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

JKnipe wrote:

I was running Windows XP 32 bit and Microsoft Office 2007 Professional, and when I saved my spreadsheet to CSV format, it never added additional commas in the blank cells.  I just upgraded to Windows 7 64 bit and installed a new license for Microsoft Office 2007 Enterprise, and now when I save the same file to CSV format, it adds commas for all the blank cells out to the last cell of the row with the most data.  Can someone tell me what is different or what I can do to make it ignore blank cells like it did before?

For example if my spreadsheet was like:

Matt | Mary | John | Tom | James

5     |   7    |   6    |   4    |   8

Yes  |  Yes  |        |         |

my CSV file will look like:

Matt,Mary,John,Tom,James

5,7,6,4,8

Yes,Yes,,,

I dont want the three commas after the second "yes" to be there.  The files I am actually dealing with have thousands of lines of data and find/replace in text editor is too risky to mess up the data and possibly program our machinery wrong.

--

Dave Peterson