SQL Server Массовая вставка CSV файла с несогласованными кавычками

Ответ 1

Вам понадобится предварительно обработать файл, период.

Если вам действительно нужно это сделать, вот код. Я написал это, потому что у меня не было выбора. Это код полезности, и я не горжусь этим, но он работает. Подход заключается не в том, чтобы заставить SQL понимать кавычки, а вместо этого манипулировать файлом, чтобы использовать совершенно другой разделитель.

EDIT: Вот код в репозитории github. Он был улучшен и теперь идет с модульными тестами! https://github.com/chrisclark/Redelim-it

Эта функция принимает входной файл и заменяет все разделители полей (НЕ запятые внутри полей кавычек, только фактические разделительные) с новым разделителем. Затем вы можете указать серверу sql использовать новый разделитель полей вместо запятой. В версии функции здесь заполнитель является <TMP> (я уверен, что это не будет отображаться в исходном csv - если это произойдет, скопируйте для взрывов).

Поэтому после запуска этой функции вы импортируете в sql, выполнив что-то вроде:

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

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

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

Ответ 2

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

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

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

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

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

Ответ 3

Я нашел ответ Крисом очень полезным, но я хотел запустить его из SQL Server с использованием T-SQL (и не использовать CLR), поэтому я преобразовал его код в код T-SQL. Но затем я сделал еще один шаг, завернув все в хранимую процедуру, которая сделала следующее:

• использовать массивную вставку для первоначального импорта CSV файла.
• очистить строки с помощью кода Chris
• возвращает результаты в формате таблицы

Для моих нужд я дополнительно очистил строки, удалив кавычки вокруг значений и конвертируя две двойные кавычки в одну двойную кавычку (я думаю, что правильный метод).

CREATE PROCEDURE SSP_CSVToTable

-- Add the parameters for the stored procedure here
@InputFile nvarchar(4000)
, @FirstLine int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--convert the CSV file to a table
--clean up the lines so that commas are handles correctly

DECLARE @sql nvarchar(4000)
DECLARE @PH1 nvarchar(50)
DECLARE @LINECOUNT int -- This will also serve as a primary key
DECLARE @CURLINE int
DECLARE @Line nvarchar(4000)
DECLARE @starti int
DECLARE @endi int
DECLARE @FieldTerminatorFound bit
DECLARE @backChar nvarchar(4000)
DECLARE @quoteCount int
DECLARE @source nvarchar(4000)
DECLARE @COLCOUNT int
DECLARE @CURCOL int
DECLARE @ColVal nvarchar(4000)

-- new delimiter
SET @PH1 = '†'

-- create single column table to hold each line of file
CREATE TABLE [#CSVLine]([line] nvarchar(4000))

-- bulk insert into temp table
-- cannot use variable path with bulk insert
-- so we must run using dynamic sql
SET @Sql = 'BULK INSERT #CSVLine
FROM ''' + @InputFile + '''
WITH
(
FIRSTROW=' + CAST(@FirstLine as varchar) + ',
FIELDTERMINATOR = ''\n'',
ROWTERMINATOR = ''\n''
)'

-- run dynamic statement to populate temp table
EXEC(@sql)

-- get number of lines in table
SET @LINECOUNT = @@ROWCOUNT

-- add identity column to table so that we can loop through it
ALTER TABLE [#CSVLine] ADD [RowId] [int] IDENTITY(1,1) NOT NULL

IF @LINECOUNT > 0
BEGIN
    -- cycle through each line, cleaning each line
    SET @CURLINE = 1
    WHILE @CURLINE <= @LINECOUNT
    BEGIN
        -- get current line
        SELECT @line = line
          FROM #CSVLine
         WHERE [RowId] = @CURLINE

        -- Replace commas with our custom-made delimiter
        SET @Line = REPLACE(@Line, ',', @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.
        SET @starti = CHARINDEX(@PH1 + '"' ,@Line, 0)
        If CHARINDEX('"', @Line, 0) = 0 SET @starti = 0

        -- loop through quoted fields
        WHILE @starti > 0 
        BEGIN
            SET @FieldTerminatorFound = 0

            -- Find end quote token (originally  a ",)
            SET @endi = CHARINDEX('"' + @PH1, @Line, @starti)  -- sLine.IndexOf("""" & PH1, starti)

            IF @endi < 1
            BEGIN
                SET @FieldTerminatorFound = 1
                If @endi < 1 SET @endi = LEN(@Line) - 1
            END

            WHILE @FieldTerminatorFound = 0
            BEGIN
                -- Find any more quotes that are part of that sequence, if any
                SET @backChar = '"' -- thats one quote
                SET @quoteCount = 0

                WHILE @backChar = '"'
                BEGIN
                    SET @quoteCount = @quoteCount + 1
                    SET @backChar = SUBSTRING(@Line, @[email protected], 1) -- sLine.Chars(endi - quoteCount)
                END

                IF (@quoteCount % 2) = 1
                BEGIN
                    -- odd number of quotes. real field terminator
                    SET @FieldTerminatorFound = 1
                END
                ELSE 
                BEGIN
                    -- keep looking
                    SET @endi = CHARINDEX('"' + @PH1, @Line, @endi + 1) -- sLine.IndexOf("""" & PH1, endi + 1)
                END

            END

            -- Grab the quoted field from the line, now that we have the start and ending indices
            SET @source = SUBSTRING(@Line, @starti + LEN(@PH1), @endi - @starti - LEN(@PH1) + 1) 
            -- sLine.Substring(starti + PH1.Length, endi - starti - PH1.Length + 1)

            -- And swap the commas back in
            SET @Line = REPLACE(@Line, @source, REPLACE(@source, @PH1, ','))
            --sLine.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
            SET @starti = CHARINDEX(@PH1 + '"', @Line, @starti + LEN(@PH1))
            --sLine.IndexOf(PH1 & """", starti + PH1.Length)

        END

        -- get table based on current line
        IF OBJECT_ID('tempdb..#Line') IS NOT NULL
            DROP TABLE #Line

        -- converts a delimited list into a table
        SELECT *
        INTO #Line
        FROM dbo.iter_charlist_to_table(@Line,@PH1)

        -- get number of columns in line
        SET @COLCOUNT = @@ROWCOUNT

        -- dynamically create CSV temp table to hold CSV columns and lines
        -- only need to create once
        IF OBJECT_ID('tempdb..#CSV') IS NULL
        BEGIN
            -- create initial structure of CSV table
            CREATE TABLE [#CSV]([Col1] nvarchar(100))

            -- dynamically add a column for each column found in the first line
            SET @CURCOL = 1
            WHILE @CURCOL <= @COLCOUNT
            BEGIN
                -- first column already exists, don't need to add
                IF @CURCOL > 1 
                BEGIN
                    -- add field
                    SET @sql = 'ALTER TABLE [#CSV] ADD [Col' + Cast(@CURCOL as varchar) + '] nvarchar(100)'

                    --print @sql

                    -- this adds the fields to the temp table
                    EXEC(@sql)
                END

                -- go to next column
                SET @CURCOL = @CURCOL + 1
            END
        END

        -- build dynamic sql to insert current line into CSV table
        SET @sql = 'INSERT INTO [#CSV] VALUES('

        -- loop through line table, dynamically adding each column value
        SET @CURCOL = 1
        WHILE @CURCOL <= @COLCOUNT
        BEGIN
            -- get current column
            Select @ColVal = str 
              From #Line 
             Where listpos = @CURCOL

            IF LEN(@ColVal) > 0
            BEGIN
                -- remove quotes from beginning if exist
                IF LEFT(@ColVal,1) = '"'
                    SET @ColVal = RIGHT(@ColVal, LEN(@ColVal) - 1)

                -- remove quotes from end if exist
                IF RIGHT(@ColVal,1) = '"'
                    SET @ColVal = LEFT(@ColVal, LEN(@ColVal) - 1)
            END

            -- write column value
            -- make value sql safe by replacing single quotes with two single quotes
            -- also, replace two double quotes with a single double quote
            SET @sql = @sql + '''' + REPLACE(REPLACE(@ColVal, '''',''''''), '""', '"') + ''''

            -- add comma separater except for the last record
            IF @CURCOL <> @COLCOUNT
                SET @sql = @sql + ','

            -- go to next column
            SET @CURCOL = @CURCOL + 1
        END

        -- close sql statement
        SET @sql = @sql + ')'

        --print @sql

        -- run sql to add line to table
        EXEC(@sql)

        -- move to next line
        SET @CURLINE = @CURLINE + 1

    END

END

-- return CSV table
SELECT * FROM [#CSV]

END

GO

В хранимой процедуре используется эта вспомогательная функция, которая анализирует строку в таблице (спасибо Erland Sommarskog!):

CREATE FUNCTION [dbo].[iter_charlist_to_table]
                (@list      ntext,
                 @delimiter nchar(1) = N',')
     RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                         str     varchar(4000),
                         nstr    nvarchar(2000)) AS

BEGIN
  DECLARE @pos      int,
          @textpos  int,
          @chunklen smallint,
          @tmpstr   nvarchar(4000),
          @leftover nvarchar(4000),
          @tmpval   nvarchar(4000)

  SET @textpos = 1
  SET @leftover = ''
  WHILE @textpos <= datalength(@list) / 2
  BEGIN
     SET @chunklen = 4000 - datalength(@leftover) / 2
     SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
     SET @textpos = @textpos + @chunklen

     SET @pos = charindex(@delimiter, @tmpstr)

     WHILE @pos > 0
     BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
     END

     SET @leftover = @tmpstr
  END

  INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))

RETURN

END

Вот как я называю это из T-SQL. В этом случае я вставляю результаты в таблицу temp, поэтому сначала создаю таблицу temp:

    -- create temp table for file import
CREATE TABLE #temp
(
    CustomerCode nvarchar(100) NULL,
    Name nvarchar(100) NULL,
    [Address] nvarchar(100) NULL,
    City nvarchar(100) NULL,
    [State] nvarchar(100) NULL,
    Zip nvarchar(100) NULL,
    OrderNumber nvarchar(100) NULL,
    TimeWindow nvarchar(100) NULL,
    OrderType nvarchar(100) NULL,
    Duration nvarchar(100) NULL,
    [Weight] nvarchar(100) NULL,
    Volume nvarchar(100) NULL
)

-- convert the CSV file into a table
INSERT #temp
EXEC [dbo].[SSP_CSVToTable]
     @InputFile = @FileLocation
    ,@FirstLine = @FirstImportRow

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

Надеюсь, кто-то еще и найдет это полезным.

Ура!

Ответ 4

Я также создал функцию для преобразования CSV в полезный формат для Bulk Insert. Я использовал ответную запись Криса Кларка в качестве отправной точки для создания следующей функции С#.

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

private void CsvToOtherDelimiter(string CSVFile, System.Data.Linq.Mapping.MetaTable tbl)
{
    char PH1 = '|';
    StringBuilder ln;

    //Confirm file exists. Else, throw exception
    if (File.Exists(CSVFile))
    {
        using (TextReader tr = new StreamReader(CSVFile))
        {
            //Use a temp file to store our conversion
            using (TextWriter tw = new StreamWriter(CSVFile + ".tmp"))
            {
                string line = tr.ReadLine();
                //If we have already converted, no need to reconvert.
                //NOTE: We make the assumption here that the input header file 
                //      doesn't have a PH1 value unless it already been converted.
                if (line.IndexOf(PH1) >= 0)
                {
                    tw.Close();
                    tr.Close();
                    File.Delete(CSVFile + ".tmp");
                    return;
                }
                //Loop through input file
                while (!string.IsNullOrEmpty(line))
                {
                    ln = new StringBuilder();

                    //1. Use Regex expression to find comma separated values 
                    //using quotes as optional text qualifiers 
                    //(what MS EXCEL does when you import a csv file)
                    //2. Remove text qualifier quotes from data
                    //3. Replace any values of PH1 found in column data 
                    //with an equivalent character
                    //Regex:  \A[^,]*(?=,)|(?:[^",]*"[^"]*"[^",]*)+|[^",]*"[^"]*\Z|(?<=,)[^,]*(?=,)|(?<=,)[^,]*\Z|\A[^,]*\Z
                    List<string> fieldList = Regex.Matches(line, @"\A[^,]*(?=,)|(?:[^"",]*""[^""]*""[^"",]*)+|[^"",]*""[^""]*\Z|(?<=,)[^,]*(?=,)|(?<=,)[^,]*\Z|\A[^,]*\Z")
                            .Cast<Match>()
                            .Select(m => RemoveCSVQuotes(m.Value).Replace(PH1, '¦'))
                            .ToList<string>();

                    //Add the list of fields to ln, separated by PH1
                    fieldList.ToList().ForEach(m => ln.Append(m + PH1));

                    //Write to file. Don't include trailing PH1 value.
                    tw.WriteLine(ln.ToString().Substring(0, ln.ToString().LastIndexOf(PH1)));

                    line = tr.ReadLine();
                }


                tw.Close();
            }
            tr.Close();

            //Optional:  replace input file with output file
            File.Delete(CSVFile);
            File.Move(CSVFile + ".tmp", CSVFile);
        }
    }
    else
    {
        throw new ArgumentException(string.Format("Source file {0} not found", CSVFile));
    }
}
//The output file no longer needs quotes as a text qualifier, so remove them
private string RemoveCSVQuotes(string value)
{
    //if is empty string, then remove double quotes
    if (value == @"""""") value = "";
    //remove any double quotes, then any quotes on ends
    value = value.Replace(@"""""", @"""");
    if (value.Length >= 2)
        if (value.Substring(0, 1) == @"""")
            value = value.Substring(1, value.Length - 2);
    return value;
}

Ответ 5

Чаще всего эта проблема вызвана тем, что пользователи экспортируют файл Excel в CSV.

Существует два пути решения этой проблемы:

• Экспорт из Excel с помощью макроса в соответствии с предложением Microsoft
• Или действительно простой способ:
  • Откройте CSV в Excel.
  • Сохранить как файл Excel. (.xls или .xlsx).
  • Импортируйте этот файл в SQL Server как файл Excel.
  • Посмеивайтесь с самим собой, потому что вам не нужно кодировать ничего подобного решениям выше... muhahahaha

Здесь SQL, если вы действительно хотите script его (после сохранения CSV как Excel):

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

Ответ 6

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

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

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

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

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

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

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

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

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

Ответ 7

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

UPDATE dbo.tablename 
        SET dbo.tablename.target_field = REPLACE(t.importedValue, '"', '')
FROM #tempTable t
WHERE dbo.tablename.target_id = t.importedID;

Я использовал его. Я не могу претендовать на производительность. Это просто быстрый и грязный способ обойти проблему.

Ответ 8

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

Ответ 9

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

Жаль, что я тоже не смог бы добавить сюда большое пиво.

Geeze, я понятия не имею, почему End Module и Public Class находятся за пределами блока кода... srry!

    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

Ответ 10

Я нашел несколько проблем, имея "," внутри наших полей, таких как Майк, "456 2nd St, Apt 5".

Решение этой проблемы: @http://crazzycoding.blogspot.com/2010/11/import-csv-file-into-sql-server-using.html

Спасибо, - Ашиш

Ответ 11

Предварительная обработка необходима.

Функция PowerShell Import-CSV поддерживает этот тип файла. Export-CSV будет затем заключать каждое значение в кавычки.

Отдельный файл:

Import-Csv import.csv | Export-Csv -NoTypeInformation export.csv

Чтобы объединить множество файлов с путями C:\year\input_date.csv:

$inputPath = 'C:\????\input_????????.csv'
$outputPath = 'C:\merged.csv'
Get-ChildItem $inputPath |
  Select -ExpandProperty FullName |
  Import-CSV |
  Export-CSV -NoTypeInformation -Path $outputPath

PowerShell обычно можно запустить с агентом SQL Server с использованием учетной записи-посредника PowerShell.

Если разделители не обрабатываются должным образом, явно укажите другой разделитель.

 Export-CSV -NoTypeInformation -Delimiter ';' -Path $outputPath

Ответ 12

Этот код работает для меня:

 public bool CSVFileRead(string fullPathWithFileName, string fileNameModified, string tableName)
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["dbConnectionString"]);
        string filepath = fullPathWithFileName;
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }
        while (!sr.EndOfStream)
        {
            //string[] stud = sr.ReadLine().Split(',');
            //for (int i = 0; i < stud.Length; i++)
            //{
            //    stud[i] = stud[i].Replace("\"", "");
            //}
            //value = stud;
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = tableName;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();

        return true;
    }

Ответ 13

Я собрал ниже, чтобы решить мое дело. Мне нужно было предварительно обработать очень большие файлы и разобраться в несогласованном цитировании. Просто вставьте его в пустое приложение С#, установите константы в соответствии с вашими требованиями и уходите. Это работало на очень большом CSV более 10 ГБ.

namespace CsvFixer
{
    using System.IO;
    using System.Text;

    public class Program
    {
        private const string delimiter = ",";
        private const string quote = "\"";
        private const string inputFile = "C:\\temp\\input.csv";
        private const string fixedFile = "C:\\temp\\fixed.csv";

        /// <summary>
        /// This application fixes inconsistently quoted csv (or delimited) files with support for very large file sizes.
        /// For example :  1223,5235234,8674,"Houston","London, UK",3425,Other text,stuff 
        /// Must become :  "1223","5235234","8674","Houston","London, UK","3425","Other text","stuff" 
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            // Use streaming to allow for large files. 
            using (StreamWriter outfile = new StreamWriter(fixedFile))
            {
                using (FileStream fs = File.Open(inputFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                using (BufferedStream bs = new BufferedStream(fs))
                using (StreamReader sr = new StreamReader(bs))
                {
                    string currentLine;

                    // Read each input line in and write each fixed line out
                    while ((currentLine = sr.ReadLine()) != null)
                    {
                        outfile.WriteLine(FixLine(currentLine, delimiter, quote));
                    }
                }
            }
        }

        /// <summary>
        /// Fully quote a partially quoted line 
        /// </summary>
        /// <param name="line">The partially quoted line</param>
        /// <returns>The fully quoted line</returns>
        private static string FixLine(string line, string delimiter, string quote)
        {
            StringBuilder fixedLine = new StringBuilder();

            // Split all on the delimiter, acceptinmg that some quoted fields 
            // that contain the delimiter wwill be split in to many pieces.
            string[] fieldParts = line.Split(delimiter.ToCharArray());

            // Loop through the fields (or parts of fields)
            for (int i = 0; i < fieldParts.Length; i++)
            {
                string currentFieldPart = fieldParts[i];

                // If the current field part starts and ends with a quote it is a field, so write it to the result
                if (currentFieldPart.StartsWith(quote) && currentFieldPart.EndsWith(quote))
                {
                    fixedLine.Append(string.Format("{0}{1}", currentFieldPart, delimiter));
                }
                // else if it starts with a quote but doesnt end with one, it is part of a lionger field.
                else if (currentFieldPart.StartsWith(quote))
                {
                    // Add the start of the field
                    fixedLine.Append(string.Format("{0}{1}", currentFieldPart, delimiter));

                    // Append any additional field parts (we will only hit the end of the field when 
                    // the last field part finishes with a quote. 
                    while (!fieldParts[++i].EndsWith(quote))
                    {
                        fixedLine.Append(string.Format("{0}{1}", fieldParts[i], delimiter));
                    }

                    // Append the last field part - i.e. the part containing the closing quote
                    fixedLine.Append(string.Format("{0}{1}", fieldParts[i], delimiter));
                }
                else
                {
                    // The field has no quotes, add the feildpart with quote as bookmarks 
                    fixedLine.Append(string.Format("{0}{1}{0}{2}", quote, currentFieldPart, delimiter));
                }
            }

            // Return the fixed string 
            return fixedLine.ToString();
        }
    }
}

Ответ 14

Если говорить на практике... В SQL Server 2017 вы можете предоставить "Текстовый квалификатор" из двойных кавычек, и он не "заменит" ваш разделитель. Я массово вставляю несколько файлов, которые выглядят так же, как в примере с OP. Мои файлы - ".csv", и у них есть противоречивые текстовые квалификаторы, которые можно найти только тогда, когда значение содержит запятую. Я не знаю, в какой версии SQL Server эта функция/функциональность начала работать, но я знаю, что она работает в стандарте SQL Server 2017. Довольно легко.

Ответ 15

Вам не нужно предварительно обрабатывать файл вне SQL.

То, что работало для меня, менялось

ROWTERMINATOR = '\n'

в

ROWTERMINATOR = '0x0a'.

Ответ 16

В 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 привел пример.

Ответ 17

Создайте программу VB.NET для преобразования в новый разделитель с помощью 4.5 Framework TextFieldParser Это автоматически обработает текстовые поля

Измененный выше код для использования встроенного TextFieldParser

Модуль Module1

Sub Main()

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

    Console.Write(vbNewLine & vbNewLine)

    If Not IsNothing(arrArgs(0)) Then
        For i = LBound(arrArgs) To UBound(arrArgs)
            Console.Write("Parameter " & i & " is " & arrArgs(i) & vbNewLine)
        Next
        InputFile = arrArgs(0)
        If Not IsNothing(arrArgs(1)) Then
            If Not String.IsNullOrEmpty(arrArgs(1)) Then
                OutPutFile = arrArgs(1)
            Else
                OutPutFile = InputFile.Replace("csv", "pipe")
            End If
        Else
            OutPutFile = InputFile.Replace("csv", "pipe")
        End If
        If Not IsNothing(arrArgs(2)) Then
            If Not String.IsNullOrEmpty(arrArgs(2)) Then
                NewDelimiter = arrArgs(2)
            Else
                NewDelimiter = "|"
            End If
        Else
            NewDelimiter = "|"
        End If
        obj.ConvertCSVFile(InputFile,OutPutFile,NewDelimiter)

    Else
        Console.Write("Usage ChangeFileDelimiter <inputfile>,<outputfile>,<NewDelimiter>")
    End If
    obj = Nothing
    Console.Write(vbNewLine & vbNewLine)
    'Console.ReadLine()

End Sub

Конечный модуль

Открытый класс ReDelimIt

Public Function ConvertCSVFile(ByVal InputFile As String, ByVal OutputFile As String, Optional ByVal NewDelimiter As String = "|") As Integer
    Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(InputFile)
        MyReader.TextFieldType = FileIO.FieldType.Delimited
        MyReader.SetDelimiters(",")
        Dim sb As New System.Text.StringBuilder
        Dim strLine As String = ""
        Dim currentRow As String()
        While Not MyReader.EndOfData
            Try
                currentRow = MyReader.ReadFields()
                Dim currentField As String
                strLine = ""
                For Each currentField In currentRow
                    'MsgBox(currentField)
                    If strLine = "" Then
                        strLine = strLine & currentField
                    Else
                        strLine = strLine & NewDelimiter & currentField
                    End If
                Next
                sb.AppendLine(strLine)
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                'MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                Console.WriteLine("Line " & ex.Message & "is not valid and will be skipped.")
            End Try
        End While
        SaveTextToFile(sb.ToString, OutputFile)
    End Using

    Return Err.Number

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
        If FileIO.FileSystem.FileExists(FullPath) Then
            Kill(FullPath)
        End If
        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

Конечный класс