none
Tanya : cara menambah data lewat file excel ke SQL server 2008 R2

    Question

  • bagaimana yah caranya menambahkan data lewat file excel ke SQL Server 2008 R2?

    jadi saya sudah membuat struktur tabel di SQL server, tetapi datanya ingin di import dari file excel. apakah bisa?

    jika bisa, bagaimana caranya yah?

    soalnya cukup banyak data yg harus saya masukan.. :(

    Tuesday, July 17, 2012 9:30 AM

Answers

  • Dear Pak Diar, 

    Untuk Query dari SQL Server ke Sumber data external (Mdb, xls, Txt). bisa menggunakan metode open rowset, metode ini akan membaca data external tersebut melalui SQL Management Studio (Query Window). Metodenya adalah menyiapkan table didalam database, kemudian insert dari sumber data external tadi melaui Link Server. 

    Untuk membaca data dari Data External Microsoft Excel dapat membuat koneksi antara sql server ke sumber data tsb dng menjalankan query ini:

    DECLARE @RC int

    DECLARE @server nvarchar(128)

    DECLARE @srvproduct nvarchar(128)

    DECLARE @provider nvarchar(128)

    DECLARE @datasrc nvarchar(4000)

    DECLARE @location nvarchar(4000)

    DECLARE @provstr nvarchar(4000)

    DECLARE @catalog nvarchar(128)

    -- Set parameter values

    SET @server = 'XLTEST_SP'

    SET @srvproduct = 'Excel'

    SET @provider = 'Microsoft.Jet.OLEDB.4.0'

    SET @datasrc = 'c:\book1.xls'

    SET @provstr = 'Excel 8.0'

    EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

    @datasrc, @location, @provstr, @catalog

    Pada bagian yang ditebalkan diganti Path lokasi folder file microsoft excelnya ya.. :)

    Untuk statement selectnya bisa menggunakan query berikut

    SELECT * FROM XLTEST_SP...[Sheet1$]

    Pada bagian yang ditebalkan adalah nama worksheet dari data tersebut ya.. :)

    Seandainya ingin menjalankan proses update dimana proses updatenya melibatkan table didalam server dan File excel maka bisa menggunakan query ini ya.. tapi harus primary key (Unique Kolom) pada table dan data filenya..

    Update Table1

    SET ALAMAT=B.Alamat

    From Table1 A Inner Join XLTEST_SP...[Sheet1$] B on A.ID=B.ID

    Salam,

    Aries Budi Susilo

    Thursday, July 19, 2012 8:53 AM

All replies

  • Dear Pak Diar,

    Selamat Pagi, Hal tersebut bisa dilakukan melalui SQL Server Import and Export Wizard / SQL Server Integration Services (SSIS), dengan cara memasukkan path file excel kemudian memasukkan Informasi Login dari SQL Server. Langkah selanjutnya adalah Memappingkan kolom dari sumber data file excel tsb ke Table yang telah dibuat di Database SQL Server. Jika ada Panjang Kolom atau Tipe data dari Microsoft Excel tidak sesuai dengan kolom di table, maka Wizard tsb akan memberikan warning secara otomatis. lakukan koreksi terhadap panjang kolom atau tipe data yang ada di database dan lakukan proses mapping ulang. Untuk proses yang periodik, maka Package SQL Server Import and Export Wizard / SQL Server Integration Services (SSIS) bisa disimpan dengan ekstensi file .dtsx, setelah diexport kemudian dapat dibuatkan Job Automatisasi melalui SQL Agent, sehingga setiap hari, minggu, bulan job tersebut dapat Run secara otomatis. Package tersebut juga dapat disetting Path Folder Lokasi Excelnya, Nama server dan Otentifikasi koneksinya. ada beberapa link yang bermanfaat sebagai learning source bapak, silahkan dibuka di

    http://msdn.microsoft.com/id-ID/sqlserver/cc511477.aspx

    http://ssisctc.codeplex.com/

    Selamat Mencoba.. :)

    Salam,

    Aries Budi Susilo.

    Wednesday, July 18, 2012 12:14 AM
  • dear Aries Budi Susilo,

    saya sudah coba lewat import export data, tetapi knp yah yg terbaca hanya 255 colom/ field, sedangkan data di dalam excel saya ada sekitar 300an.

    saya sudah coba berkali-kali tetap saja masih terbaca sebanyak 255 colom/field. jika di import melalui *.csv bisa terbaca semua, tetapi itu tidak mungkin karena di dalam data yg akan saya inputkan ada tanda koma (,) di dalamnya, jadi akan terbaca oleh system bahwa itu adalah batas dari kolom datanya..

    satu lagi, jika ingin memasukan data di field tertentu bagaimana caranya?

    contoh kasus, saya mempunyai field (no, nama, alamat, nilai)

    pada filed no, nama dan alamat telah diisi data, dan saya ingin memasukan data di alamatnya saja sesuai dengan nama dan alamat yg telah terisi,

    itu bagaimana caranya?

    mohon pencerahaannya..

    salam.. :)


    • Edited by Diar raiD Wednesday, July 18, 2012 2:01 AM
    Wednesday, July 18, 2012 1:35 AM
  • Dear Pak Diar,

    Kalo begitu bisa mengggunakan Link Server. berikut cara penggunaannya..

    DECLARE @RC int

    DECLARE @server nvarchar(128)

    DECLARE @srvproduct nvarchar(128)

    DECLARE @provider nvarchar(128)

    DECLARE @datasrc nvarchar(4000)

    DECLARE @location nvarchar(4000)

    DECLARE @provstr nvarchar(4000)

    DECLARE @catalog nvarchar(128)

    -- Set parameter values

    SET @server = 'XLTEST_SP'

    SET @srvproduct = 'Excel'

    SET @provider = 'Microsoft.Jet.OLEDB.4.0'

    SET @datasrc = 'c:\book1.xls'

    SET @provstr = 'Excel 8.0'

    EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

    @datasrc, @location, @provstr, @catalog

    SELECT * FROM OPENQUERY (XLTEST_SP, 'Select * from [Sheet1$]')

    SELECT * FROM XLTEST_SP...[Sheet1$]

    Kalo ingin mengupdate datanya.. berarti harus ada UNIQUE Key pada table SQL dan Kolom Primary ke pada File Excelnya..

    Update Table1

    SET ALAMAT=B.Alamat

    From Table1 A Inner Join XLTEST_SP...[Sheet1$] B on A.ID=B.ID

    Selamat Mencoba..:)

    Salam,

    Aries Budi Susilo

    Wednesday, July 18, 2012 3:55 PM
  • mas Aries Budi Susilo,
    maaf mas saya kurang paham maksudnya.. :(
    bisa di jelasin lebih detil ngga?
    Thursday, July 19, 2012 1:26 AM
  • Dear Pak Diar, 

    Untuk Query dari SQL Server ke Sumber data external (Mdb, xls, Txt). bisa menggunakan metode open rowset, metode ini akan membaca data external tersebut melalui SQL Management Studio (Query Window). Metodenya adalah menyiapkan table didalam database, kemudian insert dari sumber data external tadi melaui Link Server. 

    Untuk membaca data dari Data External Microsoft Excel dapat membuat koneksi antara sql server ke sumber data tsb dng menjalankan query ini:

    DECLARE @RC int

    DECLARE @server nvarchar(128)

    DECLARE @srvproduct nvarchar(128)

    DECLARE @provider nvarchar(128)

    DECLARE @datasrc nvarchar(4000)

    DECLARE @location nvarchar(4000)

    DECLARE @provstr nvarchar(4000)

    DECLARE @catalog nvarchar(128)

    -- Set parameter values

    SET @server = 'XLTEST_SP'

    SET @srvproduct = 'Excel'

    SET @provider = 'Microsoft.Jet.OLEDB.4.0'

    SET @datasrc = 'c:\book1.xls'

    SET @provstr = 'Excel 8.0'

    EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

    @datasrc, @location, @provstr, @catalog

    Pada bagian yang ditebalkan diganti Path lokasi folder file microsoft excelnya ya.. :)

    Untuk statement selectnya bisa menggunakan query berikut

    SELECT * FROM XLTEST_SP...[Sheet1$]

    Pada bagian yang ditebalkan adalah nama worksheet dari data tersebut ya.. :)

    Seandainya ingin menjalankan proses update dimana proses updatenya melibatkan table didalam server dan File excel maka bisa menggunakan query ini ya.. tapi harus primary key (Unique Kolom) pada table dan data filenya..

    Update Table1

    SET ALAMAT=B.Alamat

    From Table1 A Inner Join XLTEST_SP...[Sheet1$] B on A.ID=B.ID

    Salam,

    Aries Budi Susilo

    Thursday, July 19, 2012 8:53 AM
  • dear mas Aries Budi Susilo,

    saya masih muda mas, blum pantes di panggil pak.. hehe..

    terima kasih tutornya, nanti akan saya coba. soalnya sekarang lagi membuat aplikasi dulu, nanti saya posting lagi hasil dari query-nya.. :)

    maaf mas, boleh minta emailnya, biar nanti saya bisa diskusi lagi mengenai sql server-nya :)

    salam,

    Diar 


    • Edited by Diar raiD Thursday, July 19, 2012 9:06 AM
    Thursday, July 19, 2012 9:04 AM
  • Dear Mas Diar,

    Silahkan hubungi saya lewat profile saya saja ya.

    Ada beberapa tutorial SQL Server yang saya buat di Blog saya di 

    http://ariestwin.wordpress.com/

    Salam,

    Aries Budi Susilo

    Thursday, July 19, 2012 2:10 PM