AccessTr.neT
Excelden Veri Alma Ve Kaydı Güncelleme - Baskı Önizleme

+- AccessTr.neT (https://accesstr.net)
+-- Forum: Microsoft Access (https://accesstr.net/forum-microsoft-access.html)
+--- Forum: Access Cevaplanmış Soruları (https://accesstr.net/forum-access-cevaplanmis-sorulari.html)
+--- Konu Başlığı: Excelden Veri Alma Ve Kaydı Güncelleme (/konu-excelden-veri-alma-ve-kaydi-guncelleme.html)

Sayfalar: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19


Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - feraz - 07/02/2020

(07/02/2020, 07:33)accessman yazdı: Beynimi yakacaksın bir gün
Anladığımı zannedip yapmaya çalışıyorum ama sana yetişemiyorum
Img-grin
 Evet abey @berduş hocamız gerçekten çok zeki birisi maşAllah.Biz güncelleme,veri ekle deyince klasik yöntemleri kullanıyoruz hocamız klasını konuşturmuş.Yinede ben bu şekilde kullanmazdım tercihen fakat toplu aktarmada kullanırdım.

Alttaki gibi düşünmek bile marifet.Yani in ile ile tablodaki ve exceldeki aynı olanları buldurup sildirmek herkes düşünemez Img-grin
Özetle berduş hocamız şöyle yapmış.
Excele koodaki yöntemle bağlantı kurup ayrı bir tabloya aldırmış ve Tablo1 deki verileride Excel ile tablo1 de aynı olmayanları karşılaştırıp aynı olanları silmiş.Sonrada bağlantı ie oluşan tablodaki verileri insert into ile tablo1 e aktarıp geçici oluşan tabloyuda silivermiş.insert into nun bu şekilde topluca aktarmasınıda öğrenmiş oldukImg-grin
Gerçi ben önceden kullanmazdım into olayını.
Berduş hocamızın sadece kullandığı yöntemde hoşuma gitmeyen olay mükerrer kayıtlar olunca onlarıda siliyor çünkü Excel ile karşılaşmasında aynı veriler olduğu için onlarıda siliyor.Yani 10 tane a1 varsa 10unuda güncellemesi gerekti.

where [KOD] in (select [KOD] from TmpTablo)


Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - berduş - 07/02/2020

Aşağıdaki resimde test sonuçları var ne kadar güvenilir emin değilim kendiniz karar verirsiniz ama bana süreyi daha kısa gösteriyor gibi geldi
Testin nasıl yapıldığını anlatayım
ekleme kodları dışında her şeyiyle özdeş 4 çalışma var
testin ilk kısmında boş tablolara excelden 1500 kayıt ekleniyor
(bu 1500 kaydın 750 si asıl exceldeki -3000 kayıtlık - kayıtlarla birebir aynıyken
750 tanesinde sadece 1 alana fazladan a harfi eklenmiş)
2 kısımda ise bu sefer 3000 kayıtlı asıl dosyadan veri almaya çalışıyoruz
yani 2. testte hem ekleme hem de güncelleme yapıyoruz
Transfer Yöntemleri
1. Yöntemde normal ilk kullaılan kod var
    Do While Not rs.EOF And Not rs.BOF 'Tablo güncelleme icin(Exceldeki recordseti icine alinan veriler icin döngü)
        If DCount("[kod]", "tesisler", "[kod] = '" & rs(0) & "'") > 0 Then 'Eger tesisler deki kod sütununda excelden alinan recordset icindeki veri mevcutsa

            CurrentDb.Execute _
            "UPDATE tesisler SET [kod] = """ & rs(0) & """," & _
                              "[Kaynak] = """ & rs(1) & """," & _
                              "[Tarih] = '" & rs(2) & "'," & _
                              "[tesis] = """ & rs(3) & """," & _
                              "[bolum] = """ & rs(4) & """," & _
                              "[Tespit_eden] = """ & rs(5) & """," & _
                              "[gozlem] = """ & rs(6) & """," & _
                              "[oneriler] = """ & rs(7) & """," & _
                              "[sorumlu] = """ & rs(8) & """," & _
                              "[termin_tarihi] = '" & rs(9) & "'," & _
                              "[sorumlu_gorusu] = """ & rs(10) & """," & _
                              "[tamamlama_tarihi] = '" & rs(11) & "'," & _
                              "[durum] = '" & rs(12) & "'" & _
                              "WHERE [kod] = """ & rs(0) & """"
             
                  

            say = say + 1

        ElseIf DCount("[kod]", "tesisler", "[kod] = """ & rs(0) & """") = 0 Then 'Eger tesisler deki kod sütununda excelden alinan recordset icindeki veri mevcut degilse

            CurrentDb.Execute _
                        "INSERT INTO tesisler" _
                            & " ([kod], [kaynak], [tarih], [tesis], [bolum],[Tespit_eden],[gozlem],[oneriler],[sorumlu],[termin_tarihi],[sorumlu_gorusu],[tamamlama_tarihi],[durum])" _
                            & " VALUES ( """ & rs(0) & """ , """ & rs(1) & """ , '" & Format(rs(2), "dd.mm.yyyy") & "', """ & rs(3) & """, """ & rs(4) & """, """ & rs(5) & """, """ & rs(6) & """, """ & rs(7) & """, """ & rs(8) & """, '" & Format(rs(9), "dd.mm.yyyy") & "', """ & rs(10) & """, '" & Format(rs(11), "dd.mm.yyyy") & "', """ & rs(12) & """ );" 'Kayit ekle exceldekinde olup accesste mevcut olmayan veri

            say1 = say1 + 1
        End If

        rs.MoveNext
    Loop
2. Yöntemde
    Do While Not rs.EOF And Not rs.BOF 'Tablo güncelleme icin(Exceldeki recordseti icine alinan veriler icin döngü)
       SrgK = " select * from tesisler where [kod]='" & Replace(rs(0), "'", "''") & "'"
       rsK.Open SrgK, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
       If rsK.RecordCount = 0 Then 'rsK.MoveFirst
            rsK.AddNew
            rsK(1) = rs(0)
            rsK(2) = rs(1)
            rsK(3) = rs(2)
            rsK(4) = rs(3)
            rsK(5) = rs(4)
            rsK(6) = rs(5)
            rsK(7) = rs(6)
            rsK(8) = rs(7)
            rsK(9) = rs(8)
            rsK(10) = rs(9)
            rsK(11) = rs(10)
            rsK(12) = rs(11)
            rsK(13) = rs(12)
            rsK.Update
            say1 = say1 + 1
       Else
'            rsK.EditMode
'            rsK(1) = rs(0)
            rsK(2) = rs(1)
            rsK(3) = rs(2)
            rsK(4) = rs(3)
            rsK(5) = rs(4)
            rsK(6) = rs(5)
            rsK(7) = rs(6)
            rsK(8) = rs(7)
            rsK(9) = rs(8)
            rsK(10) = rs(9)
            rsK(11) = rs(10)
            rsK(12) = rs(11)
            rsK(13) = rs(12)
            rsK.Update
            say = say + 1
       End If
       rsK.Close
       rs.MoveNext
    Loop
3. ve 4. yöntem temelde aynı 3. yöntemde ortak kayıtlar silini exceldekiler tekrar alınırken
4.de ortaklardan farklı olan güncellenip tabloda olmayanlar ekleniyor
3. Yöntem
BasZmn = Now
'hy_Geçici_tablo_var_mı_varsa_sil_____
   If Not IsNull(DLookup("Name", "MSysObjects", "Name='TmpTablo'")) Then DoCmd.DeleteObject acTable, "TmpTablo"
'hy_tablo_kontrol____________________Bitti
TestBil = ""

DoCmd.TransferSpreadsheet TransferType:=acLink, _
                          TableName:="TmpTablo", _
                          SpreadsheetType:=10, _
                          FileName:=yahya, _
                          HasfieldNames:=True, _
                          Range:="Sayfa1$B2:N" '"Hy bin 2!B3:E"

'hy Tablo Boş Mu_____________
Dim SayRS As New ADODB.Recordset
Dim SaySql As String

SaySql = "select * from TmpTablo" 'öğretmenler tablosundan öğretmen seç

    SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    KytSay = SayRS(0)
     Krt = " where [" & SayRS(0).Name & "] Is Not Null"
    SayRS.Close
    SayRS.Open SaySql & Krt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If SayRS.RecordCount = 0 Then
        DoCmd.DeleteObject acTable, "TmpTablo"
        MsgBox "Tabloda veri yok"
        Exit Sub
    End If
    SayRS.Close

'hy Excel Boş Mu_____________Bitti
KytSy1 = DCount("*", "TmpTablo")

CurrentDb.Execute " delete from tesisler where [KOD] in (select [KOD] from TmpTablo)"
KytSy1Gncl = KytSy1 - DCount("*", "tesisler")


CurrentDb.Execute " INSERT INTO tesisler " & _
                 " ( [kod], [kaynak], [tarih], [tesis], [bolum],[Tespit_eden],[gozlem],[oneriler],[sorumlu],[termin_tarihi],[sorumlu_gorusu],[tamamlama_tarihi],[durum] ) " & _
                 " SELECT [KOD],[Kaynak],[Tarih],[Tesis],[Yer/Bölüm],[Tespit Yapan],[Uygunsuzluk/Ramak Kala/Gözlem],[Önerilen Aksiyon],[sorumlu],[Termin tarihi],[Sorumlu Görüşü/Kararı],[Tamamlama Tarihi],[Durum] " & _
                 " FROM TmpTablo where [KOD] Is Not Null"

KytSyEk = DCount("*", "tesisler") - KytSy1

Me.tesisler.Requery

    CurrentDb.TableDefs.Refresh
4. Yöntem
BasZmn = Now
'hy_Geçici_tablo_var_mı_varsa_sil_____
  If Not IsNull(DLookup("Name", "MSysObjects", "Name='TmpTablo'")) Then DoCmd.DeleteObject acTable, "TmpTablo"
'hy_tablo_kontrol____________________Bitti
TestBil = ""

DoCmd.TransferSpreadsheet TransferType:=acLink, _
                          TableName:="TmpTablo", _
                          SpreadsheetType:=10, _
                          FileName:=yahya, _
                          HasfieldNames:=True, _
                          Range:="Sayfa1$B2:N" '"Hy bin 2!B3:E"

'hy Tablo Boş Mu_____________
Dim SayRS As New ADODB.Recordset
Dim SaySql As String

SaySql = "select * from TmpTablo"

    SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    KytSay = SayRS(0)
    Krt = " where [" & SayRS(0).Name & "] Is Not Null"
    SayRS.Close
    SayRS.Open SaySql & Krt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If SayRS.RecordCount = 0 Then
        DoCmd.DeleteObject acTable, "TmpTablo"
        MsgBox "Tabloda veri yok"
        Exit Sub
    End If
    SayRS.Close

'hy Excel Boş Mu_____________Bitti
SaySql = " SELECT Count([TmpTablo]![KOD]) AS GnclSay " & _
        " FROM TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod " & _
        " WHERE ((([tesisler]![kaynak] & [tesisler]![tarih] & [tesisler]![tesis] & [tesisler]![bolum] &  " & _
        " [tesisler]![tespit_eden] & [tesisler]![gozlem] & [tesisler]![oneriler] & [tesisler]![sorumlu] &  " & _
        " [tesisler]![termin_tarihi] & [tesisler]![sorumlu_gorusu] & [tesisler]![tamamlama_tarihi] &  " & _
        " [tesisler]![durum])<>([TmpTablo]![Kaynak] & [TmpTablo]![Tarih] & [TmpTablo]![Tesis] &  " & _
        " [TmpTablo]![Yer/Bölüm] & [TmpTablo]![Tespit Yapan] & [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem] &  " & _
        " [TmpTablo]![Önerilen Aksiyon] & [TmpTablo]![Sorumlu] & [TmpTablo]![Termin Tarihi] &  " & _
        " [TmpTablo]![Sorumlu Görüşü/Kararı] & [TmpTablo]![Tamamlama Tarihi] & [TmpTablo]![Durum])));"


    SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    GuncellenecekKyt = SayRS(0)
    SayRS.Close


SqlGuncelle = " UPDATE TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod  " & _
              " SET " & _
              " tesisler.kaynak = [TmpTablo]![Kaynak],  " & _
              " tesisler.Tarih = [TmpTablo]![Tarih],  " & _
              " tesisler.tesis = [TmpTablo]![Tesis],  " & _
              " tesisler.bolum = [TmpTablo]![Yer/Bölüm],  " & _
              " tesisler.tespit_eden = [TmpTablo]![Tespit Yapan],  " & _
              " tesisler.gozlem = [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem],  " & _
              " tesisler.oneriler = [TmpTablo]![Önerilen Aksiyon],  " & _
              " tesisler.sorumlu = [TmpTablo]![sorumlu],  " & _
              " tesisler.termin_tarihi = [TmpTablo]![Termin tarihi],  " & _
              " tesisler.sorumlu_gorusu = [TmpTablo]![Sorumlu Görüşü/Kararı],  " & _
              " tesisler.tamamlama_tarihi = [TmpTablo]![Tamamlama Tarihi],  " & _
              " tesisler.durum = [TmpTablo]![Durum] " & _
              " WHERE (((TmpTablo.KOD) In (SELECT tesisler.kod " & _
              " FROM TmpTablo INNER JOIN tesisler ON TmpTablo.KOD = tesisler.kod " & _
              " WHERE ((([tesisler]![kaynak] & [tesisler]![tarih] & [tesisler]![tesis] & [tesisler]![bolum] &  " & _
              " [tesisler]![tespit_eden] & [tesisler]![gozlem] & [tesisler]![oneriler] & [tesisler]![sorumlu] &  " & _
              " [tesisler]![termin_tarihi] & [tesisler]![sorumlu_gorusu] & [tesisler]![tamamlama_tarihi] &  " & _
              " [tesisler]![durum])<> " & _
              " ([TmpTablo]![Kaynak] & [TmpTablo]![Tarih] & [TmpTablo]![Tesis] & [TmpTablo]![Yer/Bölüm] &  " & _
              " [TmpTablo]![Tespit Yapan] & [TmpTablo]![Uygunsuzluk/Ramak Kala/Gözlem] &  " & _
              " [TmpTablo]![Önerilen Aksiyon] & [TmpTablo]![Sorumlu] & [TmpTablo]![Termin Tarihi] &  " & _
              " [TmpTablo]![Sorumlu Görüşü/Kararı] & [TmpTablo]![Tamamlama Tarihi] & [TmpTablo]![Durum]))) )));"
CurrentDb.Execute SqlGuncelle

SaySql = " SELECT Count(TmpTablo.KOD) AS SayKOD " & _
        " FROM tesisler RIGHT JOIN TmpTablo ON tesisler.kod = TmpTablo.KOD " & _
        " GROUP BY tesisler.kod " & _
        " HAVING (((Count(TmpTablo.KOD)) Is Not Null) AND ((tesisler.kod) Is Null))"


    SayRS.Open SaySql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    EklenecekKyt = SayRS(0)
    SayRS.Close

SqlEkle = " INSERT INTO tesisler ( kod, kaynak, tarih, tesis, bolum, Tespit_eden, gozlem, oneriler, sorumlu, termin_tarihi, sorumlu_gorusu, tamamlama_tarihi, durum )" & _
          " SELECT TmpTablo.KOD, TmpTablo.Kaynak, TmpTablo.Tarih, TmpTablo.Tesis, TmpTablo.[Yer/Bölüm], TmpTablo.[Tespit Yapan], TmpTablo.[Uygunsuzluk/Ramak Kala/Gözlem], TmpTablo.[Önerilen Aksiyon], TmpTablo.sorumlu, TmpTablo.[Termin tarihi], TmpTablo.[Sorumlu Görüşü/Kararı], TmpTablo.[Tamamlama Tarihi], TmpTablo.Durum" & _
          " FROM tesisler RIGHT JOIN TmpTablo ON tesisler.kod = TmpTablo.KOD" & _
          " WHERE (((TmpTablo.KOD) Is Not Null) AND ((tesisler.kod) Is Null))"

CurrentDb.Execute SqlEkle

Me.tesisler.Requery

    CurrentDb.TableDefs.Refresh
    'DoCmd.DeleteObject acTable, "TmpTablo"
BitZmn = Now

[Resim: do.php?img=9542]
.rar TransferKarsilastirma_hy.rar (Dosya Boyutu: 607,83 KB | İndirme Sayısı: 12)



Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - adnan - 07/02/2020

Merhaba,
Sn.beduş hocam elinize sağlık çok güzel örnek çok emek vermişiniz teşekkür ederim.
Bir şey kafama takıldı 5 tablolu  Access dosyasında tabloların hepsi birbiriyle ilişkili Access tablosuna veri çekilebilirim ilişkiler sorun çıkarırmı?
Saygılarımla


Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - berduş - 07/02/2020

ilişkileri görmeden bir şey diyemem isterseniz yeni konu açarak dosyanızı paylaşın çözmeye çalışalım


Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - accessman - 02/03/2020

(06/02/2020, 14:04)berduş yazdı: TransferSpreadsheet yöntemiyle ilgili genel bilgiler dilerim işinize yarar 
DoCmd.TransferSpreadsheet acLink, "Dosya türü excel/versiyonu", "hangi isimle bağlanacak", "tamAdresveAd", "Başlık var mı", "Hangi aralık"
DoCmd.TransferSpreadsheet _
                          TransferType:=acLink, _
                          TableName:="TmpTablo", _
                          SpreadsheetType:=10, _
                          FileName:=yahya, _
                          HasfieldNames:=True, _
                          Range:="B3:E" '"Hy bin 2!B3:E"

TransferType:= transfer türü
                           İsim   - Sayısal değer - Tanım 
                           acImport      0       Varsayılan değerdir, içeri aktatrır
                           acExport      1       Başka dosyaya gönderir
                           acLink        2       Başka dosyadaki veriye bağlantı sağlar

TableName:="Hangi İsimle bağlanacak-acLink İse/hangi tabloya aktarılacak"

SpreadsheetType:= tablo türü
                           İsim                         Sayısal Değer    Tanım
                           acSpreadsheetTypeExcel3   0  Microsoft Excel 3.0 format
                           acSpreadsheetTypeExcel4   6   Microsoft Excel 4.0 format
                           acSpreadsheetTypeExcel5   5  Microsoft Excel 5.0 format
                           acSpreadsheetTypeExcel7   5   Microsoft Excel 95 format
                           acSpreadsheetTypeExcel8   8  Microsoft Excel 97 format
                           acSpreadsheetTypeExcel9   8  Microsoft Excel 2000 format
                           acSpreadsheetTypeExcel12  9  Microsoft Excel 2010 format
                           acSpreadsheetTypeExcel12Xml 10  Microsoft Excel 2010/2013/2016 XML format (.xlsx, .xlsm, .xlsb)

FileName:=Dosya yaolu ve adı "d:\deneme\erzak.xlsm" gibi

HasfieldNames:=Satır başlığı var mı True(var)/False(yok)

Range:=veri alınacak bölge "Hy bin 2!B3:E" --> "Hy bin 2" sayfasındaki "B3" ve "E" stunları arasındaki verileri al
teşekkürler


Cvp: Excelden Veri Alma Ve Kaydı Güncelleme - accessman - 02/03/2020

Baştan sona tekrar gezdim bir çok problem ve hepsinin çözümü yazılmış kafam karıştı
son hali için hangisini indireceğim bilemedim yapılmak istenen galiba şuydu 

excel tablosu alınırken tablo var mı bakacak 
eğer tablo var ve aynı alanlar var ise seçilen kritere göre aynı olan kayıtlar yenilenecek
eğer excelde var ama tabloda yoksa eklenecek
eğer excelde yok ama tabloda varsa tablodaki kayıt pasife alınacak veya silineneler tablosuna taşınacak 

bunları yapan eklenen hangi örnekti