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.