none
(Sumber: milis SQL Server) Baca File Excel RRS feed

  • Pertanyaan

  • Hi All,

    Saya punya aplikasi di PC00401 sedangkan databasenya ada di PC00402.

    Di PC00401 ada file excel yang ingin saya baca lewat sql server Pathnya di C:\tes.xls

    gimana querynya ya agar bisa dibaca file tersebut lewat sql server.

    Kalau aplikasi sama database di server yang sama saya bisa lakukan query berikut:

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\tesx.xls', 'SELECT * FROM [Revisi$]')

    tp ini di server yang berbeda. Mohon bantuannya.

    thanks


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 30 November 2012 03.53
    Moderator

Jawaban

  • pertama2 download dan install ini dulu

    Microsoft Access Database Engine 2010 Redistributable
    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
    pilih yg AccessDatabaseEngine_x64.exe

    trus entar pakenya
    select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=C:\sharefolder\tesx.xls;HDR=YES'
    ,'SELECT * FROM [Revisi$]')

    jika error nya
    "Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ has been denied"
    lakukan ini

    USE [master]
    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    trus masuk ke registry, cari "DisallowAdHocAccess" di set ke 0,biasanya di

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Providers\Microsoft.ACE.OLEDB.12.0

    atau

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL 10.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0    ---- untuk sql 2008

    kalau tidak ada cari "DisallowAdHocAccess" kalau ketemu tapi tidak di "Microsoft.ACE.OLEDB.12.0", cari key tersebut biasanya di sekitarnya,
    kalau ketemu tapi tidak ada "DisallowAdHocAccess", buat sendiri dengan tipe Dword di set ke 0.

    moga2 jalan.



    Dijawab oleh: Zabreak


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 30 November 2012 04.05
    Moderator

Semua Balasan

  • baca file excelnya melalui aplikasi atau manual atau by schedule.

    kalo melalui aplikasi,

    buat share folder di server sql nya, trus pasti kan ada tombol untuk menjalankan perintah itu, nah pada waktu tombol itu di tekan (onclick), jalankan proses copy file excelnya ke share folder dari server sqlnya, baru jalankan perintah itu.

    misal share folder yg di server sql nya di taro di "c:\sharefolder", jadi perintahnya
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\sharefolder\tesx.xls', 'SELECT * FROM [Revisi$]')

    intinya sih copy dulu file excelnya ke server sql, baru di jalankan perintahnya.



    Dijawab oleh: Zabreak


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 30 November 2012 04.04
    Moderator
  • Maaf mas mau tanya lagi. Kalo aku tes di komputer yang 32 bit sih jalan. cuma pas aku tes di server yang 64 bit muncul error
    The OLE DB provider "Microsoft.JET.OLEDB.4.0" has not been registered.

    mohon sarannya


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 30 November 2012 04.04
    Moderator
  • pertama2 download dan install ini dulu

    Microsoft Access Database Engine 2010 Redistributable
    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
    pilih yg AccessDatabaseEngine_x64.exe

    trus entar pakenya
    select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=C:\sharefolder\tesx.xls;HDR=YES'
    ,'SELECT * FROM [Revisi$]')

    jika error nya
    "Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ has been denied"
    lakukan ini

    USE [master]
    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO

    trus masuk ke registry, cari "DisallowAdHocAccess" di set ke 0,biasanya di

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Providers\Microsoft.ACE.OLEDB.12.0

    atau

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL 10.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0    ---- untuk sql 2008

    kalau tidak ada cari "DisallowAdHocAccess" kalau ketemu tapi tidak di "Microsoft.ACE.OLEDB.12.0", cari key tersebut biasanya di sekitarnya,
    kalau ketemu tapi tidak ada "DisallowAdHocAccess", buat sendiri dengan tipe Dword di set ke 0.

    moga2 jalan.



    Dijawab oleh: Zabreak


    Best Regards,
    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Jumat, 30 November 2012 04.05
    Moderator