DTS Package Import of HTM and column type manipulation

[tdudley]

I built a DTS Import Package to import an HTM file and it went smooth as silk. Replaced the file with another HTM Table of the same name and whammo a text field turned into an integer file and data goes missing. The first 20 or so rows of a particular column were all numbers. I tried to do a disconnected edit to alter the Source column to a string but the Edit button just disappears without changing the column's type value. The only way I can think of getting around this is to use something archiac like EDLIN to add a new table row at the top of the HTM Table with values to force the specified type. Anyone think of a better solution?

[Rawhide]

I have the same problem with Excel files if a column is formatted as "general" instead of string. Unfortunately, this behavior is by design. It is a limitation of the ISAM used to connect to the data type.

The work around I have for the Excel files is to save the Excel file as a text file. The ISAM for connecting to text files doesn't have the same limitation.

[Stephen]

You can do better with Excel with a couple steps.

1. By default, excel scans the first 8 (I think) rows to determine the data types for each column. The actual number of rows is determine by a registry setting which can be found in HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\Type GuessRows. You can get a better result by increasing the number stored here.

2. The OLEDB provider can have one of it properties updated (in the DTS package). By defaults the "Extended Properties" has a value of "Excel 8.0;HDR=YES;". Changing this to "Excel 8.0;HDR=YES;IMEX=1;" can also give you a better result. To make this change, you will need to use disconnected edit in your DTS package and from there go the to properties of the connection.

And before you ask, it has been a while so if you need an explanation of these, do what I did ... go to Google.

And whether any of this applies to the HTML file problem, I don't know.

[Rawhide]

Thanks for the suggestion!! I'll try out the IMEX=1 suggestion (on Monday).

Increasing the TypeGuessRows value in my case won't help because the Excel file almost never has that many rows. Generally, it's less than 5 records per day.

[Rawhide]

Suggestion #2 worked like a charm. Thanks!!

[mEmENT0m0RI]

I've got an Excel file with three sheets. IMEX=1 worked for two, but I still had to alter registry for the third one to import without nulls.
Thank's Stephen!

[roopesh_vp]

I tries this method but i am getting an error saying invalid connection string attribute. Plz tell me where i went wrong.