November 11, 2013 at 3:26 am
Hi,
I've been using SSIS for quite some tome reading Excel Files, and I just encountered an error I had never met before.
I have a .xls file download from the web (I put it as an attachment).
The issue I have is, whatever connection I use (every version of the "Excel" Connection source, OLE DB with Jet driver...), I can never get the first column.
The preview always shows the "B" column as "F1", instead of the A Column, which is nowhere to be found.
Only workaround I have is to open the file, save it, and then it's good to go, so I guess it was made with a different software, but automating this means going the interop route which is not really a good idea in a server environment.
So if any of you has any idea on how to fix this file automatically (even through C#) I'd be extremely grateful.
Thanks!
EDIT: Forgot to say, I'm using SQL Server 2012 on Windows 2008R2 Environment
November 11, 2013 at 7:18 am
Place Excel source for Reading Excel file and give column names as per requirement and then place condition split Transformation and in condition split place following expression IS NULL(Column Name) i.e F1 then the Destination Transformation.
As per my Knowledge this is the best option .
when you place above condition null rows will eliminate you will get actual data from source..
when your read the data with out condition split you will get first 4 rows as null rows from source. so to eliminate that iam using Condition split .
I tried with provided Excel file i got 365 Rows from file.
if your going c# code its look length code.... its up to you.....
November 11, 2013 at 7:25 am
Issue is not getting the good number of rows.
Issue is I'm not getting the first column (the one that has dates in it).
However I found a workaround : I added an "execute SQL Task" that creates a new "Dummy" tab to the excel file. Wirdly it seems to "fix" the excel file and I'm getting the right columns now.
November 11, 2013 at 7:34 am
i am able to get total number of rows from source first line to last line .
when we are working with files we have to look over with data and number of rows only.
there will different ways in work ...
finally your problem is resolved its great ...........:-P
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply