Post reply

Import Excel to MS Access Problem

  • December 13, 2007 at 4:21 am

    #73877

    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

  • December 13, 2007 at 4:25 am

    #759753

    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]

  • December 13, 2007 at 4:29 am

    #759755

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

    any other way to do this...?

    - Prakash.C

  • December 14, 2007 at 2:24 am

    #760107

    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

  • December 14, 2007 at 5:21 am

    #760155

    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.

  • December 14, 2007 at 5:59 am

    #760183

    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

  • December 14, 2007 at 12:46 pm

    #760431

    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