Professional Web Applications Themes

Excel to SQL Server - Microsoft SQL / MS SQL Server

Greetings! I want to load data from Excel file to SQL Server. I can't use "Import/Export", so I added a linked server in SQL Server. One of the columns in Sheet1 (Excel File) is something like: 13 13 13D 13E .... But when I execute the following query in Query yzer: select * from ExcelSource...Sheet1$ The column gives me: 13.0 13.0 NULL NULL .... How can I specify the data type of an Excel file? I tried to "Format Cells" in Excel and set it to "Text", but it didn't work. It does work if I double click each cell. ...

  1. #1

    Default Excel to SQL Server

    Greetings!

    I want to load data from Excel file to SQL Server. I can't use
    "Import/Export", so I added a linked server in SQL Server.

    One of the columns in Sheet1 (Excel File) is something like:

    13
    13
    13D
    13E
    ....

    But when I execute the following query in Query yzer:

    select * from ExcelSource...Sheet1$

    The column gives me:

    13.0
    13.0
    NULL
    NULL
    ....

    How can I specify the data type of an Excel file? I tried to "Format Cells"
    in Excel and set it to "Text", but it didn't work. It does work if I double
    click each cell. I have thousands of rows, so it is impossible for me to
    change it manually. Please help me!

    Thanks in advance!

    Neo


    Neo Guest

  2. #2

    Default Re: Excel to SQL Server

    Neo,

    See if the following helps.

    Make sure the Jet driver is using enough rows to
    guess the data type of the column:

    exec master..xp_regwrite
    rootkey='HKEY_LOCAL_MACHINE',
    key='SOFTWARE\Microsoft\Jet\4.0\Engines\Excel',
    value_name='TypeGuessRows',
    type='REG_DWORD',
    value=200
    go

    Tell the Jet driver to import mixed types as text:

    exec master..xp_regwrite
    rootkey='HKEY_LOCAL_MACHINE',
    key='SOFTWARE\Microsoft\Jet\4.0\Engines\Excel',
    value_name='ImportMixedTypes',
    type='REG_SZ',
    value='Text'
    go

    If this doesn't work for the linked server, you could
    try using OpenRowSet instead, and add IMEX=1, which
    I don't fully understand, but seems to avoid having the
    driver registry settings be ignored.

    from OpenRowSet(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=e:\excel\Properties.xls;HDR=YES;IMEX= 1'
    ,'select * from Properties'
    )
    go

    -- Steve Kass
    -- Drew University
    -- Ref: 9A0A1365-F785-47D0-82F3-2B46EBBC128C

    Neo Chou wrote:
     

    Steve Guest

  3. #3

    Default Re: Excel to SQL Server

    Thank you very much. I will try it.

    Neo

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    Cells" [/ref]
    double 
    >[/ref]


    Neo Guest

Similar Threads

  1. Replies: 3
    Last Post: January 14th, 11:47 PM
  2. Saving Excel data into MS SQL Server
    By AZDeveloper in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: March 12th, 09:06 PM
  3. import into excel file from SQL-Server
    By peteyjr in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 11th, 03:29 PM
  4. Server side excel programming
    By Avik in forum ASP.NET General
    Replies: 2
    Last Post: July 22nd, 11:29 AM
  5. Retrieving data from sql server to excel in vb.net
    By Anne in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 14th, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139