Avatar of theideabulb
theideabulb
Flag for United States of America asked on

MySQL Load Data Infile Dates are all zeros

I am using the Load Data Infile process to load large data files.  The columns that are supposed to be dates are coming up all zeros.  How can i make sure that the dates stay proper dates?

LOAD DATA INFILE "C:\\mydatafile.txt"
INTO TABLE data_test
COLUMNS TERMINATED BY X'7F'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'

Open in new window

DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment
theideabulb

8/22/2022 - Mon
PortletPaul

Can you provide sample(s) of the file(s)?

(or look at it this way: "How do we know what is in the files?")
theideabulb

ASKER
Here is what it looks like


111370110043LEGO Star Wars Anakin Skywalker grown up with cape from  7113 - NKH NLJ01-OCT-14 12:19:45122711900608.9920Y

111456103707LEGO Star Wars - 2 Rare Original Printed Leg Stormtroopers - Excellent - 726401-OCT-14 08:14:02257119006048.0010Y

Open in new window


You don't really see the delimiter in a pasted example
PortletPaul

This might help. I placed those 2 rows into Notepad++, this is what it looks likedelimiters visible
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
theideabulb

ASKER
Yes those breaks are correct for the columns of data
Tomas Helgi Johannsson

Hi!

Can you post the describe table output for the table data_test ?

Regards,
      Tomas Helgi
theideabulb

ASKER
all the table data looks good except for the date.  that looks like

0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
theideabulb

ASKER
Thank you