CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Import (Moderator: Yashodha Chaku) »
  • Importing 400k Records
Pages: [1]

Author Topic: Importing 400k Records  (Read 1022 times)

josh.amato

  • I’m new here
  • *
  • Posts: 6
  • Karma: 0
  • CiviCRM version: 4.3.4
  • CMS version: Wordpress 3.5.1
  • MySQL version: 5.0.95-community
  • PHP version: 5.2.9
Importing 400k Records
August 13, 2012, 01:05:56 pm
I need to import 400,000 records into Civi as contacts. There are 29 columns per row. I am able to break up the files into 10k each and then upload, but it takes days to process.

Is there another way I can go about this? Perhaps writing a MySQL query or using Microsoft Access to import the data?

Anyone with experience with this?

clarkac

  • Administrator
  • Ask me questions
  • *****
  • Posts: 399
  • Karma: 11
  • CiviCRM version: 4.4.11 & 4.5.5
  • CMS version: Drupal 7
  • MySQL version: 5.1.61-cll
  • PHP version: 5.3.27
Re: Importing 400k Records
August 13, 2012, 01:13:47 pm
I would do them in batches (not sure how big) and would make the 'Source' field in the input correspond to each batch.  So if you find a problem with a batch or batches you can easily locate the offending records and remove them.  Sounds like you have a lot of work on!
Andy Clark

josh.amato

  • I’m new here
  • *
  • Posts: 6
  • Karma: 0
  • CiviCRM version: 4.3.4
  • CMS version: Wordpress 3.5.1
  • MySQL version: 5.0.95-community
  • PHP version: 5.2.9
Re: Importing 400k Records
August 13, 2012, 01:39:27 pm
I did break it into 10k batches. The issue is going that direction isn't reasonable. It takes approximately 5 hours to process 10k records, thats if it doesn't error out. That's over 2 weeks of 12 hour days just importing.

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Importing 400k Records
August 13, 2012, 02:00:56 pm
For large imports we use the Drupal Migration module - not sure how up to date our blogs are on this - but eg http://fuzion.co.nz/content/migrate-2-civicrm-api-3-civimigrate

Lets us rattle through 100000 at a time including rewinding, tweaking and rerunning etc.
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Importing 400k Records
August 14, 2012, 08:21:57 am
I have also written import scripts that import from CSV into CiviCRM using just PHP and the API.
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Importing 400k Records
August 14, 2012, 11:36:47 am
Hi Josh,

Check out the command line import tools:
http://civicrm.org/blogs/xavier/api_batch_tools

Note that they're broken for Civi 4.1, so either use Civi 4.0, 4.2, or merge the 4.2 changes into your 4.1 codebase if need be.

Also, some parts of a GUI-based import take FAR longer than others!  In particular, try to not create relationships unnecessarily.  I usually create relationships in a second-round import.  Also, if your data's been duplicate checked, turn off duplicate checking.

Finally, Eileen McNaughton and Young-Jin Kim did an excellent presentation on doing large dataset imports at CiviCon SF back in April: http://sf2012.civicrm.org/sessions/breaking-your-proprietary-software-habit-best-practices-data-import-civicrm
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Import (Moderator: Yashodha Chaku) »
  • Importing 400k Records

This forum was archived on 2017-11-26.