Inserção em massa do SQL Server do arquivo CSV com cotações inconsistentes

É possível BULK INSERT (SQL Server) um arquivo CSV no qual os campos são apenas OCIDIONALMENTE cercados por aspas? Especificamente, aspas apenas envolvem os campos que contêm um “,”.

Em outras palavras, tenho dados parecidos com isso (a primeira linha contém headers):

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 

Como as aspas não são consistentes, não posso usar “”, “” como um delimitador e não sei como criar um arquivo de formato responsável por isso.

Eu tentei usar ‘,’ como um delimitador e carregá-lo em uma tabela temporária, onde cada coluna é um varchar e, em seguida, usar algum processamento kludgy para remover as aspas, mas isso também não funciona, porque os campos que contêm ‘,’ são divididos em várias colunas.

Infelizmente, não tenho a capacidade de manipular o arquivo CSV antecipadamente.

Isso é sem esperança?

Muito obrigado antecipadamente por qualquer conselho.

By the way, eu vi este post em massa SQL importação de csv , mas nesse caso, cada campo foi consistentemente envolto em aspas. Então, nesse caso, ele poderia usar ‘,’ como um delimitador, e depois remover as aspas depois.

Você precisará pré-processar o arquivo, ponto final.

Se você realmente precisa fazer isso, aqui está o código. Eu escrevi isso porque eu absolutamente não tive escolha. É código de utilidade e não me orgulho disso, mas funciona. A abordagem não é fazer o SQL entender os campos entre aspas, mas manipular o arquivo para usar um delimitador totalmente diferente.

EDIT: Aqui está o código em um repository github. Foi melhorado e agora vem com testes de unidade! https://github.com/chrisclark/Redelim-it

Esta function recebe um arquivo de input e replaceá todas as vírgulas delimitadoras de campo (NÃO as vírgulas dentro dos campos de texto citado, apenas as delimitadoras reais) por um novo delimitador. Você pode então dizer ao sql server para usar o novo delimitador de campo em vez de uma vírgula. Na versão da function aqui, o espaço reservado é < TMP > (tenho certeza de que isso não aparecerá no csv original – se isso acontecer, prepare-se para explosões).

Portanto, depois de executar essa function, você importa no sql fazendo algo como:

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

E sem mais delongas, a function terrível e terrível que peço desculpas antecipadamente por infligir em você (editar – eu postei um programa de trabalho que faz isso em vez de apenas a function no meu blog aqui ):

 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 

Não é possível fazer uma inserção em massa para este arquivo, no MSDN:

Para ser usado como um arquivo de dados para importação em massa, um arquivo CSV deve obedecer às seguintes restrições:

  • Campos de dados nunca contêm o terminador de campo.
  • Nenhum ou todos os valores em um campo de dados são colocados entre aspas (“”).

( http://msdn.microsoft.com/pt-br/library/ms188609.aspx )

Algum processamento de texto simples deve ser tudo o que é necessário para obter o arquivo pronto para importação. Alternativamente, os usuários podem ser obrigados a formatar o arquivo de acordo com as diretrizes ou usar algo diferente de uma vírgula como um delimitador (por exemplo, |)

Eu achei a resposta do Chris muito útil, mas eu queria executá-la de dentro do SQL Server usando o T-SQL (e não usando o CLR), então eu converti seu código para o código T-SQL. Mas então eu dei um passo adiante envolvendo tudo em um procedimento armazenado que fez o seguinte:

  1. usar inserção em massa para importar inicialmente o arquivo CSV
  2. limpar as linhas usando o código de Chris
  3. retornar os resultados em um formato de tabela

Para minhas necessidades, limpei as linhas removendo aspas em torno dos valores e convertendo duas aspas duplas em uma aspa dupla (acho que esse é o método correto).

 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 

O procedimento armazenado faz uso desta function auxiliar que analisa uma string em uma tabela (obrigado 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 

Aqui está como eu chamo de T-SQL. Neste caso, estou inserindo os resultados em uma tabela temporária, portanto, criei a tabela temporária primeiro:

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

Eu não testei muito o desempenho, mas ele funciona bem para o que eu preciso – importar arquivos CSV com menos de 1000 linhas. No entanto, pode engasgar com arquivos realmente grandes.

Espero que alguém também ache útil.

Felicidades!

Eu também criei uma function para converter um CSV em um formato utilizável para Bulk Insert. Eu usei o post respondido por Chris Clark como ponto de partida para criar a seguinte function C #.

Acabei usando uma expressão regular para encontrar os campos. Em seguida, recriou o arquivo linha por linha, escrevendo-o em um novo arquivo, evitando assim que todo o arquivo fosse carregado na memory.

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

Mais frequentemente, esse problema é causado por usuários que exportam um arquivo do Excel para CSV.

Existem duas maneiras de contornar este problema:

  1. Exportar do Excel usando uma macro, conforme a sugestão da Microsoft
  2. Ou a maneira realmente fácil:
    • Abra o CSV no Excel.
    • Salvar como arquivo do Excel. (.xls ou .xlsx).
    • Importe esse arquivo para o SQL Server como um arquivo do Excel .
    • Chuckle to yourself porque você não tem que codificar nada como as soluções acima …. muhahahaha

Importar como arquivo do Excel

Aqui está um pouco de SQL se você realmente quiser fazer o script (depois de salvar o 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$]') 

Isso pode ser mais complicado ou envolvido do que o que você está disposto a usar, mas …

Se você pode implementar a lógica para analisar as linhas em campos em VB ou C #, você pode fazer isso usando uma function com valor de tabela CLR (TVF).

Um TVF do CLR pode ser uma boa maneira de executar a leitura de dados de fonts externas quando você deseja que algum código C # ou VB separe os dados em colunas e / ou ajuste os valores.

Você precisa estar disposto a adicionar um assembly CLR ao seu database (e um que permita operações externas ou inseguras para que ele possa abrir arquivos). Isso pode ficar um pouco complicado ou envolvido, mas pode valer a pena pela flexibilidade que você obtém.

Eu tinha alguns arquivos grandes que precisavam ser carregados regularmente em tabelas o mais rápido possível, mas certas traduções de código precisavam ser executadas em algumas colunas e manipulação especial era necessária para carregar valores que teriam causado erros de tipo de dados com uma inserção em massa simples.

Em suma, um TVF CLR permite que você execute o código C # ou VB em cada linha do arquivo com desempenho de inserção em massa (embora você possa precisar se preocupar com o registro). O exemplo na documentação do SQL Server permite criar um TVF para ler o log de events que você pode usar como ponto de partida.

Note que o código no TVF do CLR só pode acessar o database em um estágio init antes que a primeira linha seja processada (por exemplo, nenhuma pesquisa para cada linha – você usa um TVF normal além disso para fazer essas coisas). Você não parece precisar disso com base na sua pergunta.

Observe também que cada CLR TVF deve ter suas colunas de saída explicitamente especificadas, portanto você não pode escrever um genérico que seja reutilizável para cada arquivo csv diferente que possa ter.

Você poderia escrever um CLR TVF para ler linhas inteiras a partir do arquivo, retornando um conjunto de resultados de uma coluna e, em seguida, usar TVFs normais para ler para cada tipo de arquivo. Isso requer que o código analise cada linha a ser escrita em T-SQL, mas evita ter que gravar muitos CLR TVFs.

Um método alternativo – supondo que você não tenha uma carga de campos ou espere que uma cotação apareça nos dados em si seria usar a function REPLACE.

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

Eu usei isso. Não posso fazer nenhuma reclamação em relação ao desempenho. É apenas uma maneira rápida e suja de contornar o problema.

Você deve ser capaz de especificar não apenas o separador de campos, que deve ser [,] mas também o qualificador de texto, que neste caso seria [“]. Usando [] para delimitá-lo para que não haja confusão com”.

Eu encontrei alguns problemas ao ter “,” dentro de nossos campos como Mike “, 456 2nd St, Apt 5”.

A solução para esse problema é @ http://crazzycoding.blogspot.com/2010/11/import-csv-file-into-sql-server-using.html

Obrigado, Ashish

Chris, Muito obrigado por isso !! Você salvou meus biscoitos !! Eu não podia acreditar que o carregador a granel não iria lidar com este caso quando o XL faz um trabalho tão bom.não esses caras vêem um ao outro nos corredores ??? De qualquer forma … Eu precisava de uma versão do ConsoleApplication, então aqui está o que eu hackeei juntos. Está baixo e sujo, mas funciona como um campeão! Eu codifiquei o delimitador e comentei o header, pois eles não eram necessários para meu aplicativo.

Eu gostaria de poder também colocar uma boa cerveja aqui também.

Geeze, eu não tenho idéia do porque o Módulo Final e a Classe Pública estão fora do bloco 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 mim:

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

Eu juntei o abaixo para resolver o meu caso. Eu precisava pré-processar arquivos muito grandes e separar as citações inconsistentes. Basta colá-lo em um aplicativo em branco C #, defina os consts para suas necessidades e você vai longe. Isso funcionou em CSVs muito grandes com mais 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(); } } } 

Falando da prática … No SQL Server 2017, você pode fornecer um ‘Qualificador de texto’ de aspas duplas e ele não “substitui” seu delimitador. Eu em massa inserir vários arquivos que se parecem com o exemplo pelo OP. Meus arquivos são “.csv” e eles têm qualificadores de texto inconsistentes que só são encontrados quando o valor contém uma vírgula. Não tenho ideia de qual versão do SQL Server esse recurso / funcionalidade começou a funcionar, mas sei que funciona no SQL Server 2017 Standard. Bem fácil.

Crie um Programa VB.NET para converter em um novo Delimitador usando o TextFieldParser 4.5 Framework. Isso manipulará automaticamente campos qualificados de Texto.

Modificado acima código para usar construído em TextFieldParser

Módulo Módulo1

 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 

Classe final