can i import csv to mdb

the code first changes the dbf file to csv.

Sub FindFiles()
    Dim strDocPath As String
    Dim strCurrntFile As String
    Dim Fname As String

strDocPath = "Y:\Eilat\Shapes\"
'strCurrentFile = Dir(strDocPath & "*.*")
strCurrentFile = Dir(strDocPath & "111.dbf")

    Workbooks.Open FileName:=strDocPath & strCurrentFile
    Fname = Left$(strCurrentFile, Len(strCurrentFile) - 4) & ".csv"
    ActiveWorkbook.SaveAs FileName:=strDocPath & Fname, FileFormat:=xlCSVMSDOS, CreateBackup:=False
    ActiveWorkbook.Close (True)


Dim filepath As String
Dim sqlinsert As String
Dim sqlvalue As String
Dim sqlquery As String
Dim sqlwhere As String

'Set db = CurrentDb
directory = "Y:\Eilat\Shapes\"
FileName = "111.csv" 
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & directory & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
'strSQL = "SELECT * FROM " & FileName
'rs.Open strSQL, strcon
'rs.MoveFirst
Dim strTextLine As String
Dim aryMyData() As String


Open directory & FileName For Input As #1
Do While Not EOF(1)             ' Loop until end of file.
    Line Input #1, strTextLine   ' Read line into variable.
    aryMyData = Split(strTextLine, ",") 'Split text into array by comma

' (the csv length changes for example this is the columns in csv but it can be longer. EHANDLE,UseCode,UseCode2,Descriptio,Gush,Helka,Owner,OwnerID,Holder,HolderID,Floor,PhysicalNo,Date,Area,Comments,Address,StreetName,HouseNo,Telephone,Fax,Email,Manager,Business,SerialNo,MeasuredBy,Height,BlockNo,Mapkey,User1,User2,user3 is the columns in csv but it can be longer.

strSQL = "??(what sql statement need??)

(dont know how to do import to mdb "Y:\Eilat\Arnona\Eilat.mdb")

Debug.Print strSQL
DoCmd.RunSQL strSQL

Loop
Close
End Sub

1 answer

  • answered 2019-11-08 15:40 SunKnight0

    With the assumptions:

    • Your code properly opens the CSV file and reads line by line
    • All your table fields are of type String
    • Your table has fields Field_01, Field_02, Field_03 into which you want to import columns 1, 2 and 3 of the CSV file

    You can use

    DoCmd.RunSQL "INSERT INTO MyTable (Field_01, Field_02, Field_03) VALUES ('" & aryMyData(0) & "','" & aryMyData(1) & "','" & aryMyData(2) & "'")
    

    and expand as needed for all your fields and columns.

    This is a very basic example that assumes your table is specifically prepared for the CSV file you are importing. If you expect the table's design to change to accommodate whatever CSV file you are importing that is way more complicated.