SQL Server Excel Worksheets Import

Question

I am working on an import script, the idea being to import multiple workbooks into one table. I have made progress, so I am able to import one workbook successfully into my table. What I want to do is create a query that will loop a folder read the file names and import the data into my database in Microsoft SQL Server Management Studio.

--Creating the TABLE--
CREATE TABLE BrewinDolphinHoldings
(
    recordID INT AUTO_NUMBER
    FUNDNAME VARCHAR(25),
    SEDOL VARCHAR(7),
    ISIN VARCHAR(11),
    NAME VARCHAR(20),
    WEIGHT INTEGER(3)
)

constraint[pk_recordID]PRIMARYKEY
(
    [recordID] ASC
)

INSERT INTO BrewinDolphinHoldings
VALUES
("HoldingsData', GB125451241, DavidsHoldings, 22)

--SELECTING THE SHEET--

SELECT/UPDATE? * 
FROM OPENROWSET('Microsoft.JET.OLEDB.4.0',
                'Excel 8.0;Database=X:\CC\sql\DEMO\SpreadsheetName + '.xlsx',
                'SELECT * FROM [Sheet1$]') AS HoldingsData

So essentially my question is, I want to create a loop a loop that will read the file name in a directory, and the import will read that name every time it loops and import the relevant spreadsheets? so,for example:

DECLARE SpreadsheetName as STRING 
DECLARE FileExtension as '.xlsx'

FOR EACH ITEM IN DIRECTORY
X=1
Y=MAX
FILENAME THAT LOOP READS = SpreadsheetName
SELECT * FROM
OPENROWSET('Microsoft.JET.OLEDB.12.0',
'Excel 8.0;Database=X:\CC\sql\DEMO\SpreadsheetName + fileExtension.xls
END LOOP

So, I'm thinking maybe something like this? Although I don't know if the loop will overwrite my database? maybe instead of UPDATE I should use INSERT? I don't want to use SSIS, preferably a query, although if anyone can recommend anything I could look into, or, help me with this loop It would greatly help

I'm open to new ideas from you guys, so if anyone can try and fix my code, or give me a few examples of imports for multiple excel sheets, would be greatly appreciated!

I'm new to SQL Server, I do have some previous programming experience!

Thanks!

Answer 1

You can use bcp to do what you are talking about to import any type of delimited text file, such as csv or text tab delimited. If it is possible generate/save the spreadsheets as csv and use this method. See these links.

Import Multiple CSV Files to SQL Server from a Folder

http://www.databasejournal.com/features/mssql/article.php/3325701/Import-multiple-Files-to-SQL-Server-using-T-SQL.htm

If it has to be excel, then you can't use bcp, but these should still help you with the logic for the loops on the file folders. I have never used the excel openrowset before, but if you have it working like you said, it should be able to insert in just the same. You can still use the xp_cmdshell/xp_dirtree to look at the files and generate the path even though you can't import it with bcp.

How to list files inside a folder with SQL Server

I would then say it would be easiest to do a insert from a select statement from the openrowset to put it into the table.

http://www.w3schools.com/sql/sql_insert_into_select.asp

Make sure xp_cmdshell is enabled on your sql server instance as well.

https://msdn.microsoft.com/en-us/library/ms190693(v=sql.110).aspx