How to load csv data with quotes and ENCLOSED BY into MariaDB table

20 January,2020 by Jack Vamvas

Question : I have a csv file , with 1 million rows and would like to import into a MariaDB table.I'm importing successfully but having a problem with a row where the ","  is within a double quote. Example row:

GenerationX,Rufus,Johnny,"Chat hype, Power crazy"

i.e instead of getting 4 columns , I'm getting 5 , because the comma within the 4th column is splitting the data

Could you give me an example of how to load the data and maintain the data integrity ?  

Answer:  When loading data into a MariaDB table  the optional : ENCLOSED BY can help you. 

In the example statement below - there are  columns in a csv file where the first line is the headers. The delimiter of the columns is a "," and the ENCLOSED BY which will exclude "," being used as a delimiter when the column is encapsulated by the "" .


LOAD DATA LOCAL INFILE "/tmp/mycsv.csv" INTO TABLE MYDB.TABLE1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

 

Looking at the original sample row published - ENCLOSED BY should deal with this problem. 

GenerationX,Rufus,Johnny,"Chat hype, Power crazy"

Read more on MariaDB

MariaDB Cheatsheet

 

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to load csv data with quotes and ENCLOSED BY into MariaDB table


dba-ninja.com