Excel den Sql e veri gönderim metotları

bkts34 · 15 Şubat 2018

Merhaba,

Excel dosyamda makrolar ile hesapladığım verileri Sql de bir tabloya kaydetmek istiyorum. Yaptığım araştırmalarda bunun "İnsert into" veya record set açarak olduğunu gördüm ki bu şekilde yapıyorum da şuan zaten. Ancak her iki yöntemde de excelde ki veriler döngü kurulup alınıyor, bunun bizim için dezavantajı verilerimiz biraz fazla satır ve sütun içeriyor ve Sql server bağlantısı da ortak ağdan olduğu için aşırı yavaşlama oluyor, donuyor, tıkanıyor.

Bu nokta da işin erbablarına sorum şu şekilde ;
Excelde ki verileri döngü ile değilde blok olarak gönderemez miyiz Sql tablosuna ?
Yani nasıl ki Ado ile veri çekerken CopyfromRecordset deyip tek seferde excele alıyorsak gönderirken de aynı bu gibi bir blok gönderim söz konusu olabilir mi ?

Yardımcı olabilecek varsa çok makbule geçecektir, şimdiden teşekkür ediyorum.

bkts34 · 15 Şubat 2018

Fikri olan varmıdır üstadlarım, ?

Zeki Gürsoy · 15 Şubat 2018

Merhaba.

Bir sql script hazırlayın. Çok fazla ise text dosyasına kaydedin. Ör:

Kod:

insert into tablo (alanlar) values(değerler);
insert into tablo (alanlar) values(değerler);
insert into tablo (alanlar) values(değerler);

Bunların tamamını tek seferde connection.Execute ile çalıştırın.

Not: Çoğu veritabanı batch execute destekler.

.

bkts34 · 15 Şubat 2018

Merhaba Zeki bey Iyi akşamlar ve ilginiz için çok teşekkür ederim öncelikle.
Ancak Sql de çok bilgim yok malesef, scriprt olustirmayi veya neyi nereye nasil yazacagimi tam kestiremiyorum ama sabah ilk is ugrasacagim bu yöntemle cok sağolun tekrar, teşekkür ederim. Iyi akşamlar.

bkts34 · 16 Şubat 2018

Merhaba Zeki bey,

Ben bir türlü yapamadım, araştırıyorum ama becerebildiğim birşey yok henüz, rica etsem aşağıda size örnek olarak vereceğim adlara göre, sizin önerdiğiniz yöntem ile script hazırlayıp ( sanırım text dosyası daha iyi olur ) sql e nasıl göndereceğime dair ( connection.execute dediğiniz kısım) örnek bir kod bloğu hazırlayabilirmisiniz ?

Excelin konumu : ‪C:\Users\bkts\Desktop\
Excel dosyası adı : asd.xlsm
Sql e göndereceğim sayfa adı : Sayfa1, sütunlar Id,Name
Sql server adı : X
Database adı : Y
Tablo adı : Z

Not: Sql benim localimde kurulu,

Yanlış anlaşılmasın diye tekrar belirtmek istiyorum, verileri oluşturmayı ve sql e göndermeyi tamamen excel den yapmak istiyorum.

Zeki Gürsoy · 16 Şubat 2018

Burada anlaşımayan şey, komut sanırım. Diğerleri için örneğin connection bağlantı aynı. Sql komutu noktalı virgül ile birleşecek o kadar. Değişken mi yaparsınız, dosyadan mı alırsınız, o size kalmış.

Kod:

dim cn as new adodb.connection

cn.open "..."

dim sql as string 

sql = "insert into Z (id, name) values(1, 'Zeki')[SIZE=3][COLOR=Red][B];[/B][/COLOR][/SIZE]"
sql = sql & "insert into Z (id, name) values(2, 'Gürsoy')[SIZE=4][COLOR=Red][B];[/B][/COLOR][/SIZE]"
sql = sql & "insert into Z (id, name) values(3, 'bkts34')[SIZE=4][COLOR=Red][B];[/B][/COLOR][/SIZE]"

cn.execute sql

cn.close

alicimri · 16 Şubat 2018

Benim de yapmak istediğiniz gibi bir işleme ihtiyacım vardı, kodla bir çözüm bulamadım. Diğer kullanıcıların çalışmadığı bir vakitte, "Microsoft SQL Server Management Studio" ile tabloyu silip, Excel'den aynı isimle yeni tablo ekledim. Bu işlemde veri türünde değişiklik yapıyor, sizin için bu durum sorun olmayacaksa kullanabilirsiniz..

alicimri · 16 Şubat 2018

Sn. Zeki Gürsoy teşekkür ederim, Benim de öteden beri kafama takılan bir soruna çözüm getirmişsiniz.
Sn. bkts34'nin sorununu 3000 kayıt ile aşağıdaki kod ile denedim 3-4 saniyede başarılı sonuç aldım.

Kod:

 Sub aa()
  Dim conn As ADODB.Connection
        Dim sConnString As String
        sConnString = "Provider= SQLOLEDB;Data Source=" & "X;" & "Initial Catalog=" & "Y" & ";Integrated Security=SSPI;"
       Set conn = New ADODB.Connection
        conn.Open (sConnString)
        Dim sql As String
For i = 2 To Range("A65536").End(3).Row
sql = sql & "insert into Z (id, name) values(" & Range("A" & i) & ", " & "'" & Range("B" & i) & "');"
Next
     conn.Execute (sql)
 End Sub

bkts34 · 16 Şubat 2018

Sayın Zeki Gürsoy ve alicimri,
İlginizden ve desteğinizden dolayı çok teşekkür ederim, sorun çözüldü, mevcuta göre çok daha iyi sonuç veriyor.

İyi çalışmalar.

bkts34 · 19 Şubat 2018

Herkese merhaba,

Bu açtığım konuyla ilgili 10.000 satır ve 5 sütunu 1,85 saniye de ortak ağ üzerinden sql server a gönderebildiğim " bulk insert " metoduna ulaştım ve mevcut a göre çok çok faydalı bir sonuç aldığım için ihtiyacı olanlara paylaşmak isterim,

Örnek olarak hazırladığım ve daha anlaşılır olması için bazı bilgileri vereyim öncelikle,

Örnekte ki Sql de;
Server Adı : ABC
Database Adı : XYZ
User ID : Ali
Password : Veli

Excelde ki;
Sayfanın adı : VERİLER
A sütunu başlığı ve veri tipi : No ( sayısal )
B sütunu başlığı ve veri tipi : Ad ( genel )
C sütunu başlığı ve veri tipi : SoyAd ( genel )
D sütunu başlığı ve veri tipi : Yas ( sayısal )
E sütunu başlığı ve veri tipi : Tarih( Tarih )

Kod öncelikle excel i temp klasörü içinde bir csv ye dönüştürüyor daha sonra bu csv yi belirtilen bir klasör üzerine ( tüm kullancıların erişmesi için ortak ağ üzerinde bir klasör kullanılmalı ), bu örnek için C sürücüsüne kopyalayarak Sql serverda belirtilen tabloya eski veriyi silerek yenisini giriyor.

Acele ile yazdım umarım anlaşılır olmuştur..

Kod:

Sub sql_bulk_insert()
    Set s1 = ThisWorkbook.Sheets("VERİLER")
    Set fso = CreateObject("Scripting.FileSystemObject")
    TmpDosyaAdi = fso.GetSpecialFolder(2) & "\deneme.csv"
    DosyaAdi = "C:\SQL\deneme.csv" '(Bu kısım ortak sql server ın veriyi alacağı klasör )
    
        strConn = "PROVIDER=SQLOLEDB;"
        strConn = strConn & "DATA SOURCE=ABC;INITIAL CATALOG=XYZ;User ID=Ali;Password=Veli;"
  
    basla = Timer
    ErrDosyaAdi = Replace(DosyaAdi, ".csv", "-err.csv")
    ErrTxt = ErrDosyaAdi & ".Error.txt"
    If fso.FileExists(ErrDosyaAdi) Then fso.DeleteFile (ErrDosyaAdi)
    If fso.FileExists(ErrTxt) Then fso.DeleteFile (ErrTxt)
    
    Set TFile = fso.OpenTextFile(TmpDosyaAdi, 2, True)
    For i = 2 To 65536
        If Trim(s1.Cells(i, 1).Value) = "" Then Exit For
        Tarih = Format(s1.Cells(i, 5).Value, "mm/dd/yyyy")
        satirStr = ""
        satirStr = satirStr & s1.Cells(i, 1).Value & ";" 'Server üzerinde olulturulan tablonun ilk kolonu (tip = int )
        satirStr = satirStr & s1.Cells(i, 2).Value & ";" 'Server üzerinde olulturulan tablonun ikinci kolonu (tip = nvarchar(50) )
        satirStr = satirStr & s1.Cells(i, 3).Value & ";" 'Server üzerinde olulturulan tablonun ilk kolonu (tip = nvarchar(50) )
        satirStr = satirStr & s1.Cells(i, 4).Value & ";" 'Server üzerinde olulturulan tablonun ilk kolonu (tip = int )
        satirStr = satirStr & Tarih 'Server üzerinde olulturulan tablonun ilk kolonu (tip = Date )
        TFile.WriteLine satirStr
    Next

    TFile.Close
    fso.CopyFile TmpDosyaAdi, DosyaAdi ' Ortak ağa direk csv oluşturmak yavaş olduğu için Temp klasörrüne bir csv oluşturup oluşan csv _
    dosyasını ortakta bir klasöre koymak gerekli veya da server ın yetkili olduğu herhangi bir klasör olabilir ( bu örnek için C ye koyduk ama normalde ortak klasörü kullandık )

    Set cn = CreateObject("adodb.Connection")
    cn.ConnectionString = strConn
    cn.Open
    
    cn.Execute "delete from [dbo].[Dene]" 'İÇERDEKİ MEVCUT TABLOYU SİL
    
    QStr = "bulk insert [dbo].[Dene]" 'BULK İNSERT KOMUT SATIRI OLUŞTUR
    QStr = QStr & " from '" & DosyaAdi & "'"
    QStr = QStr & " with"
    QStr = QStr & " ("
    QStr = QStr & "      fieldterminator = ';',"
    QStr = QStr & "      rowterminator = '\n',"
    QStr = QStr & "      errorfile = '" & ErrDosyaAdi & "',"
    QStr = QStr & "      maxerrors = 100000"
    QStr = QStr & " )"
    cn.Execute QStr 'BULK İNSERT EXECUTE
    cn.Close
    MsgBox Timer - basla
End Sub

bkts34 · 20 Şubat 2018

Bunu yapacak kullanıcıya Sql server da "bulkadmin" yetkisi verilmeli.

Security-->Logins-->KullanıcıAdı sağ klik--> Properties--> Server Roles --> bulkadmin tikle

alicimri · 20 Şubat 2018

Sn. bkts34
Evet bayağı hızlı ve başarılı

bkts34 · 20 Şubat 2018

Aynen, umarım faydalı olur.

tamer42 · 28 Şubat 2023

alicimri' Alıntı:
Sn. Zeki Gürsoy teşekkür ederim, Benim de öteden beri kafama takılan bir soruna çözüm getirmişsiniz.
Sn. bkts34'nin sorununu 3000 kayıt ile aşağıdaki kod ile denedim 3-4 saniyede başarılı sonuç aldım.

Kod:

Sub aa() Dim conn As ADODB.Connection Dim sConnString As String sConnString = "Provider= SQLOLEDB;Data Source=" & "X;" & "Initial Catalog=" & "Y" & ";Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open (sConnString) Dim sql As String For i = 2 To Range("A65536").End(3).Row sql = sql & "insert into Z (id, name) values(" & Range("A" & i) & ", " & "'" & Range("B" & i) & "');" Next conn.Execute (sql) End Sub

Merhaba,
Ekli dosyada, SQL INSERT INTO Kullanımı ile sayfadaki verileri aktarmak istiyorduk,

for ......next döngüsü içinde Conn.Execute (strSQL) komutu sorunsuz çalışmakta;

Yalnız for ......next döngüsü içinde strSQL sorgusunu birleşitrip, en sonunda bir seferde Conn.Execute (strSQL) komutu verdiğim zaman ekli hata mesajlarını vermekte; sorunu nasıl çözebiliriz?
Yardımlarınız için şimdiden teşekkürler,

iyi Çalışmalar.

Haluk · 1 Mart 2023

Döngüyle falan uğraşmadan veriler aşağıdaki kodla alınabilir;

C#:

Sub Test()
'   Haluk - 01/03/2023
'   Ref: Microsoft ActiveX Data Objects 6.1 Library

    Dim Conn As New ADODB.Connection, RS As ADODB.Recordset
    Dim myFile As String, strSQL As String
    
    myFile = ThisWorkbook.FullName
    
    Conn.Open "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & myFile & ";Extended Properties='Excel 12.0;Hdr=No'"
    Set RS = New ADODB.Recordset

    strSQL = "Insert Into [Sayfa2$] (F1, F2, F3) Select F7, F4, F5 From [Sayfa1$A14:H]"
    
    Conn.Execute (strSQL)
    
    Conn.Close
    
    Set RS = Nothing
    Set Conn = Nothing
End Sub

.

tamer42 · 1 Mart 2023

Haluk' Alıntı:

Döngüyle falan uğraşmadan veriler aşağıdaki kodla alınabilir;

C#:

Sub Test()
'   Haluk - 01/03/2023
'   Ref: Microsoft ActiveX Data Objects 6.1 Library

    Dim Conn As New ADODB.Connection, RS As ADODB.Recordset
    Dim myFile As String, strSQL As String
   
    myFile = ThisWorkbook.FullName
   
    Conn.Open "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & myFile & ";Extended Properties='Excel 12.0;Hdr=No'"
    Set RS = New ADODB.Recordset

    strSQL = "Insert Into [Sayfa2$] (F1, F2, F3) Select F7, F4, F5 From [Sayfa1$A14:H]"
   
    Conn.Execute (strSQL)
   
    Conn.Close
   
    Set RS = Nothing
    Set Conn = Nothing
End Sub

.

çok teşekkürler Haluk Hocam, iyi ki varsınız!

tamer42 · 1 Mart 2023

tamer42' Alıntı:
çok teşekkürler Haluk Hocam, iyi ki varsınız!

Haluk Hocam kodlar excel ortamında gayet güzel ve çok hızlı
herşeyden önce emeğinize sağlık!
Excel ortamındaki verileri aynı işlemi Access veri tabanına göndermek için bağlantı satırını nasıl düzenlememiz gerekmektedir.

tekrar teşekkürler, iyi çalışmalar.

Kod:

Dim CN As New ADODB.Connection
    myPath = ThisWorkbook.Path & "\DB"
   yol = myPath & "\db2.accdb"

    With CN
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & yol
        
        .Open CN
        
    End With

 strSQL = "Insert Into [tblData] (F2, F3, F4 ) Select F2, F7, F5 From [Sayfa1$A14:G]"

 CN.Execute (strSQL)

Haluk · 1 Mart 2023

Excel'den Access'e "Insert Into" metoduyla söz konusu şekilde "Select" deyimiyle Subquery kullanarak veri aktarabilmek için; alan isimlerini yani, sütun başlıklarını kullanmak zorundasınız.

"HDR=Yes" gibi düşünün yani....

.

Haluk · 1 Mart 2023

Ama eğer işinize yararsa; Excel'deki başlıksız verileri Access dosyasında yeni bir tablo oluşturup, onun içine yazabilirsiniz. Bütün bu işlem işlem SQL'de tek bir komut satırında gerçekleşir, döngü falan da kullanılmaz.

Ama bu işin sakıncası şu olur; Access dosyasında oluşturulan tablodaki alanların başlıkları, aktarılan verilerin birinci satırı olur.

Aşağıdaki kod; "DatabasePath" değişkeninde belirtilen Access dosyasında "MyTable3" isimli bir oluşturup, Excel dosyasında "Sheet1" sayfasında A20:C30 aralığını bu tabloya yazar. Oluşturulan "MyTable3" tablosundaki alanların isimleri A20:C20 hücrelerindeki verilerdir.

C#:

Sub Test2()
'   Haluk - 01/03/2023
    Dim cn As Object
   
    DatabasePath = "C:\TestFolder\MyDB.mdb"
   
    If Dir(DatabasePath) = "" Then
        MsgBox DatabasePath & " bulunamadi, programdan cikilacak !", vbCritical, "ExcelToMDB"
        Exit Sub
    End If

    Set cn = CreateObject("ADODB.Connection")
   
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath

    cn.Execute "SELECT * INTO MyTable3 FROM [Sheet1$A20:C]" & _
               "IN '' [EXCEL 12.0;DATABASE=" & ThisWorkbook.FullName & "]"
       
    MsgBox "Datalar aktarildi ...", vbInformation, "ExcelToMDB"

    cn.Close

    Set cn = Nothing
End Sub

Uzun lafın kısası; ADO ile ister Excel, ister Access kullanın ama bu tür veri tabanı işlerinde mutlaka gereken teknikleri kullanın. Veri tabanı işi yaparken alan isimlerini kullanmamak bana hiç de pratik gelmiyor.

Yok, kesinlikle alan isimlerini kullanamam diyorsanız o zaman işi 14 No'lu mesajınızdaki gibi döngüyle halledeceksiniz.

.

tamer42 · 2 Mart 2023

Haluk' Alıntı:
Ama eğer işinize yararsa; Excel'deki başlıksız verileri Access dosyasında yeni bir tablo oluşturup, onun içine yazabilirsiniz. Bütün bu işlem işlem SQL'de tek bir komut satırında gerçekleşir, döngü falan da kullanılmaz.

Ama bu işin sakıncası şu olur; Access dosyasında oluşturulan tablodaki alanların başlıkları, aktarılan verilerin birinci satırı olur.

Aşağıdaki kod; "DatabasePath" değişkeninde belirtilen Access dosyasında "MyTable3" isimli bir oluşturup, Excel dosyasında "Sheet1" sayfasında A20:C30 aralığını bu tabloya yazar. Oluşturulan "MyTable3" tablosundaki alanların isimleri A20:C20 hücrelerindeki verilerdir.

C#:

Sub Test2() ' Haluk - 01/03/2023 Dim cn As Object DatabasePath = "C:\TestFolder\MyDB.mdb" If Dir(DatabasePath) = "" Then MsgBox DatabasePath & " bulunamadi, programdan cikilacak !", vbCritical, "ExcelToMDB" Exit Sub End If Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabasePath cn.Execute "SELECT * INTO MyTable3 FROM [Sheet1$A20:C]" & _ "IN '' [EXCEL 12.0;DATABASE=" & ThisWorkbook.FullName & "]" MsgBox "Datalar aktarildi ...", vbInformation, "ExcelToMDB" cn.Close Set cn = Nothing End Sub

Uzun lafın kısası; ADO ile ister Excel, ister Access kullanın ama bu tür veri tabanı işlerinde mutlaka gereken teknikleri kullanın. Veri tabanı işi yaparken alan isimlerini kullanmamak bana hiç de pratik gelmiyor.

Yok, kesinlikle alan isimlerini kullanamam diyorsanız o zaman işi 14 No'lu mesajınızdaki gibi döngüyle halledeceksiniz.

.

Çok teşekkürler Haluk Hocam
iyi ki varsınız, sağolun, varolun!

Excel den Sql e veri gönderim metotları

bkts34

bkts34

Zeki Gürsoy

Uzman

bkts34

bkts34

Zeki Gürsoy

Uzman

alicimri

alicimri

bkts34

bkts34

bkts34

alicimri

bkts34

tamer42

Destek Ekibi

Ekli dosyalar

Haluk

Özel Üye

tamer42

Destek Ekibi

tamer42

Destek Ekibi

Haluk

Özel Üye

Haluk

Özel Üye

tamer42

Destek Ekibi