2

I am looking for help to load a .csv file into MySQL Server using batch and I have few basic questions.

Issues:

1) The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling these data?

2) To load CSV from Excel then the data that have comma are enclosed within "" (double quotes) [as the below example] so how do the import can handle this?

Here is the sample CSV with header:

name,id,Subject,ExamDate,Mark,Description
Prabhat,1,Math,2/10/2013,25,Test data for prabhat.
Murari,2,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,3,Science,,25,Test Only.

The SQL statement used to import:

load data local infile "c:\\eqa\\project\\input.csv" into table request character set latin1 fields terminated by',' lines terminated by'\n' ignore 1 lines

How can I do this, is it possible?

1 Answer 1

3

Since you are using load data local infile and Loading Data into a Table using MySQL, there is a simple solution which is provided as part of this functionality which will help you to resolve this issue.

You simply want to be sure to include the ENCLOSED BY '"' subclause as part of the FIELD clause in the MySQL syntax you are using— below is an example of this.

load data local infile "c:\\eqa\\project\\input.csv" 
into table request 
character set latin1 
fields terminated by','
ENCLOSED BY '"'
lines terminated by'\n' 
ignore 1 lines

This tells MySQL the input file has field values that are both enclosed by double quotes and separated by commas—All characters between double quotes will then be imported.


Further Resources

  • Loading Data into a Table
  • LOAD DATA INFILE Syntax
    • LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:

      LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n'
        IGNORE 1 LINES;
      

      For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true regardless of whether OPTIONALLY is specified; OPTIONALLY has no effect on input interpretation.) Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

      If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and are interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotation marks are handled as shown here:

      "The ""BIG"" boss"  -> The "BIG" boss
      The "BIG" boss      -> The "BIG" boss
      The ""BIG"" boss    -> The ""BIG"" boss
      

      source

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .