Import contacts from excel file / files

đź’¬ General Discussion
1

Hi all.

I am trying to import several contacts into suitecrm using the Import feature of the software. I saw all about the .csv format, tried different stuff but I always get errors. Some interesting issues goes like this:

a. I download the Contacts.csv template file from suiteCRM; I open the file with excel 2010 and it is scrambled. I go to Data -> Text to columns, and convert each text row into a column, with the delimiter (Comma) and the text qualifier (double quotes). Everything looks OK and I save the result as a .csv file.
I happily go into suiteCRM ->Contacts Import, choose the Contacts.csv file, Step 2 looks OK, information is arranged in a “table” style, Step3: Fields mapping looks perfect, ->Next -> Import . . . . …
Result: Error Invalid date Field Name Birthdate: (birthdate) Value 19.12.2015 02:00am

I say, OK, no problem the software cannot handle a birth date of today (ha, ha, stupid me . . ); I open contacts.csv, go to birth date, delete the data. Save as .csv and re-Import.
Result ? : Step 3 : mapping fields doesn’t look so good now . . . it reports something like the “Last Name” field is having a problem . . I select it from the list, I can go to Import, but of course . . . .a big error - in the Last Name field I have the information - GreenbergArrellano817c74d5-6fb2-2ad5-795a-56754023558fMrs.Senior Product Manager817c7f1b-57dd-1321-c.

Questions are:
Am I stupid ? - Of course I am !
I realize that the “Mapping fields” step is the key to the problem ? - Yes I did but I will not try to map all those fields one by one, Hell NO !
Do I open again the Contacts.csv ? - Yes I did, with Excel 2010 and saw that all information was of course in the first cell . . . . ; Reason for that ? - The csv file saved by Excel has now the delimiter = Space and not Comma (as the original file), but even so there is a NameIDSalutationTitleDepartmentAccount sequence which is not separated by Space delimiter.

Other questions are:
Can someone pls. explain how to save the file I open with excel into a .csv (as required by suiteCRM) which will keep the same delimiter (Semicolon) and the Text Qualifier to " ?
Is there any software better at opening / editing the .csv files without messing the delimiter/ qualifiers ? but still showing the data into tabular format ?

2

Original topic was approved by Admin.
Anyone has the same problem ?
Please help !

3

I recently exported data from SugarCRM via csv files and had exactly the problem you mentioned. Excel seems to arbitrarily enclose some fields with double-quotes but not all of them when told to save a file as CSV.

Since the files I had exported from SugarCRM were all properly delimited, I used a combination of Excel and Notepad++ for cleaning up of data. I made a copy of the CSV file, opened that in Excel and used Excel to make sure the columns lined up correctly.

I used Notepad++ and its great Search.Replace feature to clean up and problems (swapping user guids, etc).

If I did end up using Excel to save the CSV file, instead of choosing the Save As > CSV option, I chose Tab Delimited instead. I then used the following search/replace combinations to clean up the tabs:

1.) Replace all double-quotes with nothing (This removes all of the arbitrary double-quotes that may exist
2.) Replace tabs (\t) with “,” (double-quote, comma, double-quote - Make sure to check the Extended Search Mode option in Notepad++ when running these)
3.) Replace new line (\r\n) with double-quote, new line, double-quote ("\r\n")

I also had to do some manual clean up as some of the exported description fields had multiple lines in them, which when exported became multiple lines in the CSV file, but should only be enclosed in a single pair of double-quotes. Otherwise the delimiting gets screwed up.

Hope this all helps.

Kevin

4

Hi,

I advise you to open your XLS file in LibreOffice and then save them to CSV. LibreOffice is an open source alternative to excel and can be downloaded for free here:

http://www.libreoffice.org

Excel doesn’t do well writing CSV files, especially concerning quoting fields that contain newlines. When you save in LibreOffice, the option “edit filter settings” enables you to set delimiter, character encoding and quotation.