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?

Is the decimal field set to accept null values? Is it's default set to null? Also you should be able to escape the backslash using another backslash: "First\\Line" — whitwhoa
– whitwhoa, Commented Jul 11, 2016 at 13:29
Yes, decimal field can accept null values- Default set to Null. Modifying data manually or using code is not permitted. These records has to be processed using the generic script. — Vivek
– Vivek, Commented Jul 11, 2016 at 13:33
@e4c5: there is nothing in the real data than what is provided above. Since i cannot extract the production to a public website i have masked it and mentioned as simple text "First\Line". — Vivek
– Vivek, Commented Jul 11, 2016 at 14:45
FIrst\Line does not make sense. What is \L supposed to mean? — Rick James
– Rick James, Commented Jul 12, 2016 at 2:05

Unix One · Accepted Answer · 2016-07-12 06:14:24Z

3

+50

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.

answered Jul 12, 2016 at 6:14

Unix One

1,1818 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mrigesh Raj Shrestha · Accepted Answer · 2016-07-15 08:52:34Z

0

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.

edited Jul 15, 2016 at 8:52

answered Jul 15, 2016 at 8:46

Mrigesh Raj Shrestha

6204 silver badges16 bronze badges

Comments

Shahidul Islam Molla · Accepted Answer · 2017-04-27 08:46:29Z

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

Collectives™ on Stack Overflow

Load data from text file to DB

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related