solution for importing csv files from mac

FransG

In general it was hard to import a csv-file. It did not work at first, but no reason offered. Might be good to explain more/better during the process.

This might be a good article when you created a csv file with Excel on Mac. The file is using semicolons. This helpfile from Salesforce explains how to change it. I was not able to do in in Excel.

Deze pagina afdrukken
How do I replace the semi colons by commas in .csv file?

Knowledge-artikelnummer: 000176500

Beschrijving

Occasionally, you might export a file from Salesforce or other data source and the .csv contains semi colons instead of commas, to separate the data.

Sometimes an attached CSV file on the case will show records separated by semi-colons when created on a Mac OS system.

This can cause some trouble when trying to import this information back into Salesforce. You can use Notepad to replace all the semi colons found in your file, by commas, so that Salesforce accepts the file format upon importing.

Oplossing

Follow these steps to fix the issue:

1. Right click on the file and Open with Notepad.
2. Click the Edit menu and select Replace.
3. In the Find what box type in a semi colon symbol ( ; ).
4. In the Replace with box type in a comma ( , ).
5. Click the Replace all button.
6. Close the Replace window.
7. Save the file.

SOURCE: https://help.salesforce.com/apex/HTViewSolution?urlname=How-do-I-replace-the-semi-colons-by-commas-in-csv-file&language=en_US

---

1Password Version: Not Provided
Extension Version: Not Provided
OS Version: Not Provided
Sync Type: Not Provided
Referrer: forum-search:semicolon

MrC

Hi @FransG ,

Better error reporting during import would indeed be beneficial. I asked about this once, and it was explained that due to how 1Password works, this was difficult, so will require some work on AgileBits' part.

The help article you reference is not a technique I would recommend. It can break / corrupt a delimited file just as well, and unfortunately, separator replacement is non-trivial. The best method is to use a program like Libre Office's Calc that supports any separator character(s), then you can just import that file as is.

Can you explain your CSV file and how it was generated? I have in mind a new converter to help situations like yours.

FransG

I made it in Excel for Apple Mac.

MrC

Ah, In that case it would be best to just export as CSV (using standard comma separators). I'm not sure where your semicolons come into play.

Drew_AG

Hi @FransG,

Hopefully that was helpful for you! MrC is quite an expert when it comes to importing data into 1Password - which you may have figured out by looking at his thread about the converter utility he created. ;) If you need additional help with that, I'm sure he'll be able to assist you further. And of course the rest of us are here for you as well!

FransG

if I choose CSV export in Excel for Mac, it automatically puts in semicolons. Might be a matter of settings in Mac, I presume. Might have to search in that direction.

littlebobbytables

Hi @FransG,

Let us know if you find something. I don't have Excel for Mac to test I'm afraid so I can't comment or assist there. I will say I've not come across semicolons before as delimiters - that's unusual.

MrC

Hi @FransG,

I had to wait for my wife to return from a business trip, as her Mac that has Excel, and I wanted to test your situation.

When we save as CSV, as expected, commas are used as the separator. I didn't see a way to change that to something else in my quick look in the Save As dialog, but perhaps there is some other place where separator options can be configured.

hawkmoth

@FransG, @MrC, @littlebobbytables - I was looking for an option to change the separator in a CSV file in my copy of Excel 2011 for Mac. I was unable to find one, and it struck me as odd that a Comma Separated Value file would have any other separator besides a comma.

A Google search turned up the information (not verified by me) that there is no option to change this in Excel itself, but the separator is controlled by the language set in System Settings. Here is a paragraph I copied:

It's worth adding a precision: In language pane of system preferences, if the decimal separator is set on a period (US for instance), then Excel generates the cvs file with a coma as the separator. If the decimal separator is a coma (France for instance), then it can't use a coma as the separator and uses the semi-colon instead.

Perhaps that is helpful? You can read the entire thread on this page. As I say, I cannot verify its accuracy.

MrC

Good sleuthing.

This shouldn't be a necessary change, but I can see how Excel might do this. Proper quoting of internal comma's is sufficient, but given that numeric data may contain many comma-decimal separators, the engineers might have thought this would save a few characters. A bad choice in my opinion, as it renders the CSV less usable with other software that doesn't have CSV separator options.

FransG

All, thanks very much for your involvement in finding a solution. Indeed the problem was the language and the way the decimals are used. It was necessary to change it on the mac, in the system preferences. The export into a CSV-file (after opening it in text editor) showed comma's and no longer semicolons. Normally I have the settings on Dutch/European, with a point between thousands and a comma before decimals. The US setting solved it. Thanks again. Now I can continue trying to import the file :-)

littlebobbytables

That's some excellent detective work @hawkmoth. Very nice find and it's great to hear it helped @FransG.