CODEWITHSUNDEEP
phpmysqlarraysphpexcel
tushAR
tushAR

Reputation: 303

Insert large amount of Excel data into a MySQL database

I have large amount of data in my excel sheet. The excel sheet contains 5 to 6 tabs, and the columns are different in different tabs. Now I want to insert all data in database using array, but problem is due to no of columns. Is there any way to take columns name as array key name?

Here is my code:

$inserarray = array();

$inserarray['bl'][0] = "INSERT INTO  `new`.`bl` 
(`Code` ,`url` ,`date` ,`Bl` , `Title` ,`Sub`)";

$inserarray['bl'][1] = "VALUES ('1',  '1',  '12',  '12',  'ds',  's',  'sd',  's');";

$inser_query = $inserarray[$d1][0]; // [column_name][]

$process_insertarray[] = "('".implode("','",$projdetail)."','".implode("','",$sup)."')";

$final_query = $inser_query.''.'VALUES'.implode(',',$process_insertarray);

Upvotes: 1

Views: 1627

Answers (1)

Bere
Bere

Reputation: 1747

If it is large amount of data, I prefer exporting(save as) the excel data into CSV( or TSV) file and importing to DB.

Assume the exported file is called 'data.csv'. You can import the file using this command in mysql command interpreter.

load data local infile 'data.csv' into table YOURTABLE

Make sure you create a table with the same number(and type) of columns in the excel, and fire! I recommend creating separate tables for each tab (if the number and type of columns are different).

Upvotes: 1

Related Questions