SQL使用格式文件批量导入CSV单字段拆分多列报错如何解决
问题解决方案
报错原因
你的FMT格式文件存在两处不符合规范的配置,导致批量加载失败:
- FMT文件第二行填写的
8代表声明源CSV有8列,但你实际的CSV只有7列,二者不匹配 - 源文件列序号(FMT每行的第一个数字)重复使用了
3,SQL Server不支持通过重复源列序号的方式直接将单个源字段映射为多个目标字段
修复步骤
第一步:修改FMT格式文件
先正确映射CSV的7个原生列,将金额列临时命名为AmountRaw:
14.0 7 1 SQLCHAR 0 50 "," 1 PaymentUniqueNumber SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 50 "," 2 RequestDate SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 200 "," 3 AmountRaw SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 200 "," 4 TransactionType SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 1000 "," 5 TransactionStatus SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 1000 "," 6 ExtractStatus SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 2000 "\r\n" 7 ReversalStatus SQL_Latin1_General_CP1_CI_AS
第二步:调整查询语句
在查询逻辑中拆分AmountRaw为Amount和Currency两个字段,同时修正SUBSTRING的起始索引(SQL Server中字符串索引从1开始):
SELECT PaymentUniqueNumber, RequestDate, LTRIM(RTRIM(SUBSTRING(AmountRaw,1,CHARINDEX(' ',AmountRaw,0)-1))) AS Amount, LTRIM(RTRIM(SUBSTRING(AmountRaw,CHARINDEX(' ',AmountRaw,0)+1,LEN(AmountRaw)))) As Currency, TransactionType, TransactionStatus, ExtractStatus, ReversalStatus FROM OPENROWSET ( BULK 'C:\test.csv', FORMATFILE = 'C:\test.fmt', CODEPAGE = 'RAW', FIRSTROW = 2, MAXERRORS = 0, ROWS_PER_BATCH = 0 ) AS RowSource;
第三步:导入到视图(可选)
如果需要直接写入目标视图,在查询前增加INSERT语句即可:
INSERT INTO [dbo].[ReportsVW] -- 替换为上面的SELECT查询内容
内容的提问来源于stack exchange,提问作者Assad Nazar
相关产品推荐
相关产品推荐

