批量加载数据转换错误 [英] Bulk load data conversion error

问题描述

这是我在Exmaple.csv中的数据

符号：20MICRONS

系列：EQ

开放：30.5

HIGH：33.6

低：30.5

关闭：31.15

最后：30.55

PREVCLOSE：32.1

TOTTRDQTY：101214

TOTTRDVAL：3286508.05

TIMESTAMP：1-Mar-13

TOTALTRADES：989
ISIN：INE144J01027



这是我在sql中的表格

This is my data in Exmaple.csv
SYMBOL :20MICRONS
SERIES :EQ
OPEN :30.5
HIGH :33.6
LOW :30.5
CLOSE :31.15
LAST :30.55
PREVCLOSE :32.1
TOTTRDQTY :101214
TOTTRDVAL :3286508.05
TIMESTAMP :1-Mar-13
TOTALTRADES:989
ISIN :INE144J01027

this is my Table in sql

CREATE TABLE [dbo].[cmpDailyRateMaster](
	[SYMBOL] [varchar](50) NULL,
	[SERIES] [varchar](50) NULL,
	[OPENPRICE] [real] NULL,
	[HIGHPRICE] [real] NULL,
	[LOWPRICE] [real] NULL,
	[CLOSEPRICE] [real] NULL,
	[LASTPRICE] [real] NULL,
	[PREVCLOSE] [real] NULL,
	[TOTTRDQTY] [bigint] NULL,
	[TOTTRDVAL] [bigint] NULL,
	[TIMESTAMPDAY] [date] NULL,
	[TOTALTRADES] [int] NULL,
	[ISIN] [varchar](50) NULL
)

这是我的cmpDailyrate.xml文件

This is my cmpDailyrate.xml file

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharFixed" LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="5" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="6" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="7" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="8" xsi:type="CharFixed" LENGTH="30"/>
  <FIELD ID="9" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="21"/>
  <FIELD ID="11" xsi:type="CharFixed" LENGTH="11"/>
  <FIELD ID="12" xsi:type="CharFixed" LENGTH="12"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="SYMBOL" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="SERIES" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="OPENPRICE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="4" NAME="HIGHPRICE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="5" NAME="LOWPRICE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="6" NAME="CLOSEPRICE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="7" NAME="LASTPRICE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="8" NAME="PREVCLOSE" xsi:type="SQLFLT4"/>
  <COLUMN SOURCE="9" NAME="TOTTRDQTY" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="10" NAME="TOTTRDVAL" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="11" NAME="TIMESTAMPDAY" xsi:type="SQLDATE"/>
  <COLUMN SOURCE="12" NAME="TOTALTRADES" xsi:type="SQLINT"/>
  <COLUMN SOURCE="13" NAME="ISIN" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

$ b当我使用此命令时$ b

when i use this command

BULK
INSERT cmpDailyRateMaster
FROM 'D:\cm01MAR2013bhav.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
ERRORFILE ='D:\Error.txt',
FORMATFILE='D:\cmpDailyrate.xml'
)
GO

它会产生此错误

批量加载数据转换错误（指定代码页的类型不匹配或无效字符

怎么办？

it generates this error
Bulk load data conversion error (type mismatch or invalid character for the specified codepage
what to do?

推荐答案

我创建了2个表

1.temp表，其中来自csv文件和所有列的导入数据具有varchar数据类型

2.target表，其中所有列具有不同的数据类型



然后将数据从临时表加载到t arget table with cast。

i have created 2 table
1.temp table in which import data from csv file and all column has varchar datatype
2.target table in which all column has different datatype

then load data from temp table to target table with casting.

INSERT into [dbo].[Target]
SELECT [SYMBOL],[SERIES],Str([OPENPRICE], 18,4),Str([HIGHPRICE],18,4),
Str([LOWPRICE],18,4),Str([CLOSEPRICE],18,4),Str([LASTPRICE],18,4),Str([PREVCLOSE],18,4),convert(bigint,[TOTTRDQTY]),Str([TOTTRDVAL],18,4),
convert(date, [TIMESTAMPDAY], 105),convert(bigint,[TOTALTRADES]),[ISIN]
FROM [dbo].[temp]

这是另一个被ETL幻想咬伤的可怜的草皮。尝试将其更改为ELT，您有提取，现在将其加载到文件中而不进行任何转换，使目标表反映EXACT字段名称作为源并使所有字段varchar（500）。



现在编写一个存储过程来执行从目标（登台）表到目标表的转换。因为这是我生活中的40％，并且已经做了20多年，所以认真对待这个建议，你的头发不太可能退缩。

Here is another poor sod bitten by the ETL fantasy. Try changing it to ELT, you have the extract, now Load it into a file WITHOUT any transforms, make the destination table reflect the EXACT field names as the source and make all fields varchar(500).

Now write a stored proc to do the transforms from the target (staging) table to the destination table. As this is a good 40% of what I do for a living and have done for 20+ years take this advice seriously, your hair will be less likely to recede.

这篇关于批量加载数据转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！