Importing from Excel "databases"

Questions, tips and tricks and techniques for scripting in Servoy

Importing from Excel "databases"

Postby robwormald » Tue Jun 21, 2011 10:40 pm

Hi All

Just needing a push in the right direction - I'm sure this is possible but I'm not sure where to start...

I've just finished setting up a fairly simple CRM setup for our company - for the purposes of this posts, lets say we've got just a few tables - Customers, Contacts, and Jobs.

Within Servoy, I've got all the appropriate relations set up (ie, Customers to Contacts, Customers to Jobs, Etc) - and it works happily.

The next task is to bring in the 1000's rows of old data stored in excel spreadsheets and google docs. These are of course not databases - I've got a single excel sheet with customers, contacts and jobs all in the same row, ie :

<CustomerName><Contact First><Contact Last><ContactTel><JobName><JobDescription>

I've used Google's refine tools (http://code.google.com/p/google-refine/) to clean up these lists, so now I've got a huge spreadsheet, nicely formatted where all of the errors have been corrected (ie Company1 and company1 are now the same) and its as clean as a non-related sheet can be.

What I'd like to do now is import this sheet into the CRM, while retaining the relation information. There are plenty of duplicates (for example, multiple contacts on the same company means a row for each contact, which means a duplicate rows for companies) that i'd like to combine when they get imported.

Could somebody point me in the right direction here? I guess I need to write a method that imports all the customer data, then starts again, and somehow reconciles the contacts to each customer so that the relations are added? Just a little stuck!
robwormald
 
Posts: 20
Joined: Sun Feb 13, 2011 7:55 am

Re: Importing from Excel "databases"

Postby Thomas Parry » Wed Jun 22, 2011 5:04 am

My 2 cent approach:
In the past whenever I had to do this I used pretty well a simple design approach:
1.Export from the spreadsheet to csv where first line contains the column titles.
2.As you say run a custom script to read this csv file line by line and create the company first then add a related record for contacts/jobs etc as required.

I have used this module to make things easier as well:
https://www.servoyforge.net/projects/csvimporter

You will also need to ensure that you do handle duplications in some manner...Maybe after doing the import.
Tom Parry
Prospect IT
Java/C++/Servoy/Jasper Reports/Simulation/Service Applications
http://www.prospect-saas.biz
Thomas Parry
 
Posts: 498
Joined: Thu Jan 10, 2008 8:48 pm
Location: Ottawa, Canada

Re: Importing from Excel "databases"

Postby Kaptan » Wed Jun 22, 2011 9:53 am

Do you have any relation in the spreadsheets now?
If they have a related field, why not just use that as your primary- and foreign key when importing?
After the import you only have to update your sequences.
Sem
Kaptan
 
Posts: 124
Joined: Mon Aug 04, 2008 11:17 pm
Location: Amsterdam


Return to Methods

Who is online

Users browsing this forum: Bing [Bot] and 5 guests