Error importing CSV phonebook

Hello there,
I have been fighting for the last 3 hours with the CSV import feature of the asterisk phonebook. I have to add over 300 contacts and there is no way I can do it one by one. I have read all the posts on the matter but none seemed to help. Since it amounts to less than 1200 lines it should work, according to what I read in similar posts, but I can’t manage to.
I also have tried making sure to use UTF-8. I can manually create single contacts from the gui. Once created I have tried wiping out the csv created and inserting my data, and reuploading it, with no success.
here is the content of a CSV file I am unable to import:

“AGA ANDREA GRASSI”,+393XXX06475;
“AGENZIA FUNEBRE ROSSANA SRL”,+390799XXX075;
“ALAIMO”,+393296XXX190;
“ALBANESI”,+39329XXX13825;
“ALBERO”,+3932038XX040;

Formatting appears to be ok. A problem could be related with the + sign, but I already ruled that out. To rule it out I cleared out all contacts, created 1 new contact, downloaded the phonebook and edited the phone number. Saved and uploaded. It worked so the problem cannot be related to the + sign.

As another example, here is the content of another csv which, unexplicably, works:


“CasaComo”;03182XXX9;99
"Francesco";+393345XXXX842;
“prova”;03213XX54;98
"prova2";321651XX25;

I am beginning to thing there must be something wrong with my computer coding of csv, but I even tried saving the files in txt and then renaming them to csv.

Any help would be greatly appreciated.
Thank you

p.s. Please note that I have substituted some numbers in the phone numbers with an “X” for privacy reasons.

1 Like

The only thing that jumps out at me is the length of the name in the second line. It may be that there is a character length limit and if even one name in the list exceeds it, it won’t upload the list. You could experiment a bit and see if that is the problem.

Thank you for your kind suggestion, but unfortunately length does not seem to be the issue. I tried changing the names to something really short and it didn’t solve the problem. I am starting to believe there has to be a problem with csv compatibility. I tried redownloading the working phonebook and in notepad it appears as a single line string. When I do copy-paste it appears as follows. I wonder if it could be an issue with windows 8.1 notepad.

“CasaComo”;031821789;99
"Francesco";+393345949842;
“prova”;0321321654;98
"prova2";321651231325;

Francesco
I too spent considerable time trying to figure this out and was finally successful. The instructions provided by FreePBX are wrong for the version I’m using (FreePBX 12.0.59). Here’s what you need:
AGA ANDREA GRASSI;+393XXX06475;

  • The quotation marks around the contact name are not required – in fact, they appear to prevent the import.
  • The separator between the contact name and the phone number must be a semi-colon, not a comma.
  • The number does need to be appended with a semi-colon, as you have already done.

Do a find/ replace for the quotes: you only need to enter a single quotation mark in the find box and enter nothing in the replace box.

To switch the comma for a semi-colon, using find/ replace, enter a comma in the find box and a semi-colon in the replace box.

When saving the file, be sure to choose CSV text only. Then you should be able to import your entire list of 300 contacts to Asterisk Phonebook.

For those starting from scratch from an Outlook, Gmail, or whatever source, here is some guidance. In order to automate this, I used an add-on for Excel to edit the CSV output from Outlook. The add-on from AbleBits allows you to merge cells and insert the semi-colons appropriately. www dot ablebits dot com/excel-merge-cells/merge-excel-cells.php
(new user can’t post links)

  1. In Excel, select the columns with the names (first and last) that need to be merged. Go to the new AbleBits toolbar and click the merge cells button and choose separate with a space. You will now have all names merged in a column.
  2. Now comes the crappy part. Many contacts have more than one number, so you have to go through the lists and decide which numbers to use for the phonebook, or create duplicate name entries by inserting new rows for the additional numbers for these contacts.
  3. Once you have the names and corresponding numbers lined up appropriately, copy the names column (do not include the header), paste it into notepad, then open a new spreadsheet and paste the names into the first column. This is necessary because of the way Excel treats merged columns.
  4. Go back to your original spreadsheet and copy the numbers – again without headers, and paste the numbers into the next column in your new spreadsheet. Do a quick check to be sure everything is lined up correctly.
  5. Now select columns A, B and C. Yes C is blank, but this is where the appending semi-colon will go. With these 3 columns selected, use the AbleBits merge cells button and be sure to check the box that says to separate with a semi-colon.
  6. You should now have a single merged column formatted like this:
    John Doe;8881234567;
  7. Save this file as a plain text only CSV file. Import to Asterisk Phonebook.

I don’t know if using ‘+’ before the phone number will affect the import, but I doubt it. You can always try adding a manual entry in the phonebook with the plus sign and see if it’s accepted.

For my purposes, I was only interested in this project to get better caller ID results. I do not use this for contacts, nor do I use speed dials, but hopefully this works for somebody. It took a while to figure out and it drives me nuts hunting for non-existent or misleading information.

your separator between the name and the number needs to be ; not ,

[quote=“francescocmazza, post:1, topic:28762, full:true”]
Since it amounts to less than 1200 lines it should work, according to what I read in similar posts, but I can’t manage to. [/quote]

I recently imported a large csv phonebook and confirm this restriction appears to still exist.
Easy workaround is to split the csv file into chunks of circa ~ 1000 lines.
HTH

I hassled with this for hours and couldn’t get it to work, finally was able to. A few key points:

  1. I had to match the suggestion exactly
  2. Most office apps (such as Excel) use smart quotes (curly quotes) instead of linux plaintext quotes. The import script cannot handle that - you must edit them in a plaintext editor and remove/fix them if they are present.
  3. Even after doing this, there was something about the file format that the file didn’t like. I ended up using vi and copy/pasting the text in, upon which it imported correctly.

So in summary, it appears it is very particular about not only the content of the file, but the format of the file as well. Use the most basic plaintext editor as possible to avoid issues.