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 CiviMail (Moderator: Piotr Szotkowski) »
  • Import Of Larger CSV Files Not Working
Pages: [1] 2

Author Topic: Import Of Larger CSV Files Not Working  (Read 1848 times)

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Import Of Larger CSV Files Not Working
July 16, 2012, 02:48:17 pm
Good Afternoon All!

I've searched and found non-solutions in the form of work-arounds but no actual t fixes to this issue

I want to import 10000+ records into civimail

I've tweaked php by increasing the timeout and memory allocation and still get error 500 after the import fails

I'll get between 5% and 20% before the import pukes

Has anyone figured out a way to import larger csv's without having to break them into smaller file sizes?

I was going to duplicate the installation locally using WAMP, import then upload the new database

Has anyone had any success doing that?

Any suggestions?

Thanks!

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Import Of Larger CSV Files Not Working
July 16, 2012, 05:14:30 pm

Might want to check this session out from the SF CiviCon

http://sf2012.civicrm.org/sessions/breaking-your-proprietary-software-habit-best-practices-data-import-civicrm

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 06:55:47 am
I reviewed the material and nothing stands out in terms of a possible solution

I upped the amount of RAM and CPU power on the server to something that should more than handle anything I'd need

Seeing as the import is a csv is there a preferred encoding UTF-8 versus US ASCII et al?

Thanks

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 07:40:36 am
Quote from: b0b_z on July 17, 2012, 06:55:47 am
I upped the amount of RAM and CPU power on the server to something that should more than handle anything I'd need

I have run imports which took as much as 30 minutes of CPU time. Just have to set the machine and be patient. :)

Quote from: b0b_z on July 17, 2012, 06:55:47 am
Seeing as the import is a csv is there a preferred encoding UTF-8 versus US ASCII et al?

UTF-8 is probably the best.
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.

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 07:45:45 am
Thank you for your reply...

The waiting isn't the issue, it's the server timing out at between 5 and 20% of the import and generating a 500 error

Hmmm...

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 07:47:36 am
Well if 20% works, then increase your CPU and RAM by more than 5 and then 100% should work. If all else is equal and all factors are linear.

Not all else is equal nor are things linear, however, so increase by 10 and try again. :)
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.

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 02:15:46 pm
lol...

We're running 3.4gHz CPU with 6gigs of RAM

Not sure how much more power I can givver cap'n

I guess increasing CPU and RAM by an order of magnitude for each 20% would be 10 to the 5th, right

3.4gHz x 100000 = 340tHz (TeraHertz)

Holy ****

If that were possible, I would have most likely ripped a hole in the time space continuum and been done about 15 years ago

Thanks!

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Import Of Larger CSV Files Not Working
July 17, 2012, 07:28:15 pm

curios as to how complex is your import file? what are the columns?

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 03:26:04 am
Quote from: b0b_z on July 17, 2012, 02:15:46 pm
I guess increasing CPU and RAM by an order of magnitude for each 20% would be 10 to the 5th, right

No, 5 to the 10th, left.

But what I really meant was not the actual CPU itself, rather the max_execution_time value defined in php.ini. :)
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.

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 06:20:05 am
The columns are rather simple

The total number of columns are 11 and only 9 are imported

individual_prefix   first_name   first_name_calc   last_name   organization   job_title   email   data_source   email_greeting       email_greeting_calc   region

All simple text in the various fields

Quote from: Donald Lobo on July 17, 2012, 07:28:15 pm

curios as to how complex is your import file? what are the columns?

lobo

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 06:58:39 am

if u r importing 10,000 records, are you saying it dies when importing between 500 and 2000?

that does not seem right? On my 4 year old macbook pro, i can easily import a few K records without too much trouble

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 07:55:49 am
That's correct

It'll error 500 out at between 5% to 20%

That's why I'm asking for some help...

I've always had this issue for as long as I've been using the software.

I've had "success" by breaking the csv files down to 1000 records and importing but it's a hassle

The file sizes are not that big and I figured 10K records shouldn't be an issue

I've tried with firefox, IE etc

I don't know if the issues are with the data having a funky character making the import puke
OR
The browser is causing the issue
OR
the php is not quite configured properly
OR
The import settings (dup checking/date et al) are not optimum
OR ....



Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 08:05:10 am

if i had to guess its a combination of your php settings and/or your web server config (more likely the former)

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

b0b_z

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 0
  • CiviCRM version: 4.1.6
  • CMS version: Drupal 7.19
  • MySQL version: 5.1.63
  • PHP version: 5.2.17
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 08:26:33 am
Other than max_execution_time, what else should I look at?

max_input_time?
mysql.connect_timeout?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Import Of Larger CSV Files Not Working
July 18, 2012, 09:16:33 am

might want to check the error logs and see the reason for the 500 error? there probably is a lot more information there that will give you a few clues

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions
Pages: [1] 2
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviMail (Moderator: Piotr Szotkowski) »
  • Import Of Larger CSV Files Not Working

This forum was archived on 2017-11-26.