MySQL Forums
Forum List  »  Newbie

Re: how to load non english numbers + date-format ?
Posted by: Lao De
Date: April 28, 2011 03:50PM

Thanks all for the hints.

Looks like I got 1 step closer to the core question: "how to convert date nd number while loading".

After searching the docu, I came to this date-loading test with just 1 column (more columns failed with to dificult errors, to isolate for date-formating-problems):

LOAD DATA INFILE 'C:/myPath/myFlatFile.txt'
INTO TABLE T_date_test
IGNORE 1 LINES
(@BTAG)
SET B_TAG = DATE_FORMAT(@BTAG,'%d.%m.%Y')
;

Query OK, 8 rows affected, 8 warnings (0.05 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 8

mysql> show warnings
-> ;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
' |ing | 1292 | Incorrect datetime value: '25.01.2010
' |ing | 1292 | Incorrect datetime value: '04.01.2010
' |ing | 1292 | Incorrect datetime value: '13.01.2010
' |ing | 1292 | Incorrect datetime value: '04.01.2010
' |ing | 1292 | Incorrect datetime value: '21.01.2010
' |ing | 1292 | Incorrect datetime value: '04.01.2010
' |ing | 1292 | Incorrect datetime value: '01.01.2010
| Note | 1265 | Data truncated for column 'B_TAG' at row 8 |
+---------+------+--------------------------------------------+
8 rows in set (0.00 sec)

When I look into table, 7 records are NULL and the last does not match any date from flat-file: 2020-01-20

I took the date-format-variables from Syntax-Reference for DATE_FORMAT. Must I use others ?

The syntax for loading was taken from reference-manual, but there is no converting-advice, so I had to guess, how to get those operations together.

Wherefrom can I learn about correct formatting-syntax in LOAD DATA ?

laode

Options: ReplyQuote


Subject
Written By
Posted
Re: how to load non english numbers + date-format ?
April 28, 2011 03:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.