Link to home
Start Free TrialLog in
Avatar of Angel02
Angel02

asked on

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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
Avatar of Angel02
Angel02

ASKER

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.
Avatar of Angel02

ASKER

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.
Avatar of Angel02

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Angel02

ASKER

You mean import the Excel as it is to SQL Database? I imported as it is  into a temporary table in my code above.
Angel02, you still have the issue or it's already solved?