Bulk Import

[ryanakron]

I am attempting to make a script to load leads directly into the database. The database schema appears straight forward, but I think I am missing something.

The general flow:
look for a list in vicidial_lists and pull the list id
load the leads from a file into a temporary table
insert the leads into vicidial_list with values set for phone_number and list_id and status='NEW'

All the other fields have default values that seem to make sense so I just leave them. When I look at the list via the gui, it appears that they have been loaded. However, when I begin the campaign the hopper does not load. The gui says something like "list has no contacts" which doesn't make sense.

I could probably use the cli import, however, I find it limited since I really don't need most of the fields. All I care about is loading a phone number into a list - no name, no address, none of the other fields are relevant to my needs at the moment.

If anyone has already looked into this and can offer some guidance, I would be very thankful!
If I do get it working, I will post instructions here.

* I am using GoAutoDIal with Asterisk 1.4.39.1

[DomeDan]

I would guess you need a phone_code because that defaults to NULL

[ryanakron]

I left that part out, but yes I set it too.

[williamconley]

I have a silly suggestion for you ...

execute this at the command line:

perl /usr/share/astguiclient/VICIDIAL_IN_new_leads_file.pl --help
_______________________________________________________________
also:

1) Welcome to the Party!

2) As you are obviously new here, I have some suggestions to help us all help you:

When you post, please post your entire configuration including (but not limited to) your installation method and vicidial version with build.

This IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on EFLO.net, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "from scratch" you must post your operating system and should also post the .iso version from which you installed your original operating system. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600

[ryanakron]

The process I follow is like:

How to manually load data into vicidial:

1) connect to mysql and the asterisk database.

Make sure to use the flag --local-infile when loading mysql

2) find the list you want to import data into

select * from vicidial_lists;
make a note of the list_id you wish to use

3) Import the contact info. In my case I am only importing phone numbers.

In my case, the data I am importing is in the home folder and the list_id that I am importing to is 1002.

create temporary table t(phone varchar(10));

LOAD DATA LOCAL INFILE '/home/file.csv'
INTO TABLE t
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(phone);

INSERT INTO vicidial_list
(status,list_id, phone_code, phone_number)
SELECT 'NEW','1002','1',phone
FROM t;

drop table t;
// or truncate if you are reusing it to load more files

I will update this post if there are any issues when I test it. If anyone can point out any major issues with proceeding this way, I would really appreciate it! The big difference between this and the cli script is the script automatically puts '' for default values where this results in a few columns with NULL value. But I am not really sure if it matters or not. Hopefully this can help someone else avoid spending a bunch of time learning schema just to do a simple import.