How to split and load data from Excel to DB?

Question

I have 24 columns of data in Excel Sheet from A to X. Business requirement is to load data into Two tables. In Table1 we have to load the data of A to J columns and in Table2 load data of K to X columns In DB, Table1 has ID column Primary Key with having Identity which is Foreign Key of Table2. In Excel sheet, we don't have ID but data of rest of columns have. Could anyone please help me how to split and load data from Excel into two tables?

Answer 1

Hi,

almost any migrating tool can do the task in two steps. First pick the relevant data to one table and then to the second table.

For example, you can use one INSERT query which select from OPENROWSET that read the data directly from the Excel file and in the query you can get only the relevant columns for this table and manipulate the data as needed in the query itself.

In this document you have a list of tools which you can use for the task. The basic point in your case, is that you should import to each table separately. Simply read the doc, select the solution fits you and do it twice.

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017&WT.mc_id=DP-MVP-5001699

Answer 2

Hi @Ramana Kopparapu ,

You may use SSIS to do that.

When you use excel source, you may use sql command such as below:

select * from [Sheet1$A100:K100]

And then add another dataflow or excel source to get the rest data in excel. Check: How to read data from an Excel file starting from the nth row with SQL Server Integration Services Regards， Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.