Hello, please sign in or register
You are here: Home

Load Data from CSV into table

Loading data into a table using LOAD DATA

Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]

    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]

    [IGNORE number LINES]
    [(col_name,...)]

Example

My csv file contains five columns and the first row contains the column names, id, ISBN, title, url. I opnly need url, price and ISBN. I edit the csv file with this. There is a column in the table which is not included in the csv file 'affiliate'. I do not know otherwise however I have to create this field values in the CSV file. Else sack this off from the table and recreate it later with a default value. 

CREATE TABLE stock (
  isbn VARCHAR(10) NOT NULL,
  price DOUBLE, 
  url varchar(255)
) 

LOAD DATA

LOAD DATA INFILE "D:/websites/books/downloads/affiliates/thehut.csv" REPLACE

INTO TABLE stock FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

IGNORE 1 LINES

Alter table to add columns and set primary key

ALTER TABLE stock ADD COLUMN affilite VARCHAR(15) NOT NULL DEFAULT "thehut";

ALTER TABLE stock ADD PRIMARY KEY (isbn, affiliate);

Create CSV file from table

SELECT

ISBN, nielson_id, title, title_prefix, title_ ,

part_number, volume_title, sub_title, series_title,

series_volume_NO, format_codes, format_names, format_descr, edition,

page_length, page_size, map_scale, publisher_name,

country_publication, language, bic, descr_brief,

descr_full, publication_date, RRP, authors

INTO OUTFILE "D://websites/books/nielson.csv"

FIELDS TERMINATED BY ","

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY "\\r\\n"

FROM book

Comments

clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share