Importing Excel Data

[wbodger]

Here at my office we frequently have to import external data that we get from various sources, a process that almost always has issues. This latest one, for instance, seems odd to me. When a field in Excel has more than 255 characters, it is failing. If we then save that excel file as a .csv file and import the flat file where we can adjust the output field length definition, it imports correctly. When importing the Excel file I adjust the field size as well, but it still fails. Any thoughts?

wb

 

[fredericofonseca]

http://msdn.microsoft.com/en-us/library/ms141683.aspx

TypeGuessRows on registry needs to be changed

And make sure you have IMEX=1 on your connection string.

And... if you are not using the Microsoft ACE driver, you should consider using it, more specifically the 2010 version, with its SP1 installed

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

Perhaps I should have stated we are using Office 2010 and SQL Server 2008 R2? I see that the link applies to 2012 or 2005, how different is 2008 R2?

 

[wbodger]

Modified the registry and still get the error:

Error 0xc020901c: Data Flow Task 1: There was an error with output column "cDEDefinitionorProposedforExport" (24) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)

Not sure where I put IMEX=1 in the connection string?

 

[fredericofonseca]

if you read the link you will see it mentions it applies to all versions.

you are using obviously a SSIS package - on that package you will have a Excel connection - which one you will need to check - if it is not the ACE driver you should change it as I mentioned.

the IMEX=1 is placed on the connection string of the connection object you are using to read the excel file.

see

http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with

which contains info relating to your particular problem, and some ways of fixing it other then the IMEX=1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886