SQL Importing Large CSV, Out of Memory Error

Question

I am trying to import a large (65mb) CSV file into a SQL table. I am using phpMyAdming and xampp. I have changed the following settings in php.ini:

memory_limit = 6000M
post_max_size = 128M
upload_max_filesize = 128M

When I try this I get error 'Fatal error: Out of memory (allocated 1852047360) (tried to allocate 262145 bytes) in C:\xampp\phpMyAdmin\libraries\import.lib.php on line 294'

What can I do to solve this error? I would also like to import a much larger database as well.

Preet Sangha · Accepted Answer · 2012-06-07 06:36:36Z

1

Could you break the file up into chunks?

answered Jun 7, 2012 at 6:36

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1441453 Over a year ago

I tried this before I could get the memory_limit raised and it had a similar error. I've seen people online getting large files to work and since I will soon have to import a much larger csv file I would like to get this working so I can avoid breaking the file into 100 pieces

user1441453 Over a year ago

I just tried again and it's still running out of memory after I lengthen the max execution time

matchdav · Accepted Answer · 2012-06-07 07:41:05Z

1

You could write a Python/PHP/Ruby script to read the file and parse the data, and run INSERT statements row by row.

answered Jun 7, 2012 at 7:41

matchdav

7351 gold badge7 silver badges16 bronze badges

5 Comments

user1441453 Over a year ago

I have a Java program that wrote the CSV file. For some reason, when I try writing to the table from java, the table took about 17 hours to create and for the larger table I want to create would take about a month. I was hoping this would be much faster. My current db is 7 columns with one 10-digit int and six 2-digit ints, and 2.5million rows. I don't know why it is taking so long when I try to write to it from java

matchdav Over a year ago

Well you could build a large query instead of writing every time, that was more what I was thinking.

matchdav Over a year ago

And the other option is the BULK INSERT

user1441453 Over a year ago

This doesn't seem to go any faster

Leigh Over a year ago

@user1441453 - (Edit) Which method is not faster? Also, it is important to include your database type and version with any sql question. So we know what options are available to you :)

Collectives™ on Stack Overflow

SQL Importing Large CSV, Out of Memory Error

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related