How to Import 21k Comma Delimited Text Files having a few columns with quote delimited comma separated values into SQL Server

Question

I inherited a broken process, need to fix it and I'm hoping you, my fellow DBAs, may have a few good solutions.

There's a Windows server folder containing 21,663 CSV files that need to be imported into a new table. Although the process downloads the hourly files, the process appears to randomly fail to import each file on an hourly basis and continues to do the job poorly.

The good news is that all the CSV files have the same naming convention, same 27 columns and column names, are delimited with commas and are being imported into a table with all varchar columns.

The challenge is that five of the columns occasionally contain comma separated values wrapped with double quotation marks, which incorrectly imports a good portion of the data into the wrong columns. Another challenge is that the files cannot be downloaded again with different delimiters. Redownloading that many files wouldn't be ideal by any stretch but I didn't initially rule it out until I discovered the data is no longer available for download.

Scaled-down example of CSV data ('Header' & 'Row(n)' added for readability):

Header
DispatchDateTime,IncidentNumber,PatientChiefComplaint,PrimaryImpression,DispositionItemID

Row1 (No issues, imports correctly)
1/6/2020 9:36:17 PM,2020-8675309,chest pain,Chest Pain/Discomfort,Transported No Lights/Siren

Row2 (Double-Quoted values import incorrectly at each comma)
1/7/2020 1:44:52 PM,2020-9035768,"Slurred speach, right side weakness",Stroke,"Patient evaluated, Transported Lights/Siren"

The following code imported 30+ million rows from all the files, however, not surprising the columns with comma separated values were incorrectly split across other columns due to the extra commas.

DECLARE @filename VARCHAR(255),
        @path     VARCHAR(255),
        @sql      VARCHAR(8000),
        @cmd      VARCHAR(1000)

CREATE TABLE dbo.Files ([Path] VARCHAR(255),[FileName] VARCHAR(255))

SET @path = 'G:\FolderPath\'
SET @cmd = 'dir ' + @path + '*.csv /b'

INSERT INTO dbo.Files([FileName])
EXEC Master.dbo.XP_CMDSHELL @cmd

UPDATE dbo.Files
SET    [Path] = @path
WHERE  [Path] IS NULL

DECLARE c1 CURSOR FOR
  SELECT [Path],[FileName]
  FROM   dbo.Files
  WHERE  [FileName] LIKE '%.csv%'

OPEN c1
FETCH NEXT FROM c1 INTO @path, @filename

WHILE @@FETCH_STATUS <> -1
  BEGIN
      SET @sql = 'BULK INSERT ESORawJasonTest FROM '''
                 + @path + @filename + ''' ' + '     WITH ( 
                   FIELDTERMINATOR = '','', 
                   ROWTERMINATOR = ''\n'', 
                   FIRSTROW = 2 
                ) '

      PRINT @sql
      EXEC (@sql)
      FETCH NEXT FROM c1 INTO @path, @filename
  END

CLOSE c1
DEALLOCATE c1 

Please let me know if you have any suggestions for handling the multiple delimited column characters. The server is running SQL Server 2016 (SP3-GDR) and Visual Studio 2017 is an option as well. I lack experience with other coding languages but would be open to suggestions. Ideally, I'm hoping for a solution that can be used on an ongoing basis since hourly imports are needed. Thank you in advance for any help you can offer!

Answer 1

So I'm not going to call it impossible, but it's not easily possible to do on your version of SQL Server unfortunately.

As per the docs on BULK INSERT - Import data from a CSV file, it was only in SQL Server 2017 when CSV file importing was directly supported, to be able to utilize double quotes as escape characters around each column of data in the CSV file:

Beginning with SQL Server 2017 (14.x), BULK INSERT supports the CSV format, as does Azure SQL Database.

Before SQL Server 2017 (14.x), comma-separated value (CSV) files aren't supported by SQL Server bulk-import operations.

But it goes on to say:

However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. For information about the requirements for importing data from a CSV data file, see Prepare Data for Bulk Export or Import (SQL Server).

Those docs then indicate that you can utilize OPENROWSET() and the OLE DB Provider for Jet to be able to use the parameter FORMAT=CSVDelimited:

On 32-bit systems (SQL Server 2014 and below), it is possible to import CSV data into a SQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet. Jet treats text files as tables, with the schema defined by a schema.ini file that is located in the same directory as the data source. For CSV data, one of the parameters in the schema.ini file would be "FORMAT=CSVDelimited". To use this solution, you would need to understand how the Jet Text IISAM operates (its connection string syntax, schema.ini usage, registry setting options, and so on.) The best sources of this information are Microsoft Access Help and Knowledge Base (KB) articles. For more information, see Initializing the Text Data Source Driver, How To Use a SQL Server 7.0 Distributed Query with a Linked Server to Secured Access Databases, HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases, and How To Open Delimited Text Files Using the Jet Provider's Text IIsam.

Note, while the docs mention SQL Server 2014 and below there, I imagine that's dated verbiage. I would be surprised if it weren't possible on SQL Server 2016. But it'll certainly require work to setup. You may almost be better off cleaning every CSV file by replacing any escaped commas.

Answer 2

Following up to show how I resolved this in case anyone else runs into a similar issue.

I used the following PowerShell code to import the existing comma delimited files and export the data into new pipe delimited files.

Get-ChildItem -Path . -Filter *.csv | 
  ForEach-Object {
    $filename = $_.Name
    Import-Csv -Delimiter ',' -Path "G:\OriginalFolderPath\$filename" | 
    Export-Csv -Delimiter '|' -Path "E:\NewFolderPath\$filename" -NoTypeInformation
}

After the new pipe delimited files were created in a new Windows folder, I used the following SQL query to import the data. The updated FIELDTERMINATOR and ROWTERMINATOR parameters made this effort successful.

DECLARE @filename VARCHAR(255),
        @path     VARCHAR(255),
        @sql      VARCHAR(8000),
        @cmd      VARCHAR(1000)

CREATE TABLE dbo.Files ([Path] VARCHAR(255),[FileName] VARCHAR(255))

SET @path = 'E:\NewFolderPath\'
SET @cmd = 'dir ' + @path + '*.csv /b'

INSERT INTO dbo.Files([FileName])
EXEC Master.dbo.XP_CMDSHELL @cmd

UPDATE dbo.Files
SET    [Path] = @path
WHERE  [Path] IS NULL

DECLARE c1 CURSOR FOR
  SELECT [Path],[FileName]
  FROM   dbo.Files
  WHERE  [FileName] LIKE '%.csv%'

OPEN c1
FETCH NEXT FROM c1 INTO @path, @filename

WHILE @@FETCH_STATUS <> -1
  BEGIN
      SET @sql = 'BULK INSERT dbo.ESORawJasonTest FROM '''
                 + @path + @filename + ''' ' + '     WITH ( 
                   FIELDTERMINATOR = ''"|"'', 
                   ROWTERMINATOR = ''"\n'', 
                   FIRSTROW = 2 
                ) '

      PRINT @sql
      EXEC (@sql)
      FETCH NEXT FROM c1 INTO @path, @filename
  END

CLOSE c1
DEALLOCATE c1