Import multiple CSV fields into one MySQL field
<p>I have a table like this</p>

<pre><code>mytable(`id` int, 'number1' varchar(11), 'number2' varchar(1200))
</code></pre>

<p>Also I have cvs-like file</p>

<pre><code>111111111,222222222,333333333,44444444,,,
222222222,333333333,
111111111,555555555,666666666,
</code></pre>

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

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

<p>like this:</p>

<pre><code>id number1    number 2
1  111111111  222222222,333333333,44444444
2  222222222  333333333
3  111111111  555555555,666666666
</code></pre>

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

<p>thanks.</p>

<p>Here is an option which creates a <em>new</em> column combining columns 2 through 100:</p>

<pre><code>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, ''));
</code></pre>

<p>Then, you can remove columns 2 through 100 from within MySQL:</p>

<pre><code>ALTER TABLE myTable
DROP COLUMN number1,
DROP COLUMN number2,
...
DROP COLUMN number99
</code></pre>

python mysql csv