英文:
Importing csv into mysql wont work properly
问题
当我运行这段代码时，我得到以下错误：
错误代码：1292. 行1的'birthdate'列的日期值不正确：'Koeln'
我有一种感觉，也许日期中的 - 导致了这个错误，因为它们可能被视为逗号，当遇到它们时，可能会解析下一列。我不完全理解为什么会发生这个错误。有谁知道我该如何处理这个问题吗？我感激任何帮助。
英文:
I have this database table:
and I am trying to import a csv, which has the following structure:
first_name,last_name,street,number,locality,country,postal_code,birthdate,birth_place,created_at,last_updated,owner_id,nationality,user_id
Ronald,Hofmann,Wilhelmstrasse,387,Berlin,Germany,12043,2005-06-15,Koeln,20190710171824,20190710171824,15,Deutsch,14
Horst-Dieter,Wulff,Markgrafendamm,448,Berlin,Germany,12157,1978-05-20,Muenchen,20190710171824,20190710171824,16,Deutsch,15
Jaqueline,Meister,Markgrafendamm,296,Berlin,Germany,10115,1954-07-06,Hamburg,20190710171824,20190710171824,17,Deutsch,16
using the following MySQL code:
LOAD DATA INFILE 'C:\csv\\licence_owner.csv'
INTO TABLE licence_owner
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Despite that when I run the code I get the following error:
Error Code: 1292. Incorrect date value: 'Koeln' for column 'birthdate' at row 1
I have the feeling that maybe the - inside the date provoce this error, as they could work like a comma and perhaps when they are encountered the next column is being parsed? I dont fully understand why this error occurs. Does anyone know how I could go about it? I appreciate any help
答案1
得分: 1
您的数据缺少中间名。使用列列表加载数据：
LOAD DATA INFILE 'C:\csv\licence_owner.csv'
INTO TABLE licence_owner
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(first_name, last_name, street, number, locality, country, postal_code,
birthdate, birth_place, created_at, last_updated, owner_id,
nationality, user_id
)
英文:
Your data is missing the middle name. Use a column list to load the data:
LOAD DATA INFILE 'C:\csv\\licence_owner.csv'
INTO TABLE licence_owner
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(first_name, last_name, street, number, locality, country, postal_code,
birthdate, birth_place, created_at, last_updated, owner_id,
nationality, user_id
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论