Read XLS or MDB file with OpenDataSource – SQL Server 2005

Read XLS or MDB file with OpenDataSource – SQL Server 2005

Recently, I had a task to transfer data from MDB file to SQL-Server 2005 so I googling little bit and found very good way of doing so. I liked the way and felt it cool so I am sharing with you guys. Before you start this article, Please see my previous article to enable “Ad hoc distributed queries” with sp_configure. Once you enable it. Please create one XLS file, named Emp.XLS, with three columns in “sheet1”.

ID
Name
Dept

This article is moved at following location. sorry for the incovenience.

http://blog.extreme-advice.com/2009/03/02/read-xls-or-mdb-file-with-opendatasource-sql-server-2005/

Advertisements

13 Responses to “Read XLS or MDB file with OpenDataSource – SQL Server 2005”

  1. Hemant Says:

    In Excel File reading it shows error near …[Sheet1$].
    I think thr is wrong syntax. Please tell correct syntax.

  2. riteshshah Says:

    what error are you facing? I just checked this and working absolutely fine.

    SELECT * FROM
    OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source=C:\email.xls;Extended Properties=Excel 8.0’)…[Sheet1$]

    do remember, you have to have three dots before [sheet1$]

  3. NR Says:

    Hi,
    I am getting this:
    “Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.”
    where do I register it?

    NR

  4. Endy Says:

    Great post.. thanks for sharing 😀

  5. Sanjeev Thakor Says:

    It is great post for me. i solve my problem with this reply. but still i am confuse about how to read data from another sql server database using select command.

    Thanks in advance.

    • riteshshah Says:

      if you have created linked server than you can use four part naming convention for each object from one server to another….

  6. riteshshah Says:

    if you have created linked server than you can use four part naming convention for each object from one server to another….

  7. Sanjeev Thakor Says:

    Yes I create a new linked server name is “Dynamic”. And I have sql file name Company.mdf it is located on my local drive F:\Delta Folder. so what can i do for further? I hope you have any solution for me.

    Thank you again Ritesh

  8. BYRON Says:

    SELECT *
    from
    OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0’ ,
    ‘Data Source=C:\COS-20121108-20121108.mdb;User ID=Admin;Password=’).. qbcsa.empcar
    ME SALE ERROR

    El proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(null)” devolvió el mensaje “Error no especificado”.
    Mens. 7303, Nivel 16, Estado 1, Línea 1
    No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(null)”.

  9. Twitter Says:

    This is a topic that is close to my heart… Best wishes!

    Exactly where are your contact details though?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: