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.
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.
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.
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.
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.
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.OleDbCon nection _
("Provider=Microsoft.ACE.O LEDB.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.OleDbDat aAdapter(" 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.
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.OleDbCon
("Provider=Microsoft.ACE.O
'("Provider=Microsoft.Jet.
MyDA = New System.Data.OleDb.OleDbDat
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
You'll have to define the logic for converting the dates to week numbers.