Excel import - force column to be string (SSIS 2005)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Excel import - force column to be string...

11 posts, Page 1 of 2

1 2 »»

Excel import - force column to be string (SSIS 2005)

Rate Topic

Display Mode

Topic Options

Author

Message

dwill

Posted Tuesday, March 15, 2011 10:44 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 8:52 AM
Points: 21, Visits: 142

I know this topic has been covered in a few places - but it reoccurs regularly and I have NOT got a resolution for it.

The problem is similar to this post:

http://www.sqlservercentral.com/Forums/Topic1052632-148-1.aspx#bm1060473

It seems absolutely stunningly rediculous that I cannot import from Excel into SQL 2005 using an "Excel Source" and specify that one (or many) of the columns I am importing are to be (forced) treated as strings. The data I have is a column that is almost completely numbers... but the occasionally either an alphanumeric will legitmately appear or or even a very long set of digits (up to 20 characters).

From reading around the subject the issue is the JET driver and the way it samples the data before it 'decides' what data type to use.

Proposed solutions that I have tried are:

1) use IMEX=1 in connection does not work as the import is many rows and the strings can legitamatly appear in the last row

2) Change the TYPEGUESSROWS is not an option (and nor should it be in my opinion) - the dev PC is part of a BIG organisation and I do not hae admin rights to change the Registry.

Methods that I have tried:

3) using a 'dummy' spreadsheet to set up the connection and so make SSIS think that the column is full of strings. i.e. make a spreadsheet with string data - this works in design, but when I replace the real spreadsheet and run the datatypes are re-set!!

4) Using CSTR(field) as part of the Command SQL that selects from excel (i. "Select CSTR(fieldname) from spreadsheet). This seems to set the data types correctly but when running SSIS throws an error.

Am i missing something mind-numbingly obvious?

I do not have control over the import file, I could open it programmatically and use a Script task to process the data, but surly this is inefficent and unneccessary? I could perhaps import into a text/csv and then manipulate the data from there - again surly not an 'efficient' solution?

Any pointers much appreciated!

Post #1078503

steveb.

Posted Tuesday, March 15, 2011 10:53 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

you could change the datatype at the destination table and then either impliciitly or explicitly convert the data in the data flow.

Post #1078510

dwill

Posted Tuesday, March 15, 2011 11:05 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 8:52 AM
Points: 21, Visits: 142

The data type at the destination is varchar but the assumed data type of "double" nullifies any data that doesn't fit. So the alpha numeric data and the 'numeric' looking data that is (for example) 20 digits long are set to NULL during the import - so i do not have the data to convert.

Or do you mean something different to this?

Post #1078524

WayneS

Posted Tuesday, March 15, 2011 11:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 6,367, Visits: 8,225

About the only way to do that with the JET engine is to have some alpha characters in that field in the first few records. I agree, this is a major issue.

I don't know if the new ACE drivers are any better - have you tried them out yet? The ACE drivers are available here. If you try them out, please get back with us on how they work out!

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1078534

dwill

Posted Tuesday, March 15, 2011 11:24 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 8:52 AM
Points: 21, Visits: 142

Thanks Wayne - no I haven't and not sure I'll be able to in the 'risk free' environment I have to enhabit!

On this issue you're Avatar says it all I think Smile

!

It's crazy - I'm going to try a few more things and also see if I can get the file changed to csv.... not a pretty solution really.

Post #1078540

dwill

Posted Tuesday, March 15, 2011 1:08 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 8:52 AM
Points: 21, Visits: 142

A quick update... temporary solution is to actually turn off the "use headers" option in the connection so that the first row is text (i.e. import the headers then delete the header row later). Rubbish solution as I then need to convert the non-text fields back to whatever they are meant to be.

Post #1078615

tbanaski

Posted Monday, October 03, 2011 3:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 1:02 PM
Points: 1, Visits: 3

The issue is this: The way Excel is setup and interacts with SSIS, the first 8 rows of the column are critical (as set by the registry default). If you have mixed data types in the first 8 rows, ie numerics in some and alpha characters inothers, all will be imported fine. However if for example you have only numerics in the first 8 rows, then the column is "determined" to be an Integer and any alpha characters are dropped when the data is imported. The two solutions I have found so far are:

1. Make sure if there is to be alpha data in brought in from the column then some is in the first 8 rows.

2. Hack the registry on the machine where the job will be running to look further than 8 rows.

I would list the "IMEX=1" "fix" except when the data is numeric for each of the first 8 rows in the column then this is overridden. So for my purposes (over 15,000 lines of data and no alpha data until about 7,000 lines in) this is a non-solution.

I am currently exploring alternate solutions but I may have to go back to the source and require alpha data in the first 8 rows.

Post #1184931

Posted Wednesday, October 12, 2011 5:04 PM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:38 AM
Points: 421, Visits: 584

tbanaski,

Are you saying, that for each column in an Excel file that I want to import, I need to have an alpha Numeric value in one of the first 8 rows if that column has a mix of numeric and alpha data?

Thanks

GF

Post #1189496

sam.dahl

Posted Sunday, October 16, 2011 5:36 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 7:08 PM
Points: 364, Visits: 729

Yes, as has been explored there are very few options with the JET driver (don't know about the ACE driver). The number of rows read to determine the data type can be changed, or else you need to have the alpha character in the first few rows for string columns.

If you have a header row you can include it as data rather than a header and set all rows as text in this way, in the data flow you ignore the first row then manually convert any numbers to the appropriate type.
Or else if you can have 2 header rows, the first being your regular header row and the second being a datatype setting row, then you can set the first row as headers as normal, and the second row will be included in the data to set the data types but you filter it out later.

Post #1191092

Jeff Moden

Posted Sunday, October 16, 2011 6:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 32,890, Visits: 26,759

The way I solved this problem for my group was to simply use QUOTENAME(columnname, '"') and make sure that I used '"' as the text qualifier.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1191097

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions