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
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.