reading in variables from a csv file in a SQL procedure

Question

I have hundreds of .xlsx files that I would like to import into a database table. I have the names of the files in a csv file. I would like to loop through that csv file to make the import process of these hundreds of files more simple.

Here is my current insert into script which I have to write out for each file.

INSERT INTO TableName Select *, 'FileName' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

My issue is that I have to repeat/change this statement hundreds and eventually thousands of times for each different filename.

so the next few statements would look like this.

INSERT INTO TableName Select *, 'FileName2' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName2.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

INSERT INTO TableName Select *, 'FileName3' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName3.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

INSERT INTO TableName Select *, 'FileName4' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName4.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

INSERT INTO TableName Select *, 'FileName5' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName5.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

...

INSERT INTO TableName Select *, 'FileName700' as 'SurveyName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\FileName700.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Answer 1

Please replace C:\work\ with your folder name where 1000 of *.xlsx located. Run below script in SSMS with you modification.

Script

   -- Create temp table
    create table #files  (ID int IDENTITY, FileName varchar(100))

    BULK
    INSERT #files 
    FROM 'c:\test.csv'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )

    select  
    'INSERT INTO TableName Select *, ''' + FileName + ''' as ''SurveyName'' 
    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
    ''Excel 12.0;Database=C:\work\' + FileName + ';HDR=YES'', ''SELECT * FROM [Sheet1$]'')' as 'InsertSql'
     from #files
-- Drop temp table
Drop table #files

Results will give you instert statement for each .xlsx file. Copy/paste these statements in to SSMS and execute(F5) to load the data.

INSERT INTO TableName Select *, 'Files1.xls' as 'SurveyName'   FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  'Excel 12.0;Database='C:\work\Files1.xls';HDR=YES', 'SELECT * FROM [Sheet1$]')
INSERT INTO TableName Select *, 'Files2.xls' as 'SurveyName'   FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',  'Excel 12.0;Database='C:\work\Files2.xls';HDR=YES', 'SELECT * FROM [Sheet1$]')

Copy and paste