Mysql LOAD DATA INFILE Question. pls help

Hi guys

i have a text file like this to import into my table

67276,894,10/10/2007 02:23:44,67276760117,42002637885  
67276,894,10/10/2007 02:23:51,67276760117,42002637888  
66106,919,10/10/2007 02:13:53,66002999821,42002638008  
02451,919,10/10/2007 03:51:56,02420380161,42002637997  
94010,918,10/10/2007 02:09:42,94002279999,42002637999  
60607,918,10/10/2007 03:50:22,60805324231,42002637886  
90809,918,10/10/2007 01:41:11,90601432609,42002638042  
90809,918,10/10/2007 01:54:33,90601432609,42002637970  

Col 1,2,4,5 = integer and Col 3 = date

i am importing using this sql

"LOAD DATA INFILE '\home\file.txt' INTO TABLE confirmstats FIELDS TERMINATED BY ','  LINES TERMINATED BY '\\n' (scf,operation,processed_at,postnet,planet) ;

strangely the date is imported wrongly like this
autoid  scf       oper     processed_at              postnet             planet
--------------------------------------------------------------------------------------------------------------
9118        67276        894        0000-00-00 00:00:00        2147483647        2147483647
9119       67276       894       0000-00-00 00:00:00       2147483647       2147483647
9120       66106       919       0000-00-00 00:00:00       2147483647       2147483647
9121       2451        919        0000-00-00 00:00:00        2147483647        2147483647
9122       94010       918       0000-00-00 00:00:00       2147483647       2147483647
9123       60607       918       0000-00-00 00:00:00       2147483647       2147483647
9124       90809       918       0000-00-00 00:00:00       2147483647       2147483647
9125       90809       918       0000-00-00 00:00:00       2147483647       2147483647

There are 2 problems
1) processed at is all 0000
2) postnet and planet col has some strange kind of same indentical values inserted
in both columns

pls help me i guess i need to tweak the LOAD DATA query somehow to do it.
the problems seems to be with the third field which is date field.
the text file i receive does not enclose the date in " (quotes).
i cannot chane the textfile i receive from my client  so how can i use the enclosed by clause to put " around this date field after which i guess the rest of two fields would import properly

pls it wud be gr8 if some body try this out and help me with the sql.


thanks
jags
EconifyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ldunscombe: that looks like you got inspired from oracle.
in mysql, it will work a bit differently:


"LOAD DATA INFILE '\home\file.txt' INTO TABLE confirmstats FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY'"'
LINES TERMINATED BY '\\n' (scf,operation,@processed_at,postnet,planet) set processed_at = str_to_date(@processed_at, '%m/%d/%Y %T') ;

references:
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date
0
 
ldunscombeCommented:
Sorry I'm not right into SQL but you might try something like this.

"LOAD DATA INFILE '\home\file.txt' INTO TABLE confirmstats FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY'"'
(DATUM date "DD/MM/YY HH24:MI:SS", LINES TERMINATED BY '\\n' (scf,operation,processed_at,postnet,planet) ;
0
 
Steve BinkConnect With a Mentor Commented:
For `postnet` and `planet`, I would guess that your fields are defined as INT when you really need a BIGINT.  See this link for more information:

http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

For `processed_at`, I'm not quite sure, but it probably has something to do with lacking quotes.  If you're not able to change the text file, then I would recommend either user variables in the LOAD DATA statement, or a 2-step process to import the data:

1) Use LOAD DATA to import the date value into a varchar field
2) Translate the varchar field into date/datetime through a second query

For user variables, here's an example from the manual:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

You could easily modify that to use a date-formatting function.

0
 
EconifyAuthor Commented:
Thank you guys.you are rockstars
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.