Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Import CSV to MySQL

I have created a database and a table. I have also created all the fields I will be needing. I have created 46 fields including one that is my ID for the row. The CSV doesn't contain the ID field, nor does it contain the headers for the columns. I am new to all of this but have been trying to figure this out. I'm not on here being lazy asking for the answer, but looking for directions.

I'm trying to figure out how to import the CSV but have it start importing data starting at the 2nd field, since I'm hoping the auto_increment will fill in the ID field, which is the first field I created.

I tried these instructions with no luck. Can anyone offer some insight?

  1. The column names of your CSV file must match those of your table
  2. Browse to your required .csv file
  3. Select CSV using LOAD DATA options
  4. Check box 'ON' for Replace table data with file
  5. In Fields terminated by box, type ,
  6. In Fields enclosed by box, "
  7. In Fields escaped by box, \
  8. In Lines terminated by box, auto
  9. In Column names box, type column name separated by , like column1,column2,column3
  10. Check box ON for Use LOCAL keyword.

Edit:

The CSV file is 32.4kb

The first row of my CSV is:

Test Advertiser,23906032166,119938,287898,,585639051,287898 - Engager - 300x250,88793551,Running,295046551,301624551,2/1/2010,8/2/2010,Active,,Guaranteed,Publisher test,Maintainer test,example-site.com,,All,All,,Interest: Dental; custom geo zones: City,300x250,-,CPM,$37.49 ,"4,415","3,246",3,0,$165.52 ,$121.69 ,"2,895",805,0,0,$30.18 ,$37.49 ,0,$0.00 ,IMPRESSIONBASED,NA,USD
like image 998
404error Avatar asked Jun 11 '10 19:06

404error


People also ask

How do I import a CSV file into MySQL?

In the Format list, select CSV. Changing format-specific options. If the csv file is delimited by a character other than a comma or if there are other specifications to the csv files, we can change it in this portion. Click Go to start importing the csv file and the data will be successfully imported into MySQL.

How do I import a CSV file into MySQL Workbench?

Importing CSV file using MySQL Workbench The following are steps that you want to import data into a table: Open table to which the data is loaded. Review the data, click Apply button. MySQL workbench will display a dialog “Apply SQL Script to Database”, click Apply button to insert data into the table.


3 Answers

You can have MySQL set values for certain columns during import. If your id field is set to auto increment, you can set it to null during import and MySQL will then assign incrementing values to it. Try putting something like this in the SQL tab in phpMyAdmin:

LOAD DATA INFILE 'path/to/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' SET id=null;
like image 90
webbiedave Avatar answered Oct 05 '22 01:10

webbiedave


Please look at this page and see if it has what you are looking for. Should be all you need since you are dealing with just one table. MYSQL LOAD DATA INFILE

So for example you might do something like this:

LOAD DATA INFILE 'filepath' INTO TABLE 'tablename' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (column2, column3, column4);

That should give you an idea. There are of course more options that can be added as seen in the above link.

like image 24
Kavet Kerek Avatar answered Oct 05 '22 00:10

Kavet Kerek


be sure to use LOAD DATA LOCAL INFILE if the import file is local. :)

like image 36
paiego Avatar answered Oct 05 '22 00:10

paiego