error when reading from excel as datasource

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 2005 Forums

SSIS and Import/Export (2005)

error when reading from excel as datasource

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-25 : 10:10:06

I have an excel file containing a column with text that has an underscore in it. SSIS does not read these, It leaves the rows that contains underscore.

How can I fix this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 10:14:57

Which datatype are you using for import?

E 12°55'05.63"
N 56°04'39.26"

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-25 : 15:30:29

The source datatype(Excel) is DT_WSTR(255) and the target is NVARCHAR(255) (OLE DB Destination).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 16:27:29

Never heard of this error. I will look around to see if I can find something.

E 12°55'05.63"
N 56°04'39.26"

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-25 : 16:31:37

Thanks for your help Peso!
To be more accurate the entire rows are not set to null only the columns that contains the underscore.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 16:42:17

Have you set the proper IMEX mode?

E 12°55'05.63"
N 56°04'39.26"

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-25 : 16:51:56

When I test setting the connection string I get an error.
CANNOTAQUIRECONNECTIONFROMCONNECTIONMANAGER

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test_omsparad_Regionfil.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“;

This is unexplored area to me, so any help are appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 17:06:42

www.connectionstrings.com

E 12°55'05.63"
N 56°04'39.26"

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-26 : 03:19:12

I tested the following query usin openrowset:
INSERT INTO testRegion3
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Test_omsparad_Regionfil.xls;HDR=YES;IMEX=1',
'SELECT * FROM [Blad1$]')

And the result was:
Excel file: Table:
1011513 1.01151e+006
5.17801e+006 5178011_A

This happened when I set the IMEX to 1.
Table datatype is: varchar(255), excel datatype is: double precision.

Any ideas how to solve this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 04:52:28

IMEX 1 (import mode) checks first 8 records (as default) and chooses datatype accordingly.
You can either change the default setting in registry to check for more records, or alter the mixed type conversion data type.

See ImportMixedTypes and TypeGuessRows in registry.

The registry path for Jet 4.0 is
Hkey_Local_Machine\Software\Microsoft\Jet\4.0\Engines\Excel
This happens occasionally because Excel is not really a database server source.

A good choice for registry settings for these two entries can be

TypeGuessRows=0
ImportMixedTypes=Text

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources