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$]')

I've modified the question to make it more clear. Let me know if that helps. — Geoff Dawdy, Feb 06 '13 at 15:00
So are each of these workbooks constantly updated, or will `FileName5.xlsx` essentially become "read only" (for the sake of argument) once the survey in `FileName6.xlsx` goes live? — swasheck, Feb 06 '13 at 15:43
They will remain in place for as long as is needed. So in essence, yes they are read only for these purposes. — Geoff Dawdy, Feb 06 '13 at 19:26
Are you required to do this purely via SQL? This sounds like a task better suited to SSIS. — alroc, Feb 06 '13 at 20:27

Hiten004 · Accepted Answer · 2013-02-06T20:21:35.610

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

edited Feb 06 '13 at 20:21

answered Feb 06 '13 at 16:19

Hiten004

2,425
1
22
34

@AaronBertrand now the quotes are fixed in the answer. – Hiten004 Feb 06 '13 at 16:28
FileName700 should also be the filename variable. I want to be able to track which file the records were attached to. – Geoff Dawdy Feb 06 '13 at 17:58
I also don't mind pointing to a folder location which contains all of the files but my original question did ask for a way to read these values in from a csv file. – Geoff Dawdy Feb 06 '13 at 18:05
I think some of your quotes aren't nested correctly again. I tried running the script but I'm now running into some issues with the bulk insert. – Geoff Dawdy Feb 06 '13 at 19:30
Please try it now. Next time please post error. its hard to work with out Error. – Hiten004 Feb 06 '13 at 20:23

reading in variables from a csv file in a SQL procedure

1 Answers1