Create a single table by importing all CSV files in a folder?

Question

I have around 30-40 CSV files in a folder. For example, suppose folder 'Florida' has customer information from different cities of state Florida. each CSV file has customer information of one city. Now I want to create a table in SQL Server by importing all the CSV files from that folder to create a table for all customers in Florida. I wanted to know if there is any way I could perform this action for all CSV files at once. I am using SQL Server Management Studio (SSMS). All the CSV files have same column names.

I am doing the following for one CSV file:

CREATE TABLE sales.cust (
  Full_name VARCHAR (100) NOT NULL,
  phone VARCHAR(50),
  city VARCHAR (50) NOT NULL,
  state VARCHAR (50) NOT NULL,
);

BULK INSERT sales.cust
FROM 'C:\Users..............\cust1.csv'
WITH
(
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',  --CSV field delimiter
  ROWTERMINATOR = '\n',   --Use to shift the control to next row
  ERRORFILE = 'C:\Users\..............\cust1ErrorRows.csv',
  TABLOCK
)

@GiovaniSalazar I am not aware of SQLCMD if you could provide some directions on how to use sqlcmd in this question that'll be great :) @ KeithL No I do not have access to SSIS. — Tanzin Farhat, Oct 23 '19 at 20:15
Well this couldn't be easier if you had access to any tools like SSIS or even .NET. — KeithL, Oct 24 '19 at 13:37
I put together a solution of combining csv using command prompt, loading via SSMS GUI, and handling header rows. — KeithL, Oct 24 '19 at 14:06

donPablo · Answer 1 · 2019-10-24T22:35:34.487

Here is one route to get all the files into a table---

--       from Rigel and froadie @ https://stackoverflow.com/questions/26096057/how-can-i-loop-through-all-the-files-in-a-folder-using-tsql
-- 1.Allow for SQL to use cmd shell
EXEC sp_configure 'show advanced options', 1    -- To allow advanced options to be changed.
RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.

-- 2.Get all FileNames into a temp table
--for repeatability when testing in SMSS, delete any prior table
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp(csvFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B "C:\\Users..............\\*.csv"';

--       from Chompy @https://bytes.com/topic/sql-server/answers/777399-bulk-insert-dynamic-errorfile-filename
-- 3.Create sql prototype of the Dynamic sql
---- with CSV field delimiter=',' and CSV shift the control to next row='\n'
DECLARE @sqlPrototype nvarchar(500)
SET @sqlPrototype = N'BULK INSERT sales.cust
FROM ''C:\\Users..............\\xxxx''
WITH ( FIRSTROW = 2,
       FIELDTERMINATOR = '','',  
       ROWTERMINATOR = ''\n'',   
       ERRORFILE = ''C:\\Users..............\\xxxx_ErrorRows.txt'',
       TABLOCK)'

-- 4.Loop through all of the files
Declare @fileName varchar(100)
While (Select Count(*) From #tmp where csvFileName is not null) > 0
Begin
    Select Top 1 @fileName = csvFileName From #tmp

    -- 5.Replace real filename into prototype
    PRINT(@filename)
    DECLARE @sqlstmt nvarchar(500)
    Set @sqlstmt = replace(@sqlPrototype, 'xxxx', @filename)
    --print(@sqlstmt)

    -- 6.Execute the resulting sql
    EXEC sp_executesql @sqlstmt;    

    -- 4A.Remove FileName that was just processed
    Delete from #tmp Where csvFileName = @FileName
End

Caution--If ErrorFile exists, then BulkInsert will fail.

thanks for your answer. I am getting the following error "Access is denied. Invalid object name 'CSVimport'." — Tanzin Farhat, Oct 24 '19 at 19:34
@TanzinFarhat OOPs, my bad, I just edited it to change CSVimport to sales.cust (CSVimport was the name of the table that I created here to test it on my machine, and i forgot to change it to your table name) — donPablo, Oct 24 '19 at 22:35

KeithL · Answer 2 · 2019-10-24T13:58:46.227

Suggestion to use command prompt only because of limited tools.

I thought of another solution you can use that could help you out and make it so you only have to import one file.

Create your table:

CREATE TABLE sales.cust (
Full_name VARCHAR (100) NOT NULL,
phone VARCHAR(50),
city VARCHAR (50) NOT NULL,
state VARCHAR (50) NOT NULL,
);

Using command Prompt do the following:

a. Navigate to your directory using cd "C:\Users..............\"

b. Copy the files into one giant file using:

copy *.csv combined.csv

Import that file using GUI in SSMS
Deal with the headers

delete from sales.cust where full_name = 'Full_name' and phone = 'phone'

You can only do this because all columns are varchar.

Create a single table by importing all CSV files in a folder?

2 Answers2