Search code examples
sql-serverexcelssisetl

Is there a way to collect data from excel specific cells to send to SQL Server?

I have a template excel sheet that I want users to fill out daily. One sheet tells me what cells are read/write (meaning which ones I want to send to my data base). The form is not tubular ie. sometimes data is setup (A3 -> A4) or (A3-> B3).

I was wondering is there a excel add on or any way I can read certain cells then send them to my db after the form has been completed.

I have looked into Excel documentation. which says: import data in a single step, directly from Excel to SQL, by using one of the following tools: The SQL Server Import and Export Wizard SQL Server Integration Services (SSIS) The OPENROWSET function You can import data in two steps, by exporting your data from Excel as text, and then using one of the following tools to import the text file: The Import Flat File Wizard The BULK INSERT statement BCP The Copy Wizard (Azure Data Factory) Azure Data Factory I also have looked into VBA a bit.

Would one of these be the best path?

any suggestions are appreciated.

I have tried to use The SQL Server Import and Export Wizard with SQL Server Integration Services (SSIS) But my templates dont follow a normalized table layout, which I cannot control.

I have tried to use Microsoft Excels built in JavaScript API's but I couldn't find a way to connect a sever side language to it.

Solution

  • You can achieve that in two approaches:

    Using SQL command as data source

    In the Excel Source editor, change the Access mode to SQL Command and specify the range after the sheet name, as example:

    SELECT * FROM [Sheet1$A3:A4]

    Using Script Component as Source

    You can use a Script Component as Source and within the Script you can use Interop.Excel.ddl assembly to read the Excel cells and generate desired output:

    Helpful links