Import Excel to MS Access Problem

  • Hi All,

    I'm having a Excel Sheet With first column which has numeric data for first 70 or 80 records then comes some string data, here i'm using this statement to import data from excel to MS Access DataBase, but its not retriving the records which were in string data type, is there any other way to do this...?

    SELECT [Item #], [Description], [Pack Size], [Price] FROM

    [Database=C:\Test.xls;Excel 8.0;HDR=Yes;IMEX=2].[Sheet1$] Where

    [Item #] Is Not Null

    thank's in Advance.

    - Prakash.C

  • If I remember correctly the IMEX setting should be 1(one).



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • Hi Ade thanks, i also tried IMEX also, but that too does'nt helps same result.

    any other way to do this...?

    - Prakash.C

  • I recall having a similar problem quite some time ago and according to the notes I took at the time the way I got round it was to amend the Registry value at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to a higher value than the default of 8. By doing this, the driver will inspect more rows before it decides what type of data it is working with.

    If you don't fully understand the Registry PLEASE DO NOT DO TOUCH IT - messing up your registry can cause your entire machine to stop working.

    HTH.

    Mark

  • The cowards way out, when you can, is to simply add (and later discard) top rows containing text to keep Excel from incorrect assumptions about the rest of the row contents.

  • Can you convert the item to text in the select statement?

    SELECT CAST([Item #] as varchar(20)) , [Description], [Pack Size], [Price] FROM

    [Database=C:\Test.xls;Excel 8.0;HDR=Yes;IMEX=2].[Sheet1$] Where

    [Item #] Is Not Null

  • As a quick and dirty fix (assuming this is a one time data load), you can set the data cells in the column to Text in the .XLS file, forcing the numbers to be loaded into a SQL Server datatype such as char/varchar, to get the data loaded into the SQL Server Table.

    Happy T-SQLing.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply