SQL Server 批量插入带有不一致引号的 CSV 文件 [英] SQL Server Bulk insert of CSV file with inconsistent quotes

问题描述

是否可以批量插入(SQL Server)一个 CSV 文件，其中的字段只是偶尔被引号包围?具体而言，引号仅将包含,"的字段括起来.

Is it possible to BULK INSERT (SQL Server) a CSV file in which the fields are only OCCASSIONALLY surrounded by quotes? Specifically, quotes only surround those fields that contain a ",".

换句话说，我的数据看起来像这样(第一行包含标题):

In other words, I have data that looks like this (the first row contain headers):

id, company, rep, employees
729216,INGRAM MICRO INC.,"Stuart, Becky",523
729235,"GREAT PLAINS ENERGY, INC.","Nelson, Beena",114
721177,GEORGE WESTON BAKERIES INC,"Hogan, Meg",253

因为引号不一致，我不能使用'","'作为分隔符，我不知道如何创建一个说明这一点的格式文件.

Because the quotes aren't consistent, I can't use '","' as a delimiter, and I don't know how to create a format file that accounts for this.

我尝试使用 ',' 作为分隔符并将其加载到一个临时表中，其中每一列都是一个 varchar，然后使用一些 kludgy 处理来去除引号，但这也不起作用，因为包含的字段',' 被分成多列.

I tried using ',' as a delimter and loading it into a temporary table where every column is a varchar, then using some kludgy processing to strip out the quotes, but that doesn't work either, because the fields that contain ',' are split into multiple columns.

很遗憾，我无法事先操作 CSV 文件.

Unfortunately, I don't have the ability to manipulate the CSV file beforehand.

这是无望的吗?

非常感谢您提供任何建议.

Many thanks in advance for any advice.

顺便说一下，我看到了这篇文章从 csv 批量导入 SQL，但是在这种情况下，每个字段都始终用引号括起来.因此，在这种情况下，他可以使用 ',' 作为分隔符，然后去掉引号.

By the way, i saw this post SQL bulk import from csv, but in that case, EVERY field was consistently wrapped in quotes. So, in that case, he could use ',' as a delimiter, then strip out the quotes afterwards.

推荐答案

您将需要预处理文件，句点.

You are going to need to preprocess the file, period.

如果你真的需要这样做，这里是代码.我写这个是因为我绝对别无选择.它是实用程序代码，我并不以此为荣，但它确实有效.该方法不是让 SQL 理解引用的字段，而是操作文件以使用完全不同的分隔符.

If you really really need to do this, here is the code. I wrote this because I absolutely had no choice. It is utility code and I'm not proud of it, but it works. The approach is not to get SQL to understand quoted fields, but instead manipulate the file to use an entirely different delimiter.

这是 github 存储库中的代码.它已得到改进，现在带有单元测试！https://github.com/chrisclark/Redelim-it

Here is the code in a github repo. It's been improved and now comes with unit tests! https://github.com/chrisclark/Redelim-it

此函数接受一个输入文件，并将用新的分隔符替换所有字段分隔逗号(不是引用文本字段中的逗号，只是实际的分隔符).然后您可以告诉 sql server 使用新的字段分隔符而不是逗号.在此处的函数版本中，占位符是 <TMP>(我相信这不会出现在原始 csv 中 - 如果出现，请准备好爆炸).

This function takes an input file and will replace all field-delimiting commas (NOT commas inside quoted-text fields, just the actual delimiting ones) with a new delimiter. You can then tell sql server to use the new field delimiter instead of a comma. In the version of the function here, the placeholder is <TMP> (I feel confident this will not appear in the original csv - if it does, brace for explosions).

因此，在运行此函数后，您可以通过执行以下操作在 sql 中导入:

Therefore after running this function you import in sql by doing something like:

BULK INSERT MyTable
FROM 'C:FileCreatedFromThisFunction.csv'
WITH
(
FIELDTERMINATOR = '<*TMP*>',
ROWTERMINATOR = '
'
)

不用多说，我提前为给你造成的可怕的、可怕的功能道歉(编辑 - 我已经发布了一个可以执行此操作的工作程序，而不仅仅是功能 在我的博客上):

And without further ado, the terrible, awful function that I apologize in advance for inflicting on you (edit - I've posted a working program that does this instead of just the function on my blog here):

Private Function CsvToOtherDelimiter(ByVal InputFile As String, ByVal OutputFile As String) As Integer

        Dim PH1 As String = "<*TMP*>"

        Dim objReader As StreamReader = Nothing
        Dim count As Integer = 0 'This will also serve as a primary key'
        Dim sb As New System.Text.StringBuilder

        Try
            objReader = New StreamReader(File.OpenRead(InputFile), System.Text.Encoding.Default)
        Catch ex As Exception
            UpdateStatus(ex.Message)
        End Try

        If objReader Is Nothing Then
            UpdateStatus("Invalid file: " & InputFile)
            count = -1
            Exit Function
        End If

        'grab the first line
    Dim line = reader.ReadLine()
    'and advance to the next line b/c the first line is column headings
    If hasHeaders Then
        line = Trim(reader.ReadLine)
    End If

    While Not String.IsNullOrEmpty(line) 'loop through each line

        count += 1

        'Replace commas with our custom-made delimiter
        line = line.Replace(",", ph1)

        'Find a quoted part of the line, which could legitimately contain commas.
        'In that case we will need to identify the quoted section and swap commas back in for our custom placeholder.
        Dim starti = line.IndexOf(ph1 & """", 0)
        If line.IndexOf("""",0) = 0 then starti=0

        While starti > -1 'loop through quoted fields

            Dim FieldTerminatorFound As Boolean = False

            'Find end quote token (originally  a ",)
            Dim endi As Integer = line.IndexOf("""" & ph1, starti)

            If endi < 0 Then
                FieldTerminatorFound = True
                If endi < 0 Then endi = line.Length - 1
            End If

            While Not FieldTerminatorFound

                'Find any more quotes that are part of that sequence, if any
                Dim backChar As String = """" 'thats one quote
                Dim quoteCount = 0
                While backChar = """"
                    quoteCount += 1
                    backChar = line.Chars(endi - quoteCount)
                End While

                If quoteCount Mod 2 = 1 Then 'odd number of quotes. real field terminator
                    FieldTerminatorFound = True
                Else 'keep looking
                    endi = line.IndexOf("""" & ph1, endi + 1)
                End If
            End While

            'Grab the quoted field from the line, now that we have the start and ending indices
            Dim source = line.Substring(starti + ph1.Length, endi - starti - ph1.Length + 1)

            'And swap the commas back in
            line = line.Replace(source, source.Replace(ph1, ","))

            'Find the next quoted field
            '                If endi >= line.Length - 1 Then endi = line.Length 'During the swap, the length of line shrinks so an endi value at the end of the line will fail
            starti = line.IndexOf(ph1 & """", starti + ph1.Length)

        End While

            line = objReader.ReadLine

        End While

        objReader.Close()

        SaveTextToFile(sb.ToString, OutputFile)

        Return count

    End Function

这篇关于SQL Server 批量插入带有不一致引号的 CSV 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！