SQL Server Insertar a granel de archivo CSV con comillas inconsistentes

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

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

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

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!

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 fieldList = Regex.Matches(line, @"\A[^,]*(?=,)|(?:[^"",]*""[^""]*""[^"",]*)+|[^"",]*""[^""]*\Z|(?<=,)[^,]*(?=,)|(?<=,)[^,]*\Z|\A[^,]*\Z") .Cast() .Select(m => RemoveCSVQuotes(m.Value).Replace(PH1, '¦')) .ToList(); //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; } 

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

Importar como archivo de Excel

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$]') 

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.

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.

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

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

Chris, ¡muchas gracias por esto! ¡¡Salvaste mis cookies !! 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 ,") 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 

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

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"; ///  /// 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" ///  ///  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)); } } } } ///  /// Fully quote a partially quoted line ///  /// The partially quoted line /// The fully quoted line 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 - ie 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(); } } } 

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.

Cree un progtwig 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 ,,") End If obj = Nothing Console.Write(vbNewLine & vbNewLine) 'Console.ReadLine() End Sub 

Módulo final

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

Clase final