Double quotes in import files, why?

Started by Yost67, February 19, 2024, 08:04:25 AM

Previous topic - Next topic

Yost67

I'm wondering why there are double quotes needed in import files. Especially because every field needs to have them, while they are already comma separated. And I learned this weekend that seems very hard to make such an import file on a Mac (both Excel and Numbers export to csv without adding the quotes).

Maybe I'm overlooking something simple, so please correct me if I do. And otherwise this is just a suggestion for future innovation. 

Phil Beauvoir

#1
Actually, you don't need double quotes for a field except where you use a comma in the text field. The use of double quotes is recommended in the User Guide to be on the safe side.

For example:

ID,Type,Name,Documentation,Specialization
100,ArchimateModel,Archisurance,An example of a fictional Insurance company.,
101,BusinessActor,"Archisurance, Inc.",,
102,BusinessActor,Back Office,,

This is how Excel on Mac will export to CSV.
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

Yost67

Thanks for the reply. So the lack of quotes is not the reason for the error I'm getting, interesting. I will have to "debug" some more...

Yost67

I now learned that I got an import message because 1 of my fields included a comma, and as the field was not delimited by quotes, that line suddenly contained more fields than the import function expected...

This problem occurred because the CSV export from both Excel and Numbers creates files with semicolons, and I was searching & replacing those semicolons with commas, and I wasn't aware that some of my fields contained commas already. The underlaying problem is that both Excel and Numbers don't "quote" fields with commas, if they are using semicolons as delimiter.

I found a (weird) way for forcing Numbers to create CSV files with commas as delimiter (and also use quotes around fields that include commas): use MacOS System Preferences to set the number display to US (so to 9,999,999.99). After restarting your Mac, Numbers will have automagically changed the delimiters from semicolons to commas.

Also note: I heard from colleagues that they are using Open Office or Libre Office for creating CSV files, as these product give you full and explicit control over how to format your export files. I did not test this myself.