SSIS Excel import cannot handle my spreadsheet

Question

Post reply

SSIS Excel import cannot handle my spreadsheet

born2bongo

SSCrazy

Points: 2477
More actions
March 11, 2010 at 5:29 am

#156768

I've spent 2 days trying to get this spreadsheet into SQL server. I've not been able to find a solution that works.
I am using SQL Server 2005
OpenRowSet/OpenDataSource are disabled
I have columns that contain dates only, and columns that contain time only. Sometimes there is no applicable time for a date, and the spreadsheet then contains N/A (not #N/A).
Excel displays the time as 6:00 AM and the actual values are 06:00:00
All the date columns are coming out dd/m/yyyy 00:00:00, which is fine.
A time column that is always populated is showing as 30/12/1899 hh:mm:ss, which is fine.
A data reader on the output of the Excel source component shows that, if the column is a mixture of times and N/A, the text is kept but the times are set to null.
SSIS has destroyed my data before I can get to it and convert it.
I started to follow an MSDN lead where I would use a linked server for the spreadsheet. The linked server was created but was unable to access the sheet as a table. It could not give a specific cause of the problem.
I don't have control of the spreadsheet format so how can I get this data in?
And why is it so hard to connect these 2 products by the same vendor?
TIA
b2b

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

If the data in a column is of mixed datatypes, you should put the IMEX=1 option in your connectionstring.

For example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\myServer\myExcelFile.xls";Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

This will force the Jet adapter to read in the column as text. Then, filter out the N/A with a conditional split and convert the remaining rows back to the datetime datatype.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

born2bongo SSCrazy Points: 2477 More actions · Answer 2

da-zero (3/11/2010)
If the data in a column is of mixed datatypes, you should put the IMEX=1 option in your connectionstring.
For example:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\myServer\myExcelFile.xls";Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";
This will force the Jet adapter to read in the column as text. Then, filter out the N/A with a conditional split and convert the remaining rows back to the datetime datatype.

Thanks for the quick response. I've just added IMEX=1 to the connection string and straight away the data viewer is showing fractions where it used to have nulls. Now I just need to format them

Cheers

b2b

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:

* Excel Source component - for reading data from Excel worksheet.

* Excel Destination component - for writing data in Excel worksheet.

* Excel Task - for manipulating Excel workbooks.

* Excel Connection - used by the components above and also for implementing custom scripts based on it.

Also the Excel components above doesn't try to be smart about the cell data and will not try to format the returned data to fit in specific type. You can also get the raw data from the worksheet.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

dg227 SSCrazy Points: 2017 More actions · Answer 4

Just another thought on Excel books that you don't control: parsing the Excel to a flat file using PowerShell or Perl is very handy, and also avoids metadata issues, if it's a regular import.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

dg227 (6/11/2010)
Just another thought on Excel books that you don't control: parsing the Excel to a flat file using PowerShell or Perl is very handy, and also avoids metadata issues, if it's a regular import.

Yes, but it requires Office installation. Also the PowerShell performance cannot match the speed of the SSIS data flow, in case you want to direct the data to another object. There is introspection going on at runtime, whenever data is transferred between objects and this slows down the process approximately 10x.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

dg227 SSCrazy Points: 2017 More actions · Answer 6

Partially: if you run PowerShell using COM objects to extract the data, then yes, Office is required and will load on the machine that the script runs on. Otherwise, you can read the data out as an OleDbConnection to the Excel book, which wouldn't load the book as a COM object, and is much faster than the COM method (minutes faster, depending on the sheets and data).

Perl just needs the appropriate modules installed, which doesn't require Office.

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

dg227 (6/11/2010)
Partially: if you run PowerShell using COM objects to extract the data, then yes, Office is required and will load on the machine that the script runs on. Otherwise, you can read the data out as an OleDbConnection to the Excel book, which wouldn't load the book as a COM object, and is much faster than the COM method (minutes faster, depending on the sheets and data).
Perl just needs the appropriate modules installed, which doesn't require Office.

True. I forgot about that.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/