0

My question is not how to export .csv file data into MySQL. My question is how to first add a column to the existing table and then populate that newly created column with the data from .csv file. For example, imagine I have a table with 2 columns right now and 400 rows:

+----------------+------------+
| username       | first_name |
+----------------+------------+
| abc            | Hun        |
+----------------+------------+
...

I have this .csv file (also with 400 rows):

## column1 ##   
ID,75u
PID,2_3_b
LID,3_b
...

This is what I have tried to do:

LOAD DATA INFILE '/path/to/file/example.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@col1, @col2) SET new_column = @col2;

I received this error afterwards:

ERROR 1054 (42S22): Unknown column 'new_column' in 'field list'

So I had to create a new column with this name:

ALTER TABLE my_table ADD new_column VARCHAR(400) AFTER first_name;

After this, I was able to insert the data from .csv file but what it did was this:

  • The data were appended vertically (instead of horizontally). i.e., my table now has 800 rows because it inserted new .csv data below old data while giving null values (for 401-800 rows) to 2 pre-existing columns.

How can I append the new column to the table and then append .csv data to that new column horizontally (ideally in 1 step)?

tera_789
  • 489
  • 4
  • 20
  • Doubt very much you can do this in one step. You should load your data into a load table, then do a second update/insert step in a second query. – TomC Sep 20 '18 at 23:54
  • A sure way is to have the column created in one step then perform the load as in the last answer of: https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table – NoChance Sep 20 '18 at 23:58
  • @NoChance the last answer there is using PHP – tera_789 Sep 21 '18 at 00:01
  • @TomC can you please provide an example? – tera_789 Sep 21 '18 at 00:03
  • It depends on the sort order, anyway, search for the text "Fahad", he is the user who provided the answer that contains the statements to match your case. Always be aware that the data in the table may be whipped out. – NoChance Sep 21 '18 at 00:03
  • @NoChance I saw his answer but it is not much different from mine. Where does he create a new row? – tera_789 Sep 21 '18 at 00:19
  • The new row is created for you by the load utility. It reads each input row and inserts a new one filled with data from the input. The major difference here is that your code is trying to create a column within this process....Create the new column in a separate step then run the code from Fahad (after customization to your case). There is no need for the SET... – NoChance Sep 21 '18 at 00:25
  • @NoChance Sorry I meant 'where does he create a new column'? But this is what I am saying, I want to to do all of it in 1 step. – tera_789 Sep 21 '18 at 00:34
  • You can do it in one "stored procedure" composed of two steps. To create a new column you use the syntax: ALTER TABLE table ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column]; To create a stored procedure, see: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html – NoChance Sep 21 '18 at 00:39
  • You may *want* to do it in one step, but its a multiple-step task. Only option if you want to make it *look* like one step is to wrap the 2 or 3 steps in a stored proc as @NoChance suggests. – TomC Sep 21 '18 at 00:46

0 Answers0