Correcting delimiter definitions in .csv files

Question

I have a number of .csv files that have a semicolon (;) delimiter rather than comma, and I'm wondering if there is a simple way to change the text in them all.

Lets say I have a test file "a.csv" with text:

1;0.5;7
2;0.4;8
3;0.6;7

And I want to convert the semicolons to commas, such that it can import nicely as a csv file.

Test = "1;0.5;7
2;0.4;8
3;0.6;7"
Export["a.csv", Test](*Creates Test File*)
Export["a.csv", ToExpression[
StringReplace[Import["a.csv", "String"],
{";" -> ",", "\r\n" -> "\n"}]]](Imports/Replaces/Exports)
FullForm@Import["a.csv", "CSV"](Read Output)

Running the above code results in:

List[List["1,0.5,7\n2,0.4,8\n3,0.6,7"]]

Which is wrong and I'm looking for something that would make:

List[List[1,0.5`,7],List[2,0.4`,8],List[3,0.6`,7]]

The above code works if I am dealing with .txt files, but doesn't seem to function with .csv files. However, I hope to keep the .csv tag in the file names. Is there something I'm missing, or some simpler method?

Why not use the "Table" format instead? ImportString["1;0.5;7 2;0.4;8 3;0.6;7", "Table", "FieldSeparators" -> ";"] — J. M.'s missing motivation, Jan 04 '21 at 11:23
Cool! This is what I needed I think. Although some files are still comma separated, so "FieldSeparators" -> {";",","} seems to be what I want. — ChaSta, Jan 04 '21 at 11:38
Does this answer your question? When importing is it possible to set the delimiter? — creidhne, Jan 04 '21 at 11:43
If you export a string to a csv file, it is not written literally but converted to a csv-field-entry. To avoid that, you can use an explicit data format in the export: Export["a.csv", ToExpression[ StringReplace[ Import["a.csv", "String"], {";" -> ",", "\r\n" -> "\n"}]], "String"] — Albert Retey, Jan 04 '21 at 13:27

score 2 · Accepted Answer · answered Jan 04 '21 at 19:02

Use the "Table" format with a custom "FieldSeparators" option:

ImportString["1;0.5;7 2;0.4;8 3;0.6;7", "Table", "FieldSeparators" -> ";"]

You can specify multiple field separators so, as you mentioned in comments, you can also use "FieldSeparators" -> {";", ","} if some of the fields are still comma-separated.

Correcting delimiter definitions in .csv files

1 Answers1