我已经试了好几天来解决这个问题。我有一个PandasDataframe,我将其导出为如下csv文件:
df.to_csv(csv_name, index=False, header = False, encoding='utf-8-sig', sep='\t')
我需要使用bcp将此csv文件上载到sql server数据库,使用以下命令:
bcp [DB].[dbo].[Table] in file.csv -Sserver -Uuser -Ppass -c -C65001 -t "\t" -e error.log
每次运行时,都会出现以下错误:
Error = [Microsoft][ODBC Driver 17 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.
在查看error.log时,我发现要上载的日期值有问题。在表定义中,日期格式为“smalldatetime”。我想上传的实际值是“2020-05-30 12:55:22”。
我只是将一行Dataframe导出到csv。
这是我收到的错误消息(同时显示csv文件的内容):
@ Row 1, Column 8: Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. @#
165405677 156147965 1135358 1425879 3.5 "Nice value ??Bordeaux Blend ???
Deep purple color. Blackberry plum tobacco and oaky with hints of pepper. Full-bodied dry and slightly spicy taste.
Let it breathe for at least 1 hr
Pairs well with pecorino and salami crostini??
# uncorkingArgentina" en 2020-05-30T19:55:42.000Z True 2 444897692 67 8 "49 292 320 334 422" "'blackberry' 'oaky' 'pepper' 'plum' 'tobacco'" 3353 0 Completed 89036 3.7 536982,90,Normal,71,3.6,484,26,,,"Nice value ??Bordeaux Blend ???
Deep purple color. Blackberry, plum, tobacco and oaky with hints of pepper. Full-bodied, dry and slightly spicy taste.
Let it breathe for at least 1 hr
Pairs well with pecorino and salami crostini??
# uncorkingArgentina",False,True
这是我在sql server中的表定义
CREATE TABLE [Table] (
[column1] bigint PRIMARY KEY,
[column2] bigint,
[column3] bigint,
[column4] bigint,
[column5] float(53),
[column6] nvarchar(1500) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column7] nvarchar(5) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column8] smalldatetime,
[column9] bit,
[column10] nvarchar(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column11] bigint,
[column12] integer,
[column13] integer,
[column14] nvarchar(150) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column15] nvarchar(500) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column16] bigint,
[column17] integer,
[column18] nvarchar(50) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column19] integer,
[column20] float(53),
[column21] integer,
[column22] integer,
[column23] nvarchar(60) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column24] integer,
[column25] float(53),
[column26] integer,
[column27] integer,
[column28] nvarchar(255) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column29] nvarchar(255) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column30] nvarchar(1500) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[column31] bit,
[column32] bit
)
GO
我试图上传另一行,但我总是对其中每一行的日期值有问题,无论该行的其他内容是什么。
我想尽一切办法把它修好。你认为这个问题的原因是什么?谢谢您!
1条答案
按热度按时间flmtquvp1#
这太长了,不能发表评论。
将数据加载到所有列都是字符串的暂存表中。
然后您可以查看数据并确定转换错误发生的位置,使用如下逻辑: