使用 BULK INSERT 映射列

发布于 2024-12-15 09:32:15 字数 831 浏览 2 评论 0原文

我有一个包含 600 万行的 CSV 文件。每行都由相同的格式组成，例如/

I,h,q,q,3,A,5,Q,3,[,5,Q,8,c,3,N,3,E,4,F,4,g,4,I,V,9000,0000001-100,G9999999990001800000000000001,G9999999990000001100PDNELKKMMCNELRQNWJ010, , , , , , ,D,Z

我的表中有 2 列。

第一列应为 CSV 中的字段 27，第二列应为 CSV 文件中的整行。

我尝试设置格式文件但无法使其工作。

是否有可能进行这种映射？

这是我所拥有的：

BULK INSERT Staging FROM 'C:\Data.txt' 
   WITH 
   (
      FIELDTERMINATOR =',',
      ROWTERMINATOR ='\n',
      KEEPNULLS,
      formatfile='C:\format.fmt'
   )

这是我的格式文件

9.0
2
1       SQLCHAR       0       40     ","     27     Col27               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       200     "\r\n"  1     Col1               SQL_Latin1_General_CP1_CI_AS

相比之下，我在 SQLite 中工作需要 2 分 35 秒。

原文

I have a CSV file with 6 millions rows. Each line is made up of the same format eg/

I,h,q,q,3,A,5,Q,3,[,5,Q,8,c,3,N,3,E,4,F,4,g,4,I,V,9000,0000001-100,G9999999990001800000000000001,G9999999990000001100PDNELKKMMCNELRQNWJ010, , , , , , ,D,Z

I have 2 columns in a table.

The first column should be field 27 in the CSV and the second column should be the whole line in the CSV file.

I have tried to set up a format file but cannot get it working.

Is it even possible to do this sort of mapping?

Here is what I have:

BULK INSERT Staging FROM 'C:\Data.txt' 
   WITH 
   (
      FIELDTERMINATOR =',',
      ROWTERMINATOR ='\n',
      KEEPNULLS,
      formatfile='C:\format.fmt'
   )

This is my format file

9.0
2
1       SQLCHAR       0       40     ","     27     Col27               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       200     "\r\n"  1     Col1               SQL_Latin1_General_CP1_CI_AS

In terms of comparison, I have this working in SQLite which takes 2min 35secs.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泡沫很甜 2024-12-22 09:32:15

您可以通过几种方法解决这个问题，但我的方法是通过动态 SQL 将整个 csv 文件批量插入到临时表中：

CREATE TABLE #BulkLoadData(
    RecordData NVARCHAR(max)
)


SET @SQL = 'BULK INSERT #BulkLoadData FROM ''' + @SourceFileFullPath + ''' '
SET @SQL = @SQL + 'WITH (FORMATFILE = ''' + @UPXInputFileBulkLoadFormat + 'UPXInputFileBulkLoadFormat.xml'', TABLOCK, ROWS_PER_BATCH = 2500 ) '

EXECUTE (@SQL)

然后您可以将数据插入到目标表中，如下所示：

INSERT INTO dbo.TargetTable
SELECT dbo.fnParseString(27, ',', RecordData), RecordData

您需要创建像这样的解析函数：

CREATE FUNCTION [dbo].[fnParseString]
(
    @Section SMALLINT,
    @Delimiter CHAR,
    @Text VARCHAR(MAX)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @startindex NUMERIC(18,0),
     @length NUMERIC(18,0),
     @FieldPosition INT

 SET @FieldPosition = ABS(@Section) - 1
 SET @startindex = 0


 WHILE @FieldPosition != 0
 BEGIN
    SET @FieldPosition = @FieldPosition - 1
     SET @startindex = CHARINDEX(@Delimiter, @Text, @startindex + 1) 
 END     


 SET @Text = SUBSTRING(@Text, @startindex + 1, LEN(@Text) - @startindex)
 SET @Text = SUBSTRING(@Text, 0, CHARINDEX(@Delimiter, @Text))

 RETURN @Text
END

希望有帮助！如果您需要有关格式文件的帮助，请告诉我。

这是格式文件内容：

<?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="CharTerm" TERMINATOR="\n" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="RecordData" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

You could crack this nut a couple of ways but the way I did it was to bulk insert the WHOLE csv file into a temp table via dynamic SQL:

CREATE TABLE #BulkLoadData(
    RecordData NVARCHAR(max)
)


SET @SQL = 'BULK INSERT #BulkLoadData FROM ''' + @SourceFileFullPath + ''' '
SET @SQL = @SQL + 'WITH (FORMATFILE = ''' + @UPXInputFileBulkLoadFormat + 'UPXInputFileBulkLoadFormat.xml'', TABLOCK, ROWS_PER_BATCH = 2500 ) '

EXECUTE (@SQL)

Then you can insert the data into the target table like this:

INSERT INTO dbo.TargetTable
SELECT dbo.fnParseString(27, ',', RecordData), RecordData

You'll need to create a parse function like so:

CREATE FUNCTION [dbo].[fnParseString]
(
    @Section SMALLINT,
    @Delimiter CHAR,
    @Text VARCHAR(MAX)
)
RETURNS VARCHAR(8000)
AS

BEGIN
DECLARE @startindex NUMERIC(18,0),
     @length NUMERIC(18,0),
     @FieldPosition INT

 SET @FieldPosition = ABS(@Section) - 1
 SET @startindex = 0


 WHILE @FieldPosition != 0
 BEGIN
    SET @FieldPosition = @FieldPosition - 1
     SET @startindex = CHARINDEX(@Delimiter, @Text, @startindex + 1) 
 END     


 SET @Text = SUBSTRING(@Text, @startindex + 1, LEN(@Text) - @startindex)
 SET @Text = SUBSTRING(@Text, 0, CHARINDEX(@Delimiter, @Text))

 RETURN @Text
END

Hope that helps! If you need help with the format file let me know.

Here is the format file contents:

<?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="CharTerm" TERMINATOR="\n" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="RecordData" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

回复收藏 0 原文

~没有更多了~