Option Compare Database
Private Sub ButtonImport_Click()
Dim strTabellenname As String
Dim strDateipfad As String
strTabellenname = "Tabelle1"
strDateipfad = "C:\Users\m.hofelich\Downloads\Test.csv"
Call csvDateiInTabelleEinlesen(strTabellenname, strDateipfad)
End Sub
Public Sub csvDateiInTabelleEinlesen(ByVal Tabellenname As String, ByVal Dateipfad As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim d As Long
Dim Zeile As Variant
Dim arrWerte As Variant
Dim i As Integer
Dim j As Integer
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim fldname As String
Set db = CurrentDb
On Error Resume Next
db.TableDefs.Delete Tabellenname
On Error GoTo 0
d = FreeFile
Open Dateipfad For Input As #d
Do While Not EOF(d)
Line Input #d, Zeile
arrWerte = Split(Zeile, ";")
j = j + 1
If j = 1 Then
For i = 0 To UBound(arrWerte)
If arrWerte(i) = "" Then
fldname = "Spalte " & i + 1
Else
fldname = arrWerte(i)
fldname = Replace(fldname, Chr(10), " ")
fldname = Replace(fldname, Chr(34), "")
fldname = Replace(fldname, ".", "_")
fldname = Replace(fldname, "!", "")
End If
If i = 0 Then
Set tdf = db.CreateTableDef(Tabellenname)
Set fld = tdf.CreateField(fldname, dbText, 255)
tdf.Fields.Append fld
tdf.Fields.Refresh
db.TableDefs.Append tdf
db.TableDefs.Refresh
Else
Set fld = tdf.CreateField(fldname, dbText, 255)
tdf.Fields.Append fld
tdf.Fields.Refresh
Set fld = Nothing
End If
Next i
Set tdf = Nothing
Set rs = CurrentDb.OpenRecordset(Tabellenname, dbOpenDynaset)
Else
rs.AddNew
For i = 0 To UBound(arrWerte)
rs(i) = IIf(arrWerte(i) = "", Null, Left(arrWerte(i), 255))
Next i
rs.Update
End If
Loop
Close #d
End Sub