Import to SQL from a tricky excel file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Import to SQL from a tricky excel file

Author

Topic

L_i_L_i
Starting Member

6 Posts

Posted - 2013-11-02 : 21:41:43

Hi!

I have this scenario. A bunch of Excel files that are autogenerated every day and I am now building the SSIS-package that will import them into a table. There is an Excel file per Pilot and day and they look like this (General worksheet):

There is one more worksheet, with some more info, and it looks like this:

And finally, my SQL table looks like this:

I have managed to grab the data from B8 to the end of table, using openrowset. I couldn't use row #7 for column names because of merged cells that it contains. Now, I need to import pilot_id and date from either cells A2(the value within brackets) and A3 in General worksheet, or cells C1 and I1&J1&K1 in the additional worksheet. I wrote a script (as a source) and grabbed Pilot ID value from the cell C1 in the second worksheet, but I don't know how to go on with that task in SSIS. Should I first import the data and than update the table with pilotid where the value is null. But how do I update the table from the script task? I already have excel file as source component. And is the update the best way to do this, when I have several Excel files that I am going to import using a loop? I mean is it safe to use update where value is null so I don't mess it up when importing other Excel files from the folder, I have an Excel file per Pilot ID. Please suggest the order in which to do this, Excel source, script source, SQL destination.... Or if u have any suggestions or ideas of doing this in some other way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 03:15:30

I think best thing would be have a parallel data flow path within same data flow task to move contents of B1 and I1,J1,K1 to another table. have a derived column in both the data flow paths (the existing one and new one above) to add filename (you get it using a SSIS variable created and you can use for each loop with file enumerator for getting it) to data flow.
Then using filename join the two tables and update values of pilot_id and date to other table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

L_i_L_i
Starting Member

6 Posts

Posted - 2013-11-05 : 03:42:26

You really rock! :) Thanks a lot, this now works... BUT The script component fails if I don't open the excel file first... very strange... I tried with files localy and on a server and I tried to run the package from BIDS and as SSIS job, I even tried with a new file to avoid eventually protections but it's still same, the script cannot access the file. When I first open the file and have it opened, it works great...
The error message is:
system.runtime.interopservices.COMException (0X800A03EC) Cannot access name of the file... :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 04:24:13

quote:
Originally posted by L_i_L_i

You really rock! :) Thanks a lot, this now works... BUT The script component fails if I don't open the excel file first... very strange... I tried with files localy and on a server and I tried to run the package from BIDS and as SSIS job, I even tried with a new file to avoid eventually protections but it's still same, the script cannot access the file. When I first open the file and have it opened, it works great...
The error message is:
system.runtime.interopservices.COMException (0X800A03EC) Cannot access name of the file... :(

where's the file originally present? is that path accessible to login which executes the package. Also is the original extension of file xls/xlsx?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

L_i_L_i
Starting Member

6 Posts

Posted - 2013-11-05 : 05:26:43

I put the file in a folder on my desktop (for testing purposes) and I am the one executing the package from BIDS. And I can open the file from Excel, and when I do that and file is open, the script works... :S The extension of the file is xls.

This is the VBscript:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.Office.Interop.Excel

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub PreExecute()
MyBase.PreExecute()
'
' Add your code here for preprocessing or remove if not needed
'
End Sub

Public Overrides Sub PostExecute()
MyBase.PostExecute()
'
' Add your code here for postprocessing or remove if not needed
' You can set read/write variables here, for example:
' Me.Variables.MyIntVar = 100
'
End Sub

Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
'
Dim objExcel As Object = CreateObject("Excel.Application")
Dim FileName As String = Me.ReadOnlyVariables("User::FilePath").Value.ToString()
Dim objBook As Object = objExcel.Workbooks.Open(FileName)

Dim objSheet As Object = objBook.Worksheets("CCSInfo")

Output0Buffer.AddRow()
Output0Buffer.Pilot = CDbl(objSheet.Range("C1").value)
Output0Buffer.Datum = CDbl(objSheet.Range("F1").value) & "_" & CDbl(objSheet.Range("G1").value) & "_" & CDbl(Right(objSheet.Range("H1").value, 2))

End Sub

End Class

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 06:04:59

is protection on for xls file? have you set a password?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

L_i_L_i
Starting Member

6 Posts

Posted - 2013-11-05 : 07:18:50

The workbook is not protected but the sheets are, so u cannot change anything in them, but u can still open it and view it without the password. That's why I wrote that I already tried to create a new workbook without protection but the error is still there. One thing that just hit me, is to try to build another excel source instead of the script, and than one more for the date. The excel source I already have works just fine but the script doesn't. I could use openrowset for pilotid and date in two more excel sources and than merge them all just like u suggested. I'll give it a try...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources