CSV with semicolon or semicolon?

Question

How to create a CSV file in general? With commas or with a comma? Any tips you can use?

Answer 1

On Windows, this depends on the Language and Regional Settings screen, where you will find the List separator. These are char char applications that are expected as a CSV delimiter.

Of course, this has an effect only in Windows applications, for example Excel will not automatically split the data into columns if the file does not use the aforementioned delimiter. All applications using regional Windows settings will have this behavior.

If you are writing a program for Windows that will require you to import CSV into other applications, and you know that the list separator is set for your target computers,, then go to it, otherwise I prefer ; since it causes fewer problems with decimal points, groups numbers and does not appear in a lot of text.

Answer 2

I would say that I adhere to a comma, as it is widely recognized and understood. Do not forget to indicate your values ​​and avoid your quotes.

 ID,NAME,AGE "23434","Norris, Chuck","24" "34343","Bond, James ""master""","57" 

Answer 3

CSV is the standard format defined in RFC 4180 (in 2005), so there is no standard. https://www.ietf.org/rfc/rfc4180.txt

And even before that, C in CSV always stood for Comma, and not for semiColon :(

It is unfortunate that Microsoft continues to ignore this and still adheres to the monstrosity that they turned decades ago (yes, I admit, that was before the creation of the RFC).

• One entry per line if a new line is not found in the quoted text (see below).
• COMMA as a column delimiter. Never use a semicolon.
• PERIOD as a decimal point in numbers. Never a comma.
• Text containing commas, periods, and / or newlines enclosed in double quotation marks.

• Only if the text is enclosed in double quotation marks, such quotation marks are marked in the text that 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 indicate the values ​​of date and time; personally, I try to adhere to the ISO 8601 format to avoid confusion day / month / year - month / day / year.

Answer 4

Also appropriate, but especially for Excel, look at this answer and this other one that suggests inserting a line at the beginning of CSV with

 "sep=," 

To tell excel which separator to expect

Answer 5

Initially, this was supposed to be a comma, however, since a comma is often used as a decimal point, it would not be such a good separator, therefore others, such as a semicolon, mostly depending on the country

http://en.wikipedia.org/wiki/Comma-separated_values#Lack_of_a_standard

Answer 6

CSV is a comma separated file. The separator is usually a comma, but I have seen many other characters used as separators. They are not so often used.

Regarding usage recommendations, we need to know your application. Is the file specific to your application / program, or is it necessary to work with other programs?

Answer 7

To change the comma to a semicolon as the default Excel separator for CSV - go to Region → Advanced settings → Number table → List of separators and type; instead of the default value,

Answer 8

1.> Change the file format to .CSV (comma separated)

To achieve the desired result, we need to temporarily change the delimiter setting in Excel Parameters:

 Move to File -> Options -> Advanced -> Editing Section 

Uncheck the Use system separators box and place a comma in the Decimal separator box.

Now save the file in .CSV format and it will be saved in a comma delimited format.

Answer 9

Well, just to say a few words about the comma. In many countries, a comma is used for the decimal period. Mostly from the EU colonies, which consist of half the world, the other half conforms to the British standard (how the hell the UK is so big O_O) that in turn makes using a comma for a database that includes a number, creates most of the headache. because Excel refuses to recognize it as a delimiter.

As wise in my country, Vietnam, follow the French standard, our Hong Kong partner uses the British standard, so the comma makes CSV unusable, and we use \ t or; instead for international use, but it is still not “standard” for a CSV document.

Answer 10

the best way is to save it in a text file with the 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\" & Name & ".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 & ";" & Replace(ThisWorkbook.Worksheets(1).Cells(i, 6).Value, ".", ",")) j = j + 1 i = i + 1 Loop stream.Close End Sub