SQL Server Объемная вставка файла CSV с непоследовательными кавычками

Новая опция была добавлена в SQL 2017 для определения WITH ( FORMAT='CSV') для BULK INSERT команды.

пример от страница Microsoft GitHub:

BULK INSERT Product
FROM 'product.csv'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
        ROWTERMINATOR = '0x0a',
        TABLOCK); 

Подробная документация для той опции доступна здесь: https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql? view=sql-server-2017#input-file-format-options

я успешно использовал эту опцию с данными CSV, содержащими дополнительные кавычки так же, как OP, дал пример.

Невозможно выполнить массовую вставку для этого файла из MSDN:

Для использования в качестве файла данных для массового импорта файл CSV должен соответствовать следующим ограничениям :

• Поля данных никогда не содержат терминатора поля.
• Либо ни одно, ни все значения в поле данных заключаются в кавычки ("").

( http://msdn.microsoft .com / en-us / library / ms188609.aspx )

Чтобы подготовить файл к импорту, необходимо выполнить простую обработку текста. В качестве альтернативы вашим пользователям может потребоваться либо отформатировать файл в соответствии с указаниями se, либо использовать в качестве разделителя что-либо, кроме запятой (например, |)

Вы должны быть в состоянии указать не только разделитель полей, который должен быть [,], но также и спецификатор текста, который в этом случае будет ["]. Использование [] для включения этого так что нет путаницы с ".

Это может быть сложнее или сложнее, чем то, что вы хотите использовать, но ...

Если вы можете реализовать логику для разбора строк на поля в VB или C #, вы можете сделайте это с помощью функции CLR с табличным значением (TVF).

CLR TVF может быть хорошим эффективным способом чтения данных из внешнего источника, когда вы хотите, чтобы некоторый код C # или VB разделял данные на столбцы и / или настраивал значения.

Вы должны быть готовы добавить в свою базу данных сборку CLR (и ту, которая разрешает внешние или небезопасные операции, чтобы открывать файлы). Это может быть немного сложным или запутанным, но оно того стоит из-за получаемой вами гибкости.

У меня было несколько больших файлов, которые нужно было регулярно загружать в таблицы как можно быстрее, но для некоторых столбцов необходимо было выполнить определенные преобразования кода, и для загрузки значений, которые в противном случае вызвали бы ошибки типа данных при простой массовой вставке, требовалась специальная обработка.

Короче говоря, CLR TVF позволяет запускать код C # или VB для каждого строка файла с массовой вставкой, например, с производительностью (хотя вам, возможно, придется беспокоиться о регистрации). Пример в документации по SQL Server позволяет вам создать TVF для чтения из журнала событий, который можно использовать в качестве отправной точки.

Обратите внимание, что код в CLR TVF может получить доступ к базе данных только на этапе инициализации перед первым строка обрабатывается (например, нет поиска для каждой строки - вы используете обычную TVF поверх этого, чтобы делать такие вещи). Судя по вашему вопросу, вам это не нужно.

Также обратите внимание, что для каждой CLR TVF должны быть явно указаны выходные столбцы, так что вы можете ' t написать общий, который может быть повторно использован для каждого другого CSV-файла, который у вас может быть.

Вы можете написать одну TVF среды CLR для чтения целых строк из файла, возвращая набор результатов в один столбец, а затем использовать обычные TVF для чтения из него для каждый тип файла. Это требует, чтобы код анализировал каждую строку, которая должна быть написана на T-SQL, но избегает необходимости писать много CLR TVF.

Крис, Большое спасибо за это !! Ты спас мое печенье !! Я не мог поверить, что массовый погрузчик не справится с этим случаем, когда XL делает такую ​​хорошую работу .. разве эти парни не видят друг друга в залах ??? В любом случае ... Мне нужна была версия ConsoleApplication, вот что я вместе взломал. Он грязный и грязный, но работает как чемпион! Я жестко запрограммировал разделитель и закомментировал заголовок, поскольку они не нужны для моего приложения.

Хотел бы я и тебе поставить сюда хорошего пива.

Боже, я понятия не имею, почему конечный модуль и открытый класс находятся за пределами блока кода ...

    Module Module1

    Sub Main()

        Dim arrArgs() As String = Command.Split(",")
        Dim i As Integer
        Dim obj As New ReDelimIt()

        Console.Write(vbNewLine & vbNewLine)

        If arrArgs(0) <> Nothing Then
            For i = LBound(arrArgs) To UBound(arrArgs)
                Console.Write("Parameter " & i & " is " & arrArgs(i) & vbNewLine)
            Next


            obj.ProcessFile(arrArgs(0), arrArgs(1))

        Else
            Console.Write("Usage Test1 <inputfile>,<outputfile>")
        End If

        Console.Write(vbNewLine & vbNewLine)
    End Sub

 End Module

 Public Class ReDelimIt

    Public Function ProcessFile(ByVal InputFile As String, ByVal OutputFile As String) As Integer

        Dim ph1 As String = "|"

        Dim objReader As System.IO.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 System.IO.StreamReader(System.IO.File.OpenRead(InputFile), System.Text.Encoding.Default)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

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

        'grab the first line
        Dim line = objReader.ReadLine()
        'and advance to the next line b/c the first line is column headings
        'Removed Check Headers can put in if needed.
        'If chkHeaders.Checked Then
        'line = objReader.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)

            While starti > -1 'loop through quoted fields

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

                'The end quote token could be a false positive because there could occur a ", sequence.
                'It would be double-quoted ("",) so check for that here
                Dim check1 = line.IndexOf("""""" & ph1, starti)

                'A """, sequence can occur if a quoted field ends in a quote.
                'In this case, the above check matches, but we actually SHOULD process this as an end quote token
                Dim check2 = line.IndexOf("""""""" & ph1, starti)

                'If we are in the check1 ("",) situation, keep searching for an end quote token
                'The +1 and +2 accounts for the extra length of the checked sequences
                While (endi = check1 + 1 AndAlso endi <> check2 + 2) 'loop through "false" tokens in the quoted fields
                    endi = line.IndexOf("""" & ph1, endi + 1)
                    check1 = line.IndexOf("""""" & ph1, check1 + 1)
                    check2 = line.IndexOf("""""""" & ph1, check2 + 1)
                End While

                'We have searched for an end token (",) but can't find one, so that means the line ends in a "
                If endi < 0 Then endi = line.Length - 1

                '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

            'Add our primary key to the line
            ' Removed for now
            'If chkAddKey.Checked Then
            'line = String.Concat(count.ToString, ph1, line)
            ' End If

            sb.AppendLine(line)

            line = objReader.ReadLine

        End While

        objReader.Close()

        SaveTextToFile(sb.ToString, OutputFile)

        Return count

    End Function

    Public Function SaveTextToFile(ByVal strData As String, ByVal FullPath As String) As Boolean
        Dim bAns As Boolean = False
        Dim objReader As System.IO.StreamWriter
        Try
            objReader = New System.IO.StreamWriter(FullPath, False, System.Text.Encoding.Default)
            objReader.Write(strData)
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            Throw Ex
        End Try
        Return bAns
    End Function

End Class