how to get data from csv to mdb file

crystalsoft,Please have a look at my answer at https://www.experts-exchange.com/questions/24566385/Import-multiple-Excel-files-into-Access.html?anchorAnswerId=24842897All#a24842897All you would need to do there is change the ".xls" search term to ".csv", and use DoCmd.TransferText instead of DoCmd.TransferSpreadsheet.Patrick

Thanx for reply

I am getting "Compile Erro"

"Variable Not Defined"

and highlighted on "acImport"

        DoCmd.TransferText TransferType:=acImport, _

That code was written to be run from Access.  Were you perhaps trying to run it from VB6?

yeh
i am trying to run it from vb6

Import a Delimited Text File into a Database
http://www.freevbcode.com/ShowCode.Asp?ID=612

Get all the files on your disk
Private Sub ListFiles(strPath As String, Optional Extention As String)
'Leave Extention blank for all files
    Dim File As String
   
    If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
   
    If Trim$(Extention) = "" Then
        Extention = "*.*"
    ElseIf Left$(Extention, 2) <> "*." Then
        Extention = "*." & Extention
    End If
   
    File = Dir$(strPath & Extention)
    Do While Len(File)
        ListView1.ListItems.Add , , File
        File = Dir$
    Loop
End Sub

Private Sub ListFiles(strPath As String)
'Leave Extention blank for all files
    Dim File As String
    
    If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    Extention = "*.csv"
  
    File = Dir$(strPath & Extention)
    Do While Len(File)
        -- CALL HERE THE FUNCTION TO IMPORT INTO YOUR DATABASE
        File = Dir$
    Loop
End Sub

Select allOpen in new window

are all csv files should be imported to the same table?

thanks for reply
yeh all csv files should be imprted to the same table

this is it, but you gonna need to tweak the script for your needs:

MDB_FILE -> the .mdb file path
TABLE_NAME -> the table name
TABLE_COLUMNS -> the table's columns separated by comma
CSV_SEARCH_PATH -> the directory (full path) where the .csv files are located

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1
const MDB_FILE = "c:\scripts\test.mdb"
const TABLE_NAME = "Employees"
const CSV_SEARCH_PATH = "c:\temp\csv"
const TABLE_COLUMNS = "EmployeeID,EmployeeName,Department"

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = " & MDB_FILE 

objRecordSet.Open "SELECT * FROM " & TABLE_NAME, _
    objConnection, adOpenStatic, adLockOptimistic

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 
drive = Split(CSV_SEARCH_PATH, "\")(0)
path = "\\" & Replace(Split(CSV_SEARCH_PATH, drive + "\")(1), "\","\\") & "\\"

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")
For Each ofile in colFiles 
	set objFile = fso.OpenTextFile(ofile.Name, ForReading)
	for each line in Split(objFile.ReadAll, vbNewLine)
		objRecordSet.AddNew
		columns = Split(TABLE_COLUMNS,",")
		props = Split(line,",")
		for i=0 to UBound(columns)
			objRecordSet(columns(i)) = props(i)
			objRecordSet.Update
		next
	next
Next


objRecordSet.Close
objConnection.Close

Select allOpen in new window

made a slight mistake, here's the updated script:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1
const MDB_FILE = "c:\scripts\test.mdb"
const TABLE_NAME = "Employees"
const TABLE_COLUMNS = "EmployeeID,EmployeeName,Department"
const CSV_SEARCH_PATH = "c:\temp\csv"

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = " & MDB_FILE 

objRecordSet.Open "SELECT * FROM " & TABLE_NAME, _
    objConnection, adOpenStatic, adLockOptimistic

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 
drive = Split(CSV_SEARCH_PATH, "\")(0)
path = "\\" & Replace(Split(CSV_SEARCH_PATH, drive + "\")(1), "\","\\") & "\\"

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")
For Each ofile in colFiles 
	set objFile = fso.OpenTextFile(ofile.Name, ForReading)
	for each line in Split(objFile.ReadAll, vbNewLine)
		objRecordSet.AddNew
		columns = Split(TABLE_COLUMNS,",")
		props = Split(line,",")
		for i=0 to UBound(columns)
			objRecordSet(columns(i)) = props(i)
		next
		objRecordSet.Update
	next
Next


objRecordSet.Close
objConnection.Close

Select allOpen in new window

Variable not defined

Highlighted on  objWMIService =

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

add:

dim objWMIService

before line 19

same thing with Drive
Variable not defined

Highlighted on  Drive


Drive = Split(CSV_SEARCH_PATH, "\")(0)

I added following two lines

Dim objConnection As New ADODB.Connection
Dim objRecordSet As New ADODB.Recordset

variable not defined colFiles

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")

and please check         fso.  also
    Set objFile = fso.OpenTextFile(oFile.Name, ForReading)

Getting an Error

Run Time Error
 type mismatch

highlighted on  this line
            objRecordSet(columns(i)) = props(i)

just for information My data type is

First       column is number
Second  column is Text
Third      Column is Date/Time
fourth to Nine columns are Text
Tenth column is Number
Eleventh columns is Text
twelve to fifteenth columns are number

This is just for Ides..

did u initialize TABLE_COLUMNS  with the real columns from your table?

yeh,
Like this
Const TABLE_COLUMNS = "BNo,Mode,BDate,From_City,To,"

Hello sedgwick:

We are almost rich to solution, sir just need your assistant for run time error "type mismatch".

Thanks in advance

sorry for the delay, i'll get back to you with solution asap

Thanks sir

Hello sedgwick:

We are almost rich to solution, Jjust need your assistant for run time error "type mismatch". for columns

Sir,  I am still waiting,
My Project is Stuck cause of this type mismatch error,

And "Time limit" sword is hanging on my neck   :-)

Waiting for your Precious time, and Suggestion

I'll Appreciate if you share little time for this problem...!

Kind Regards

@crystalsoft

sorry for delay, long vacation...
where are we standing?
can u post most updated script and current error?

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.