Bulk import of contacts?

[lee mcdermott]

Hi,

We have a requirement to add a new organisation to our external customers.

As such they will require access to  https://customer.hornbill.com/instancename

Is there any way to do a bulk import of users for this?

There are well over 100 users that will need adding which will be very cumbersome to add each one manually.

 

thanks

lee

[James Ainsworth]

Hi @lee mcdermott

There is the Contact Import tool which can import from a SQL source or CSV.

https://wiki.hornbill.com/index.php/SQL_Contact_Import

Let us know if this is what you are looking for.

Regards,

James

[lee mcdermott]

@James Ainsworth

thanks james. Will have a look at that.

I assume you can specify which organisation you want to attribute the users too?

[James Ainsworth]

Yes.  There is a section on the mapping which should help with this.

[Martyn Houghton]

@lee mcdermott

Just a note to confirm that the organisation record needs to exists and at the moment you have to manually create any new organisations first, as there is no tool at the moment to bulk create organisations.

Also it is worth having a think about the defaults you want to set at the top of the conf file, in relation to the Customer Portal, as going back and doing this manually on each contact is quite time consuming.

"AttachCustomerPortal": true, /* options : true/false ; whether contact is allowed to enter the customer portal */
   "CustomerPortalOrgView": true, /* options : true/false ; whether contact is allowed to view orgaisation calls in the customer portal */
   "CustomerPortalOrgViewRevoke": false, /* options : true/false ; whether the contact visibility to organisation calls in the customer portal is to be revoked */
   "UpdateContactStatus": false,

Also if you can connect the tool to a database as well as the CSV connection. Below is example conf for a MySQL DB.

 

 "SQLConf": {
        "Driver": "mysql",
        "Server": "localhost",
        "Database": "oo",
        "UserName": "user",
        "Password": "xxxxxxxxx",
        "Port": 3306,
        "ContactID": "zendesk_id",
        "FieldID": "h_logon_id",
        "Encrypt": false,
        "Query": "SELECT * FROM oo.oocontact_import where organization_id<>1229"
    },

 

Cheers

Martyn

[lee mcdermott]

@James Ainsworth

Hi James finally managed to get around to trying this as I have been off for a while.

 

I have managed(eventually) to get the files downloaded and setup. When i run the utility it says it connects but doesn't seem to create the contact in the csv file see screen shots below.

I noticed the instructions on your wiki mentions a  Schema.ini file which seems to dictate which fileds contain which type, but I cannot find a schema.ini file anywhere so not sure if i need this.

The csv file doesnt contain a header row as the example on the wiki doesn't appear to have one and I wasn't sure what to put in if it did need one? Not sure if this is anything to do with the issue?

any idea? I have tried adding and removing extra fields in both the csv file and the mapping file.

 

thanks

lee

 

[lee mcdermott]

@James Ainsworth

 

Hi James or anybody,

any idea why this import does not work?

 

thanks

lee

[Martyn Houghton]

@lee mcdermott

It does not look like it is reaching the csv file. Can you post the ODBC Connector screenshots as per the wiki from the client you are using.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

hi martin

 

screen shot attached. I have just ran it again as i noticed the column header was ticked and as you can see from my csv file above i do not have any column headers.

I ran the utility again and it has ran ok this time.

 

However it has not created a contact called Atest?

 

it says successful and given a contact ID  - but when checking in contacts in Hornbill I can see contact ID it created but all the values are empty?

 

Any ideas. I suspect something to do with the CSV file and format , do I need to use column headers?

[Martyn Houghton]

@lee mcdermott

I think you are going to need to use column headings in the format of FIELD1, FIELD2 etc to match the mapping in the JSON and then enable it in the CSV ODBC config as well.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

 

Hi Martyn,

 

I tried that this morning but still get the same blank account created?

 

my csv headings are 

logon_id,firstname,lastname,company,email_1

I wonder if it needs certain fields that I have not specified?

[Martyn Houghton]

@lee mcdermott

Have you updated the 'ContactMapping' section to use the new column headings?

Cheers

Martyn

[lee mcdermott]

Yeah as below,

 

unless this is wrong?

[Martyn Houghton]

@lee mcdermott

Do your column headings now appear in the CSV ODBC connector setup?

Can you update the field references to your column headings, i.e. '{{.FIELD1}}' to be '{{.logon_id}}' etc,

Cheers

Martyn

[lee mcdermott]

yeah thats done,

 

I'm beginning to think it would have been easier to manually add all 100+ account...

[Martyn Houghton]

@lee mcdermott

Can you post your json conf file and a screenshot of the csv file in notepad.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

 

Hi Martin

 

"APIKey": "******************",
    "InstanceId": "*************",
    "ContactAction": "Create",
    "AttachCustomerPortal": true,
    "CustomerPortalOrgView": true,
    "CustomerPortalOrgViewRevoke": false,
    "UpdateContactStatus": false,
    "SQLConf": {
        "Driver": "csv",
        "Server": "localhost",
        "Database": "CSV64",
        "UserName": "**********",
        "Password": "*******",
        "Port": 5002,
        "ContactID": "FIELD1",
        "FieldID": "h_logon_id",
        "Encrypt": false,
        "Query": "SELECT * FROM adbc.csv"
    },
    "ContactMapping": {
        "logon_id": "{{.FIELD1}}",
        "firstname": "{{.FIELD2}}",
        "lastname": "{{.FIELD3}}",
        "company": "{{.FIELD4}}",
        "email_1": "{{.FIELD5}}",
        "email_2": "",
        "tel_1": "",
        "tel_2": "",
        "jobtitle": "",
        "description": "",
        "notes": "",
        "country": "",
        "language": "",
        "private": "0",
        "rights": "0",
        "contact_status": "0",
        "custom_1": "",
        "custom_2": "",
        "custom_3": "",
        "custom_4": "",
        "custom_5": "",
        "custom_6": ""
    }

 

 

[Martyn Houghton]

@lee mcdermott

The contact rows in the csv file need to be  encased in "", i.e. "BTest@test.co.uk","B"..........

Then update json 
 "ContactID": "logon_id",
        "FieldID": "h_logon_id",
        "Encrypt": false,
        "Query": "SELECT * FROM adbc.csv"
    },
    "ContactMapping": {
        "logon_id": "{{.logon_id}}",
        "firstname": "{{.firstname}}",
        "lastname": "{{.lastname}}",
        "company": "{{.company}}",
        "email_1": "{{.emial_1}}",

 

Then give that a go.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

thanks for that..still getting the same though of blank values?

 

 

[Martyn Houghton]

@lee mcdermott

Can you upload the log file generated from the last run.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

 

here you go

SQL_Contact_Import_2019-05-24T10-43-34+01-00.log

[Martyn Houghton]

@lee mcdermott

The json configuration seems to be fine. It appears the field names from the ODBC connector definition are not getting picked up so the mapping return null.

To test this, if you put hardcoded values in the mapping i.e. "tel_1":"12345678",

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

 

do you mean in the contact mapping part of the conf.json file?

 

As I tried that but that fails not sure if it is because it has an sql query and is looking at the csv file still

[Martyn Houghton]

@lee mcdermott

Yes, on the conf.json. When we did out imports we put some hard coded values in to the config.

Can you upload the conf and log.

Cheers

Martyn

[lee mcdermott]

@Martyn Houghton

wasnt sure if it needed the dot before the value so I have tried both with and without.

Or have I done this totally wrong?

"firstname": "{{B}}", and "firstname": "{{.B}}",