Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Schnelles Einlesen von großen Datenmengen
zurück: Formularfelder per SQL-Abfrage füllen weiter: if then Problem Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Antwort Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
aiciel
Neuling


Verfasst am:
08. Feb 2012, 17:43
Rufname:
Wohnort: München

Schnelles Einlesen von großen Datenmengen - Schnelles Einlesen von großen Datenmengen

Nach oben
       Version: Office 2007

Hallo,

in meiner Arbeit muß ich immer wieder große Datenmengen aus xls in Access einlesen. Dabei muß ich allerdings der Tabelle eine andere Struktur verpassen. Bisher löse ich das Problem mit folgendem VBA-Code (Manche Zeilen habe kürzen müssen):

[size=7][i][size=9]Sub database()

' definition variablen
Dim DeviceNo As Integer
Dim LineOffset As Integer
Dim ColumnOffset As Integer
Dim i As Integer
Dim j As Long
Dim xx1 As Double

' Datenbank Header
Dim sql As String
Dim sqlh As String

' values
DeviceNo = 24
RowOffset = 20
ColumnOffset = 10

' Pfad für Datei
Pfad = "c:\Users\..."
Datei = "....xlsx"

sqlh = "INSERT INTO "
sqlh = sqlh & "Table3" & " "

sqlh = sqlh & "("
sqlh = sqlh & "dc" & ","
' sqlh = sqlh & "testtemp" & ","
' sqlh = sqlh & "lotnr" & ","
' sqlh = sqlh & "wafernr" & ","
sqlh = sqlh & "chipnr" & ","
' sqlh = sqlh & "chipx" & ","
' sqlh = sqlh & "chipy" & ","
sqlh = sqlh & "testnr" & ","
' sqlh = sqlh & "Testtype" & ","
' sqlh = sqlh & "blwt" & ","
' sqlh = sqlh & "vddc" & ","
' sqlh = sqlh & "padnr" & ","
' sqlh = sqlh & "grp" & ","
sqlh = sqlh & "xx1"
sqlh = sqlh & ") "

' Excel öffnen

Set xlAppl = CreateObject("Excel.Application")
Set xlWB = xlAppl.Workbooks.Open(Pfad & Datei, , , , "")
xlAppl.Worksheets("Input1").Activate

' einlesen und abspeichern der Daten
For i = 1 To DeviceNo

cl1 = i + ColumnOffset

' lotnr = xlAppl.Cells(3, cl1)
' lotnr = Mid$(lotnr, 2, Len(lotnr) - 2)
' wafernr = xlAppl.Cells(5, cl1)
chipnr = xlAppl.Cells(2, cl1)
' chipx = xlAppl.Cells(11, cl1)
' chipy = xlAppl.Cells(12, cl1)
' testtemp = xlAppl.Cells(18, cl1)
dc = DateCode(Datei)

For j = 1 To 200000
' dc =
testnr = xlAppl.Cells(j + RowOffset, 4).Value
xx1 = xlAppl.Cells(j + RowOffset, cl1).Value
' xx1 = XlAppl.Cells(rw1, cl1)

' Datensatz anfügen

sql = sqlh 'header definiert oben

sql = sql & "VALUES ("
sql = sql & "'" & dc & "',"
' sql = sql & "'" & testtemp & "',"
' sql = sql & "'" & lotnr & "',"
' sql = sql & "'" & wafernr & "',"
sql = sql & "'" & chipnr & "',"
' sql = sql & "'" & chipx & "',"
' sql = sql & "'" & chipy & "',"
sql = sql & "'" & testnr & "',"
' sql = sql & "'" & Testtype & "',"
' sql = sql & "'" & blwt & "',"
' sql = sql & "'" & vddc & "',"
' sql = sql & "'" & padnr & "',"
' sql = sql & "'" & grp & "',"
sql = sql & "'" & xx1 & "'"
sql = sql & ");"

'Debug.Print sql

Set Db = CurrentDb()
Db.Execute sql

Debug.Print "CHIP FERTIG!"
Next j

Next i

'Excel schliessen

Set xlWS = Nothing
Set xlWB = Nothing
xlAppl.Application.Quit
Set xlAppl = Nothing
'Close #1

' xls schließen

End Sub

Function DateCode(Datei)

DateCode = Left$(Datei, 14)

End Function[/size][/i][/size]


Mich interessiert generell, wie ich große Datenmengen über VBA oder auch über andere Lösungen, schneller einlesen kann.

Gruß Aiciel
KlausMz
Moderator Access


Verfasst am:
08. Feb 2012, 19:08
Rufname:
Wohnort: Irgendwo in der Pfalz


AW: Schnelles Einlesen von großen Datenmengen - AW: Schnelles Einlesen von großen Datenmengen

Nach oben
       Version: Office 2007

Hallo,
ich könnte mir denken, dass es schneller wäre die Exceltabelle zu verknüpfen und dann mit Anfüge/Aktualisierungsabfragen die Daten in die richtigen Tabellen anfügen/aktualisieren. Die Abfragen sollten dann aber als gespeicherte Abfragen vorliegen.
VBA würde man dann nur zum Start der Abfragen brauchen.

_________________
Gruß
Klaus . . . . . Feedback wäre wünschenswert.
Ich möchte bitte keine unaufgeforderten PN. Fragen bitte im Forum.
Fritzerich
Gast


Verfasst am:
08. Feb 2012, 22:11
Rufname:

AW: Schnelles Einlesen von großen Datenmengen - AW: Schnelles Einlesen von großen Datenmengen

Nach oben
       Version: Office 2007

Hallo Aiciel!

Grundsätzlich mache ich das (seit Jahren) auch so wie Du.
Der einzige Grund ist allerdings, dass ich schon beim Einlesen der Daten Strukturen und Inhalte prüfen muss. Sonst würde ich wohl auch eher, wie Klaus vorschlägt, mit verknüpften (oder vielleicht sogar importierten) Excel-Tabellen arbeiten.

Zu Deinem Code
1) Hast Du wirklich immer genau 200000 Datenzeilen im Excel?
Die letzte tatsächlich verwendete Zeile kannst Du z. B. ermitteln mit
Code:
LastUsedRow = xlAppl.Worksheets("Input1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

2) Ein wenig Performance kannst Du sicher gewinnen, wenn Du Berechnungen und Zuweisungen aus den For-Schleifen raus bekommst
z.B.:
Code:
Set Db = CurrentDb()
gehört auf jeden Fall noch vor die 1. For-Schleife
Die Zuweisung wird sonst immerhin 24*200.000=4.800.000 mal durchgeführt.
Code:
For j = 1 To 200000
' dc =
  testnr = xlAppl.Cells(j + RowOffset, 4).Value
  xx1 = xlAppl.Cells(j + RowOffset, cl1).Value
  ...
würde ich durch
Code:
For j = 1+RowOffset To 200000+RowOffset
' dc =
  testnr = xlAppl.Cells(j, 4).Value
  xx1 = xlAppl.Cells(j, cl1).Value
  ...
ersetzen.
(immerhin nur 48 Additionen statt 9.600.000)
3) Performance habe ich seinerzeit auch gewonnen, indem ich den ganzen Excel-Zellbereich zuerst in ein Variant-Array geladen habe und dann nur mehr aus diesem Array ausgelesen habe.
z.B.:
Code:
Dim vaRange As Variant 
  vaRange = xlAppl.Worksheets("Input1").UsedRange.Value 'vRange hat nun die Struktur Array(ExcelZeile, ExcelSpalte) = Zellwert

Zusammen sieht Dein Code bei mir in etwa so aus
Code:
Sub database()

' definition variablen
Dim DeviceNo As Integer
Dim LineOffset As Integer
Dim ColumnOffset As Integer
Dim i As Integer
Dim j As Long
Dim xx1 As Double

'Excel
Dim xlApp As Excel.Application
Dim xlWB  As Excel.Workbook
Dim vaRange As Variant

' Datenbank Header
Dim sql As String
Dim sqlh As String

' values
DeviceNo = 24
RowOffset = 20
ColumnOffset = 10

' Pfad für Datei
Pfad = "c:\Users\..."
Datei = "....xlsx"

sqlh = "INSERT INTO "
sqlh = sqlh & "Table3" & " "
sqlh = sqlh & "("
sqlh = sqlh & "dc" & ","
' sqlh = sqlh & "testtemp" & ","
' sqlh = sqlh & "lotnr" & ","
' sqlh = sqlh & "wafernr" & ","
sqlh = sqlh & "chipnr" & ","
' sqlh = sqlh & "chipx" & ","
' sqlh = sqlh & "chipy" & ","
sqlh = sqlh & "testnr" & ","
' sqlh = sqlh & "Testtype" & ","
' sqlh = sqlh & "blwt" & ","
' sqlh = sqlh & "vddc" & ","
' sqlh = sqlh & "padnr" & ","
' sqlh = sqlh & "grp" & ","
sqlh = sqlh & "xx1"
sqlh = sqlh & ") "

' Excel öffnen
Set xlAppl = CreateObject("Excel.Application")
Set xlWB = xlAppl.Workbooks.Open(Pfad & Datei, , , , "")

'Daten in Array laden
vaRange = xlAppl.Worksheets("Input1").UsedRange.Value 'vRange hat nun die Struktur Array(ExcelZeile, ExcelSpalte) = Zellwert

'Excel schliessen
Set xlWS = Nothing
Set xlWB = Nothing
xlAppl.Application.Quit
Set xlAppl = Nothing

dc = DateCode(Datei)
Set Db = CurrentDb()

' einlesen und abspeichern der Daten
For cl1 = 1 + ColumnOffset To DeviceNo + ColumnOffset
  ' lotnr = vaRange(3, cl1)
  ' lotnr = Mid$(lotnr, 2, Len(lotnr) - 2)
  ' wafernr = vaRange(5, cl1)
  chipnr = vaRange(2, cl1)
  ' chipx = vaRange(11, cl1)
  ' chipy = vaRange(12, cl1)
  ' testtemp = vaRange(18, cl1)

  For j = 1 + RowOffset To ubound(vaRange, 1)  'Hier bis zur letzten Zeile statt 200000
    ' dc =
    testnr = vaRange(j, 4)
    xx1 = vaRange(j, cl1)
    ' xx1 = vaRange(rw1, cl1)

    ' Datensatz anfügen

    sql = sqlh 'header definiert oben

    sql = sql & "VALUES ("
    sql = sql & "'" & dc & "',"
    ' sql = sql & "'" & testtemp & "',"
    ' sql = sql & "'" & lotnr & "',"
    ' sql = sql & "'" & wafernr & "',"
    sql = sql & "'" & chipnr & "',"
    ' sql = sql & "'" & chipx & "',"
    ' sql = sql & "'" & chipy & "',"
    sql = sql & "'" & testnr & "',"
    ' sql = sql & "'" & Testtype & "',"
    ' sql = sql & "'" & blwt & "',"
    ' sql = sql & "'" & vddc & "',"
    ' sql = sql & "'" & padnr & "',"
    ' sql = sql & "'" & grp & "',"
    sql = sql & "'" & xx1 & "'"
    sql = sql & ");"

    'Debug.Print sql
    Db.Execute sql

    Debug.Print "CHIP FERTIG!"
  Next j
Next cl1
End Sub

Function DateCode(Datei)
  DateCode = Left$(Datei, 14)
End Function


Da die meiste Performance wahrscheinlich beim Datenbank-Insert verloren geht, wird das jetzt zwar nicht die große Verbesserung bringen, aber vergleich's mal.

Hoffentlich was Brauchbares dabei,
Fritzerich

PS: Ein Vergleichsergebnis würde mich auch interessieren
Marsupilami72
Office-VBA-Programmierer


Verfasst am:
09. Feb 2012, 11:00
Rufname:
Wohnort: Goslar


AW: Schnelles Einlesen von großen Datenmengen - AW: Schnelles Einlesen von großen Datenmengen

Nach oben
       Version: Office 2007

Also nach meiner Erfahrung ist der Zugriff auf einzelne Zellen eines Excel-Sheets mit dem Cells Objekt die größte Bremse - wenn man sich wie beschrieben die ganzen Daten vorher am Stück in ein Array holt, dürfte das schon einiges bringen.
_________________
Gruß,
Martin

Bitte keine Fragen per PN - dafür ist das Forum da!
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Access Formulare: Exp. txt File einlesen und Formular generieren 9 Chillion 202 27. Dez 2012, 19:23
steffen0815 Exp. txt File einlesen und Formular generieren
Keine neuen Beiträge Access Tabellen & Abfragen: Einlesen von Dateinamen mit Dateigröße in ACCESS-Tabelle 2 JH58 109 17. Dez 2012, 13:45
Gast Einlesen von Dateinamen mit Dateigröße in ACCESS-Tabelle
Keine neuen Beiträge Access Formulare: Globale Variable einlesen 9 FazzBumm 291 05. Okt 2012, 14:07
FazzBumm Globale Variable einlesen
Dieses Thema ist gesperrt, du kannst keine Beiträge editieren oder beantworten. Access Formulare: Daten aus Tabelle einlesen 18 obscho 412 21. Feb 2012, 13:39
obscho Daten aus Tabelle einlesen
Keine neuen Beiträge Access Tabellen & Abfragen: Transferspreadsheet bestimmte Register einlesen 12 shorty111 496 10. Feb 2012, 21:39
MissPh! Transferspreadsheet bestimmte Register einlesen
Keine neuen Beiträge Access Tabellen & Abfragen: Dateien einlesen 1 Jin Jin 200 19. Mai 2011, 12:19
lothi Dateien einlesen
Keine neuen Beiträge Access Tabellen & Abfragen: Mehrere Tabellen in eine Mastertabelle einlesen 1 Gast 599 04. Apr 2010, 09:31
kyron9000 Mehrere Tabellen in eine Mastertabelle einlesen
Keine neuen Beiträge Access Formulare: Gefilterte Daten in Combox einlesen 1 Gast 181 04. Dez 2009, 17:26
MiLie Gefilterte Daten in Combox einlesen
Keine neuen Beiträge Access Tabellen & Abfragen: Fehlerhaftes einlesen von Dateien 0 peterchenpan 294 22. Sep 2008, 00:31
peterchenpan Fehlerhaftes einlesen von Dateien
Keine neuen Beiträge Access Formulare: Springen in großen Schritten durch Datensätze 4 jape 393 21. Feb 2008, 23:26
jape Springen in großen Schritten durch Datensätze
Keine neuen Beiträge Access Tabellen & Abfragen: Einlesen von ASCII (TXT) Dateien schlägt manchmal fehl??? 6 blicki 1515 12. Sep 2007, 16:33
Nouba Einlesen von ASCII (TXT) Dateien schlägt manchmal fehl???
Keine neuen Beiträge Access Formulare: Datei Pfad einlesen, Datei mit passendem Programm öffnen 3 student88 3337 12. Aug 2007, 01:01
Willi Wipp Datei Pfad einlesen, Datei mit passendem Programm öffnen
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Access Tabellen