Load data from text file to DB

Question

Data:

1|\N|"First\Line"
2|\N|"Second\Line"
3|100|\N

\N represents NULL in MYSQL & MariaDB. I'm trying to load above data using LOAD DATA LOCAL INFILE method into a table named ID_OPR.

Table structure:

CREATE TABLE ID_OPR (
  idnt decimal(4),
  age decimal(3),
  comment varchar(100)
);

My code looks like below:

LOAD DATA LOCAL INFILE <DATA FILE LOCATION> INTO TABLE <TABLE_NAME> FIELDS TERMINATED BY '|' ESCAPED BY '' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';

Problem with this code is it aborts with error Incorrect decimal value: '\\N' For column <Column name>.

Question: How to load this data with NULL values in second decimal column and also without loosing \(Backslash) from third string column?

I'm trying this is MariaDB which is similar to Mysql in most case.

Update: The error i have mentioned appears like a warning and the data is actually getting loaded into table. But the catch here is with the text data.

For example: Incase of the third record above it is being loaded as \N itself into string column. But i want it to be NULL.

Is there any way to make the software to recognize this null value? Something like decode in oracle?

Answer 1

we need three text file & 1 batch file for Load Data: Suppose your file location 'D:\loaddata' Your text file 'D:\loaddata\abc.txt'
1. D:\loaddata\abc.bad -- empty
2. D:\loaddata\abc.log -- empty
3. D:\loaddata\abc.ctl
a. Write Code Below for no separator

OPTIONS ( SKIP=1, DIRECT=TRUE, ERRORS=10000000, ROWS=5000000) 
load data 
infile 'D:\loaddata\abc.txt' 
TRUNCATE
into table Your_table
(
 a_column          POSITION   (1:7) char,
 b_column          POSITION   (8:10) char,
 c_column          POSITION   (11:12) char,
 d_column          POSITION   (13:13) char,
 f_column          POSITION   (14:20) char
)

b. Write Code Below for coma separator

OPTIONS ( SKIP=1, DIRECT=TRUE, ERRORS=10000000, ROWS=5000000) 
load data 
infile 'D:\loaddata\abc.txt' 
TRUNCATE
into table Your_table
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(a_column,
 b_column,
 c_column,
 d_column,
 e_column,
 f_column
 )

4.D:\loaddata\abc.bat "Write Code Below"

sqlldr db_user/db_passward@your_tns control=D:\loaddata\abc.ctl log=D:\loaddata\abc.log

After double click "D:\loaddata\abc.bat" file you data will be load desire oracle table. if anything wrong check you "D:\loaddata\abc.bad" and "D:\loaddata\abc.log" file

Answer 2

Deal with nulls with blanks. that should fix it.

1||"First\Line"
2||"Second\Line"
3|100|

Thats how nulls are handled on CSVs and TSVs. And don't expect decimal datatype to go null as it stays 0, use int or bigint instead if needed. You should forget about "ESCAPED BY"; as long as string data is enclosed by "" that deals with the escaping problem.

Answer 3

You can't have it both ways - either \ is an escape character or it is not. From MySQL docs:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So, I'd suggest a consistently formatted input file, however that was generated:

• use \\ if you want to keep the backslash in the strings
• make \ an escape character in your load command

OR

• make strings always, not optionally, enclosed in quotes
• leave escape character empty, as is
• use NULL for nulls, not \N

BTW, this also explains the warnings you were experiencing loading \N in your decimal field.