Subscribe to
Posts
Comments

If you are working with SQL Server then you will find from time to time you will need to import data from Excel into SQL server in order to query or manipulate the data easily. One way and I think the most used method to import data from Excel into SQL server is to use DTS (Data Transformation Service).

DTS is a powerful tool and it is very easy to use but there is pitfall. When DTS import Excel data it will try to guess the data type of the columns to be imported.

Once it determines the data type of a column it will ignore the data that is not conforming to the data type and does not do any implicit conversion. The result is that if your column has number and text then most likely your number or your text will became NULL after they are imported into SQL server. I had tried to see whether DTS has properties I can change to take care of this problem but I failed. So a workaround have to be use to take care of this issue.

One way to work around this problem is to open your excel file, copy your column to notepad and then format the column as text in excel. After that copy the data from notepad and paste it back to the column as text. After that the column can be import with out losing any values. You would ask what if I need a scheduled job to do that. Well there is another way. That is to link the Excel file into SQL server by running the following statement.

EXEC sp_addlinkedserver 'Excel', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\Book1.xls', NULL, 'Excel 8.0;HDR=Yes;IMEX=1'

Then you use a sql statement like this to import the data into SQL server. You may need to change the security setting of the linked server in order to run the query successfully

select * into ExcelData from Excel...Sheet1$

The trick is on the option “IMEX=1″. It tells the Jet engine not to guess the data type and treat them all as text (nvarchar by default)


Related Posts:

  • How to Prevent Innodb Data File Keep Growing
  • T-SQL Query to Get Database Size
  • A Fast Way to Update Large Amount of Data in SQL Server With Select Into Statement
  • Mortgage Calculator in Excel
  • SQL Server Backup Device Error or Device off-line


  • 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
    Loading ... Loading ...

    RSS feed | Trackback URI

    8 Comments »

    Comment by mygr8r
    2007-12-04 14:38:33
    MyAvatars 0.2

    The Data UpLoader to SQL Server that’s available free on the Pervasive DataTools web site let’s you map data from Excel and many other data sources to tables in SQL Server. It includes a transformation scripting language for cleaning and manipulating the data, and it lets you select the data type for each field in the SQL Server table from a list of real SQL Server data types. Pretty cool tool…I would recommend it to just about anyone who needs to convert data to SQL Server.

    Hope this helps!

    Comment by orange_square
    2008-09-02 07:55:53
    MyAvatars 0.2

    Thanks a lot for the tip. Worked perfectly!

     
     
    Comment by Travis Hyatt
    2008-03-03 10:40:22
    MyAvatars 0.2

    Thanks so much for that tip.
    I was banging my head into a wall for a day or so.
    Worked perfectly!
    Thanks,
    Travis

     
    Comment by Luis Moran Subscribed to comments via email
    2008-03-05 10:43:53
    MyAvatars 0.2

    Thanks a lot.
    I shall be saying good bye to all those CONCATENATE apostrophe formulas in Excel.

    Moran

     
    Comment by Alan Houck
    2008-06-19 08:26:13
    MyAvatars 0.2

    Another alternative is a registry hack on the SQL Server host machine:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel
    change “TypeGuessRows” to 0

    This tells DTS to examine all the data before setting the data type.

     
    Comment by dit bukiss
    2009-02-25 08:19:26
    MyAvatars 0.2

    Rad… i was wracking my brain trying to figure out why I kept importing nulls when the data was clearly there. I even tried converting the cell formatting in excel to just TEXT… still nothing. the notepad trick worked.

     
    Comment by SQLMadness
    2009-06-08 11:15:38
    MyAvatars 0.2

    I wanted to thank you for this tip. It help me sort out a problem with NULL values when importing to SQL using Jet engine.

    Comment by Andrew Chen
    2009-06-10 22:57:50
    MyAvatars 0.2

    I am glad that help. Thank you for commenting :cool:

     
     
    Name (required)
    E-mail (required - never shown publicly)
    URI
    Subscribe to comments via email
    Your Comment (smaller size | larger size)
    You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.