How to force SQL Server 2008 express import from Excel to a specific data type?

Question

Hi, I'd like to force SQL Management Studio - Import Data - from XLS excel file to read one column as specific data type? It does a type guessing. My first N rows contain decimal data, but some later columns have also characters in there. I know I need to specify IMEX=1 in the connection string to the XLS file, but as far as I know this could be done only if I were using ADO.NET application to do it. (this forces Jet engine to honor registry setting HKML\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes which is set to Text).

It could also be done if I had developer/enterprise edition of sql server 2008, and editing DTS package to include the connection string. But I use Express edition that does not allow saving DTS packages - what are my other options? Thanks

Answer 1

Add a first row having text data that you remove from the target table after importing the data.

Answer 2

Use the OLE DB Provier for Access Database Engine (or Jet depending on version of Excel), click the 'Properties' button to bring up the 'Data Link Properties' dialog, click the 'All' tab and in the list edit the 'Extended Properties' item to add IMEX=1.

Where to get the OLE DB provider? The version for the Access Database Engine, known as ACE, which was released as part of Access2007, can be used to open Jet 4.0 data sources including Excel spreadsheets. It can be downloaded from MSDN as 2007 Office System Driver: Data Connectivity Components. The stated supported operating systems are: Windows 7, Windows Vista, Windows Server 2008, Windows Server 2003 and Windows XP. There could be an equivalent for Access2010 but I'm out of the loop.