asked on

upload csv extract to database - server timeout

Hi All

I have a system where the client needs to upload a csv to their website (approx 4mb) the contents of which are extracted and placed into a mysql database.

I have created a script to process the csv data and separate it into 3 separate tables.

The script and the upload is working fine, the problem I have is that when the files is c. 4MB it is taking a long time to process (c. 4000 rows in the csv converted into 1 table with 3960 rows, another with 40 rows and the 3rd with 200,000+) and the browser is timing out.....

Clearly the big problem I have is the 200,000+ records... I am inserting each one individually.

INSERT INTO table(field1,field2,field3) VALUES(value1,value2,value3);

Is there a better way to do this?

Cheers

SOLUTION

dj_alik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cycledude

ASKER

dj_alik:

Thanks, I have though of using that but the problem is the CSV file is 4040 rows by 130 columns

the data is spilt into 3 tables

table 1 has 4 cols and c. 4000 rows
table2 has 4 cols and c. 40 rows
table3 has 4 cols and 200,000+ rows

Table3 contains 120 rows for each entry in table1 and table2, the data coming from 120 cols of the csv.

dj_alik

First load the data into a single table, then move it around to the needed tables using SQL statements. If you need something more complex some programming will be required.

cycledude

ASKER

so it will be faster to manipulate the database than csv?

cycledude

ASKER

how about something like

INSERT INTO tablename VALUES ( 'Bill', 29, 'Ford' ), ( 'Mike', 16, 'Beetle' ), ( 'Alisa', 36, 'Van' )");

how many 'values' can you put into a single sql statement?

cycledude

ASKER

I will need approx 120 sets of values.... can it be done this way?

ASKER CERTIFIED SOLUTION

cycledude

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION