[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mysql LOAD DATA INFILE Question.  pls help

Posted on 2007-10-11
4
Medium Priority
?
791 Views
Last Modified: 2010-07-27
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
0
Comment
Question by:Econify
4 Comments
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20063207
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 20063231
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
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 300 total points
ID: 20063240
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
 

Author Comment

by:Econify
ID: 20063554
Thank you guys.you are rockstars
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question