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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
Angel02Author 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?
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Angel02Author 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.
Angel02Author 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

            For i = 1 To cWeekCnt
                dt.Rows.Add("intWeek" & i)

 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)


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

Please advise.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angel02Author 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ãoMSSQL Senior EngineerCommented:
Angel02, you still have the issue or it's already solved?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.