CSV with comma or semicolon

Question

How is a CSV file built in general  With commas or semicolons  Any advice on which one to use

User · Answer

I d say stick to comma as it s widely recognized and understood  Be sure to quote your values and escape your quotes though   ID NAME AGE  23434   Norris  Chuck   24   34343   Bond  James   master     57

User · Answer

best way will be to save it in a text file with csv extension   Sub ExportToCSV   Dim i  j As Integer Dim Name  As String  Dim pathfile As String  Dim fs As Object     Dim stream As Object      Set fs   CreateObject  Scripting FileSystemObject   On Error GoTo fileexists  i   15 Name   Format Now     ddmmyyHHmmss   pathfile    D  1    amp  Name  amp    csv   Set stream   fs CreateTextFile pathfile  False  True    fileexists   If Err Number   58 Then     MsgBox  File already Exists       Your code here     Return End If On Error GoTo 0  j   1 Do Until IsEmpty ThisWorkbook ActiveSheet Cells i  1  Value       stream WriteLine  ThisWorkbook Worksheets 1  Cells i  1  Value  amp       amp  Replace ThisWorkbook Worksheets 1  Cells i  6  Value                  j   j   1     i   i   1 Loop   stream Close  End Sub

User · Answer

In Windows it is dependent on the  Regional and Language Options  customize screen where you find a List separator  This is the char Windows applications expect to be the CSV separator   Of course this only has effect in Windows applications  for example Excel will not automatically split data into columns if the file is not using the above mentioned separator  All applications that use Windows regional settings will have this behavior   If you are writing a program for Windows that will require importing the CSV in other applications and you know that the list separator set for your target machines is    then go for it  otherwise I prefer   since it causes less problems with decimal points  digit grouping and does not appear in much text

User · Answer

CSV is a standard format  outlined in RFC 4180  in 2005   so there IS no lack of a standard   https   www ietf org rfc rfc4180 txt  And even before that  the C in CSV has always stood for Comma  not for semiColon     It s a pity Microsoft keeps ignoring that and is still sticking to the monstrosity they turned it into decades ago  yes  I admit  that was before the RFC was created     One record per line  unless a newline occurs within quoted text  see below   COMMA as column separator  Never a semicolon  PERIOD as decimal point in numbers  Never a comma  Text containing commas  periods and or newlines enclosed in  double quotation marks   Only if text is enclosed in double quotation marks  such quotations marks in the text escaped by doubling  These examples represent the same three fields   1  this text contains   quotation marks    3  1 this text contains  quotation marks  3   The standard does not cover date and time values  personally I try to stick to ISO 8601 format to avoid day month year -- month day year confusion

User · Answer

Also relevant  but specially to excel  look at this answer and this other one that suggests  inserting a line at the beginning of the CSV with   sep      To inform excel which separator to expect

User · Answer

Initially it was to be a comma  however as the comma is often used as a decimal point it wouldnt be such good separator  hence others like the semicolon  mostly country dependant  http   en wikipedia org wiki Comma-separated values Lack of a standard

User · Answer

Well to just to have some saying about semicolon  In lot of country  comma is what use for decimal not period  Mostly EU colonies  which consist of half of the world  another half follow UK standard  how the hell UK so big O O  so in turn make using comma for database that include number create much of the headache because Excel refuse to recognize it as delimiter   Like wise in my country  Viet Nam  follow France s standard  our partner HongKong use UK standard so comma make CSV unusable  and we use  t or   instead for international use  but it still not  standard  per the document of CSV

User · Answer

1   Change File format to  CSV  semicolon delimited   To achieve the desired result we need to temporary change the delimiter setting in the Excel Options   Move to File - gt  Options - gt  Advanced - gt  Editing Section   Uncheck the    Use system separators    setting and put a comma in the    Decimal Separator    field   Now save the file in the  CSV format and it will be saved in the semicolon delimited format

User · Answer

CSV is a Comma Seperated File   Generally the delimiter is a comma  but I have seen many other characters used as delimiters   They are just not as frequently used   As for advising you on what to use  we need to know your application   Is the file specific to your application program  or does this need to work with other programs

User · Answer

To change comma to semicolon as the default Excel separator for CSV - go to Region -  Additional Settings -  Numbers tab -  List separator and type   instead of the default

[csv] CSV with comma or semicolon?

Examples related to csv