While opening CSV files, Excel will use a system regional setting called List separator to determine which default delimiter to use. Microsoft Excel will open.csv files, but depending on the system's regional settings, it may expect a semicolon as a separator instead of a comma, since in some languages the comma is used as the decimal separator. Syntax and Options. The default is the name of the CSV file with an extension of fmt.r, -required A data value is required in data columns for which the format specification does not include an explicit specification of whether data is required for a column. The default is false (i.e., data are not required).
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 Office2007 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 James5 7 6 4 8Yes Yes my CSV file will look like:Matt,Mary,John,Tom,James5,7,6,4,8Yes,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. 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 CommaKillerDim TextLine As String, comma As Stringcomma = ','Close #1Close #2Open 'c:alpha.csv' For Input As #1Open 'c:omega.csv' For Output As #2Do While Not EOF(1)Line Input #1, TextLinel = Len(TextLine)For i = 1 To lIf Right(TextLine, 1) = comma ThenTextLine = Left(TextLine, Len(TextLine) - 1)End IfNextPrint #2, TextLineLoopClose #1Close #2End Sub GSNU201507.
Lots of programs won't care if there are extra trailing commas at the end. Forinstance, 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:Column Delimiters Missing in Spreadsheet Saved as Text(It actually describes missing delimiter, but if some are 'missing', maybe theones 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 theway 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: )Chip Pearson's:J.E. McGimpsey's: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 Office2007 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 James5 7 6 4 8Yes Yes my CSV file will look like:Matt,Mary,John,Tom,James5,7,6,4,8Yes,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.
![Bad Csv File Verify That Your Delimiters Are Consistent Bad Csv File Verify That Your Delimiters Are Consistent](/uploads/1/2/5/4/125414837/787366961.png)
Did anyone ever find out what the root cause of the extra commas was?I use.csv files to create a tooling catalogue and run a macro within Catia V5 that then generates a catalogue from the CSV file.I have now upgraded to Office 2010 and experience exactly the same as the original poster and many extra commas are created. Unfortunately for me the macro within Catia does not understand these new commas and errors out and aborts the creation of my toolingcatalogue.If I change the.csv to a.txt and manually remove the commas (This takes ages), then change back to a.csv the macro works perfectly again.
I was hoping that someone may have found a setting somewhere within excel that can be changed so that.csv filessave the same as they used to in previous versions of Excel.CheersPaul. Paul wrote:Did anyone ever find out what the root cause of the extra commas was?What's to figure out?In JKnipe's example, the Excel worksheet contains data in columns A through E. So Excel saves 5 columns for each row. The 'extra' commas reflect columns where there is no data (or null strings. 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. Suprprised me!open the problematic.csvcopy the good columns and rows desired.paste into the 2X2 worksheet.
(now 3 X 120)Save as whatever.csvclose execl.rename file as appropriate. Delete the bad one. I'm clueless but this worked for me.
![Delimiters Delimiters](/uploads/1/2/5/4/125414837/571204692.png)
Yes, I get your point, but you have a file that has 2 delimiters. If you only take one, and use it, you will still end up with an error parsing that CSV or bad data. Because you can't account for the second delimiter that exists.So in your example, the first set of values you might receive using; because it happens more frequently isabc,111def; 111The second set of data would beijk; 222All that is based on the assumption PHP can handle it that way and doesn't return FALSE designating an error due to the comma delimiter.
Cancer10 said:Hello againI am currently using your code posted in post #43One issue is with the following data, the getDelimiter function returns null`EMAIL;[email protected];[email protected];[email protected];`But when I add some chars after semicolon it works like a charm`EMAIL; [email protected];[email protected];[email protected];`It didnt resolve even when I added the trim function.Can you please let me know why is this happening?ThanksI don't get that result, I get ';', which is correct.See attached code.