导入CSV到MySQL不会正常工作。

huangapple go评论71阅读模式
英文:

Importing csv into mysql wont work properly

问题

当我运行这段代码时,我得到以下错误:

错误代码:1292. 1'birthdate'列的日期值不正确:'Koeln'

我有一种感觉,也许日期中的 - 导致了这个错误,因为它们可能被视为逗号,当遇到它们时,可能会解析下一列。我不完全理解为什么会发生这个错误。有谁知道我该如何处理这个问题吗?我感激任何帮助。

英文:

I have this database table:

导入CSV到MySQL不会正常工作。

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
    )

huangapple
  • 本文由 发表于 2020年1月3日 19:46:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/59578069.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定