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!