We help IT Professionals succeed at work.

Importing into SQL database from Excel.

I have data in the following format in Excel. It was created using a transform query.

PRODUCT      CATEGORY      Sun, Jan 4 2015      Sun, Jan 11 2015      Sun, Jan 18 2015      Sun, Jan 25 2015      Sun, Feb 1 2015
A                       ABC                      3                         1                                 1                                     2.5                       1
B                       DEF                        0                         5                                  3.00                             4                       0.00
C                       GHI                              2.5                      2                                 1                                 2                              3
D                       JKL                              8                          0.00                        0.00                            7                        8
E                      MNO                      6                        6                                7                                    0.00                    5

I need to insert this data using VB.NET windows form into a SQL table of the following format

Product             Category      Qty       Year        Week
A                         ABC              3            2015        1
A                         ABC              1            2015        2
A                         ABC              1            2015        3
A                         ABC              2.5         2015       4
A                         ABC              1            2015        5
B                         DEF              0            2015        1
B                         DEF              5            2015        2
B                         DEF              3            2015        3
----
----

Which is the fastest way of doing it? The excel might have around 2000 rows and number of weeks at a time might be 5-10.
Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
In SQL Server T-SQL that would be an UNPIVOT, ass-u-me-ing that the date column headers will be interpreted as dates, into a table with that date.

You'll have to define the logic for converting the dates to week numbers.

Author

Commented:
Should I Read from Excel and insert into a temporary table and then UNPIVOT or can I directly Unpivot data read from Excel and then insert into my DB table?
If this is a one-time import, use the "Import and Export Data" utility (which does nothing but generates and executes an SSIS package).

For a long-term solution, my recommendation would still be to create a small SSIS package that reads data from the Excel file and inserts into a defined SQL Server table.

Author

Commented:
I am creating a VB.NET Windows application which selects the Excel file and does the import to SQL. I believe I can write queries in the code to do the import.

Author

Commented:
There is another issue. The number of weeks in the Excel could vary. I have to import only the weeks that are mentioned in the Excel. The weeks always start with 1 and increment by 1.

I created a datatable dt and added columns

 dt.Columns.Add(product)
 dt.Columns.Add(Cat)
 
            For i = 1 To cWeekCnt
                dt.Rows.Add("intWeek" & i)
            Next

 MyConnection = New System.Data.OleDb.OleDbConnection _
                ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
            '("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" & "Extended Properties=""Excel12.0;HDR=NO;IMEX=1""")
            MyDA = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)

            MyDA.Fill(dt)

I am not sure what to do next. Should I save this table in the database so I can run an Unpivot query like mentioned here https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx.

Please advise.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>The number of weeks in the Excel could vary. I have to import only the weeks that are mentioned in the Excel.
Not sure what this means, as you can't import something that's not there.

>The weeks always start with 1 and increment by 1.
Ok.  Recommend importing the Excel as it is, with the dates being imported as dates.
Then after the import you can use T-SQL to add columns 'Year' and 'Week', and update with values based on the date.

As an aside, it's really playing with fire to (1) import data from Excel and (2) import pivoted data and have to unpivot.  
Is there any chance you can get a normalized flat file?

Author

Commented:
You mean import the Excel as it is to SQL Database? I imported as it is  into a temporary table in my code above.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Angel02, you still have the issue or it's already solved?