codepage - SQL Server Insertar a granel de archivo CSV con comillas inconsistentes

csv to sql

Question

¿Es posible BULK INSERT (SQL Server) un archivo CSV en el que los campos están OCCASSIONALLY rodeados de comillas? Específicamente, las comillas solo rodean los campos que contienen un ",".

En otras palabras, tengo datos que se parecen a esto (la primera fila contiene encabezados):

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

Debido a que las comillas no son consistentes, no puedo usar ''","'' como delimitador, y no sé cómo crear un archivo de formato que tenga esto en cuenta.

Intenté usar '','' como un delimitador y cargarlo en una tabla temporal donde cada columna es varchar, y luego usar algún proceso kludgy para quitar las comillas, pero eso tampoco funciona, porque los campos que contienen '','' se dividen en varias columnas.

Lamentablemente, no tengo la capacidad de manipular el archivo CSV de antemano.

¿Esto es inútil?

Muchas gracias de antemano por cualquier consejo.

Por cierto, vi esta importación masiva de SQL a partir de csv , pero en ese caso, TODOS los campos estaban consistentemente envueltos entre comillas. Entonces, en ese caso, podría usar '','' como delimitador, luego quitar las comillas después.

Answer 1

Chris, ¡muchas gracias por esto! ¡¡Salvaste mis galletas !! No podía creer que el cargador a granel no manejaría este caso cuando XL hace un trabajo tan agradable ... ¿no se ven estos chicos en los pasillos? De todos modos ... Necesitaba una versión de ConsoleApplication así que aquí es lo que pirateé. Está abajo y sucio, pero funciona como un campeón! Codifiqué el delimitador y comenté el encabezado porque no eran necesarios para mi aplicación.

Ojalá pudiera también pegar una buena cerveza aquí para ti también.

Geeze, no tengo idea de por qué el Módulo final y la Clase pública están fuera del bloque de código ... ¡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

Answer 2

Cree un programa de VB.NET para convertir a un nuevo delimitador usando 4.5 Framework TextFieldParser. Esto manejará automáticamente los campos calificados de texto.

Código modificado arriba para usar construido en TextFieldParser

Módulo 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

Módulo final

ReDelimit de clase pública

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

Clase final

Answer 3

Debería poder especificar no solo el separador de campo, que debería ser [,] sino también el calificador de texto, que en este caso sería ["]. Usar [] para encerrar eso para que no haya confusión con".

Answer 4

En la mayoría de los casos, este problema se debe a que los usuarios exportan un archivo de Excel a CSV.

Hay dos formas de resolver este problema:

1. Exportar desde Excel usando una macro, según la sugerencia de Microsoft
2. O la manera realmente fácil:
   • Abra el archivo CSV en Excel.
   • Guardar como archivo de Excel. (.xls o .xlsx).
   • Importe ese archivo en SQL Server como un archivo de Excel .
   • Ríase a sí mismo porque no tiene que codificar nada como las soluciones anteriores .... muhahahaha

Aquí hay algunos SQL si realmente quieres crear un script (después de guardar el archivo CSV como Excel):

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

Answer 5

Encontré algunos problemas mientras tenía '','' dentro de nuestros campos como Mike, "456 2nd St, Apt 5".

La solución a este problema es @ http://crazzycoding.blogspot.com/2010/11/import-csv-file-into-sql-server-using.html

Gracias, - Ashish

Answer 6

Encontré la respuesta de Chris muy útil, pero quería ejecutarla desde SQL Server usando T-SQL (y no usando CLR), así que convertí su código a código T-SQL. Pero luego di un paso más al envolver todo en un procedimiento almacenado que hizo lo siguiente:

1. usar inserción masiva para importar inicialmente el archivo CSV
2. limpiar las líneas usando el código de Chris
3. devolver los resultados en un formato de tabla

Para mis necesidades, limpié las líneas eliminando las comillas de los valores y convirtiendo dos comillas dobles en una comilla doble (creo que ese es el método correcto).

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, @endi-@quoteCount, 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

El procedimiento almacenado hace uso de esta función auxiliar que analiza una cadena en una tabla (¡gracias 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

Así es como lo llamo desde T-SQL. En este caso, estoy insertando los resultados en una tabla temporal, por lo que primero creo la tabla temporal:

    -- 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

No he probado mucho el rendimiento, pero funciona bien para lo que necesito, importando archivos CSV con menos de 1000 filas. Sin embargo, podría ahogarse en archivos realmente grandes.

Con suerte, alguien más también lo encuentra útil.

¡Aclamaciones!

Answer 7

Este código funciona para mí:

 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;
    }

Answer 8

Esto podría ser más complicado o complicado que lo que estás dispuesto a usar, pero ...

Si puede implementar la lógica para analizar las líneas en campos en VB o C #, puede hacerlo utilizando una función valorada de tabla CLR (TVF).

Un CLR TVF puede ser una buena forma de leer los datos desde una fuente externa cuando desee tener algún código C # o VB para separar los datos en columnas y / o ajustar los valores.

Debe estar dispuesto a agregar un conjunto CLR a su base de datos (y uno que permita operaciones externas o inseguras para que pueda abrir archivos). Esto puede ser un poco complicado o complicado, pero puede valer la pena por la flexibilidad que obtienes.

Tenía algunos archivos grandes que debían cargarse regularmente en las tablas lo más rápido posible, pero se necesitaban ciertas traducciones de códigos en algunas columnas y era necesario un manejo especial para cargar valores que de lo contrario habrían causado errores de tipo de datos con una inserción simple.

En resumen, un TVF de CLR le permite ejecutar el código C # o VB contra cada línea del archivo con un rendimiento similar a la inserción masiva (aunque es posible que deba preocuparse por el inicio de sesión). El ejemplo en la documentación de SQL Server le permite crear un TVF para leer desde el registro de eventos que podría usar como punto de partida.

Tenga en cuenta que el código en CLR TVF solo puede acceder a la base de datos en una etapa init antes de que se procese la primera fila (por ejemplo, no hay búsquedas para cada fila; usted usa un TVF normal encima para hacer tales cosas). No parece necesitar esto en función de su pregunta.

También tenga en cuenta que cada CLR TVF debe tener sus columnas de salida explícitamente especificadas, por lo que no puede escribir una genérica que sea reutilizable para cada archivo csv diferente que pueda tener.

Puede escribir un CLR TVF para leer líneas enteras del archivo, devolver un conjunto de resultados de una columna, luego usar TVF normales para leer de cada tipo de archivo. Esto requiere que el código analice cada línea para que se escriba en T-SQL, pero evita tener que escribir muchos CLR TVF.

Answer 9

Hablando desde la práctica ... En SQL Server 2017 puede proporcionar un ''Calificador de texto'' de comillas dobles, y no "reemplaza" su delimitador. A granel, inserto varios archivos que se parecen al ejemplo del OP. Mis archivos son ".csv" y tienen calificadores de texto inconsistentes que solo se encuentran cuando el valor contiene una coma. No tengo idea de qué versión de SQL Server comenzó a funcionar esta característica / funcionalidad, pero sé que funciona en SQL Server 2017 Standard. Muy fácil.

Answer 10

Necesitará preprocesar el archivo, punto.

Si realmente necesitas hacer esto, aquí está el código. Escribí esto porque no tenía opción. Es un código de utilidad y no estoy orgulloso de eso, pero funciona. El enfoque no es hacer que SQL entienda los campos entre comillas, sino manipular el archivo para usar un delimitador completamente diferente.

EDITAR: Aquí está el código en un repositorio github. Ha sido mejorado y ahora viene con pruebas unitarias! https://github.com/chrisclark/Redelim-it

Esta función toma un archivo de entrada y reemplazará todas las comas de delimitación de campo (NO las comas dentro de los campos de texto citado, solo las delimitadoras reales) con un nuevo delimitador. A continuación, puede indicarle a SQL Server que use el nuevo delimitador de campo en lugar de una coma. En la versión de la función aquí, el marcador de posición es < TMP > (estoy seguro de que esto no aparecerá en la csv original; si lo hace, prepárate para las explosiones).

Por lo tanto, después de ejecutar esta función, importe en sql haciendo algo como:

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

Y sin más preámbulos, la terrible y horrible función que pido disculpas de antemano por infligirte (edit - He publicado un programa que funciona y no solo la función en mi blog aquí ):

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

Answer 11

No es posible hacer una inserción masiva para este archivo, desde MSDN:

Para ser utilizable como un archivo de datos para la importación masiva, un archivo CSV debe cumplir con las siguientes restricciones:

• Los campos de datos nunca contienen el terminador de campo.
• O ninguno o todos los valores en un campo de datos están entre comillas ("").

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

Algún procesamiento de texto simple debería ser todo lo que se requiere para preparar el archivo para la importación. Alternativamente, se puede requerir a los usuarios que formateen el archivo de acuerdo con las pautas se o que utilicen algo que no sea una coma como delimitador (por ej. |)

Answer 12

Reuní lo siguiente para resolver mi caso. Necesitaba preprocesar archivos muy grandes y ordenar las comillas inconsistentes. Simplemente péguelo en una aplicación C # en blanco, configure los consensos según sus requisitos y listo. Esto funcionó en CSV muy grandes de más de 10 GB.

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();
        }
    }
}

Answer 13

También he creado una función para convertir un CSV a un formato utilizable para inserción masiva. Utilicé la publicación respondida por Chris Clark como punto de partida para crear la siguiente función C #.

Terminé usando una expresión regular para encontrar los campos. Luego recreé el archivo línea por línea, escribiéndolo en un nuevo archivo a medida que avanzaba, evitando tener todo el archivo cargado en la memoria.

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''s 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;
}

Answer 14

Un método alternativo: suponiendo que no tiene una carga de campos o espera que aparezca un presupuesto en los datos, sería utilizar la función REEMPLAZAR.

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

Lo he usado No puedo hacer ningún reclamo con respecto al rendimiento. Es solo una manera rápida y sucia de evitar el problema.