CODEWITHSUNDEEP
pythonmysqlcsv
user3804623
user3804623

Reputation: 165

Import multiple CSV fields into one MySQL field

I have a table like this

mytable(`id` int, 'number1' varchar(11), 'number2' varchar(1200))

Also I have cvs-like file

111111111,222222222,333333333,44444444,,,
222222222,333333333,
111111111,555555555,666666666,

They are separated by ","(or something else) The csv have 100 colunms.

I would like to combine the second column to 100rd loumn into mysql "number2", the first column into mysql "number1".

like this:

id number1    number 2
1  111111111  222222222,333333333,44444444
2  222222222  333333333
3  111111111  555555555,666666666

So can I use LOAD DATA INFILE to load the file into the table? How can I do this..? or have other method?

thanks.

Upvotes: 1

Views: 193

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

Here is an option which creates a new column combining columns 2 through 100:

LOAD DATA INFILE 'input.csv'
INTO TABLE myTable
COLUMNS TERMINATED BY ','
IGNORE 1 LINES 
(id, number1, number2, ..., number99) 
SET newCol = CONCAT(NULLIF(number1, ''), NULLIF(number2, ''), ..., NULLIF(number99, ''));

Then, you can remove columns 2 through 100 from within MySQL:

ALTER TABLE myTable
DROP COLUMN number1,
DROP COLUMN number2,
...
DROP COLUMN number99

Upvotes: 1

yiming.xie
yiming.xie

Reputation: 23

you can use the function file() to open the cvs file ;

use the function explode(',', $each_line) to seperate each line with ,,

I did not understand your demand clearly,I think simple code could like this:

$file_items = file('file.cvs');
foreach($file_items as $item) {
    $item_arr = explode(',', $item);
    // combine $item_arr[0]... $item_arr[99] ,you get $number1, $number2;
    $sql_insert = "INSERT INTO mytable (number1, number2) VALUES(number1, $number2)";
}

Upvotes: 0

Related Questions