CODEWITHSUNDEEP
mysqlsqlexcelcsv
confused
confused

Reputation: 3

Importing data from CSV file in MySQL

I have created a table called StatesandCities with 3 columns: CountryCode(its a foreign key), State and City, like shown below:

create table StatesandCities (CountryCode varchar(2), State varchar(20), city varchar(20),foreign key (CountryCode) references ListOfCountries (CountryCode));

I have a .CSV file with all the values:

CountryCode,State,City
IN,Karnataka,Bangalore
IN,Karnataka,Mysore
IN,Karnataka,Tumkur
IN,Maharashtra,Mumbai
IN,Maharashtra,Thane
IN,Maharashtra,Nasik
GB,Yorkshire,County Hill
GB,SouthWales,Murrey

And this is exactly how it appers in an Excel sheet:

CountryCode State   City
IN  Karnataka   Bangalore
IN  Karnataka   Mysore
IN  Karnataka   Tumkur
IN  Maharashtra Mumbai
IN  Maharashtra Thane
IN  Maharashtra Nasik
GB  Yorkshire   County Hill
GB  SouthWales  Murrey

I used this code to input the CSV file in MySQL 5.6 command-line client:

LOAD DATA LOCAL INFILE 'H:/Statesncities.csv' INTO TABLE StatesandCities FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ignore 1 lines (CountryCode, State, City);

The values are getting entered but they are not in the right table format I need. And also if the data value is long its getting cut. It's all mixed up: I've tried like changing the screen size, page layout, nothing seems to work.

OUTPUT

OUTPUT:

Upvotes: 0

Views: 318

Answers (2)

Paulo Freitas
Paulo Freitas

Reputation: 13649

Try this one:

LOAD DATA LOCAL INFILE 'H:/countries.csv' INTO TABLE ListOfCountries
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES (CountryName, CountryCode);

I'm using IGNORE 1 LINES to skip CSV header line. I'd also turned FIELDS ENCLOSED BY optionally.

You can refer to LOAD DATA INFILE Syntax section of MySQL manual for more details.

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

this should do it !! 

BULK INSERT mycountry
        FROM 'C:\CSVmydata\country.csv'
        WITH
        (
        FIRSTROW = 2,
        FIELDTERMINATOR = ',',  --CSV field delimiter
        ROWTERMINATOR = '\n',   --Use to shift the control to next row
        ERRORFILE = 'C:\CSVmydata_error\countryErrorRows.csv',
        TABLOCK
        )

Upvotes: 0

Related Questions