Solved

how to get data from csv to mdb file

Posted on 2010-09-04
30
753 Views
Last Modified: 2012-06-21
Hi
How to transfer data from csv to access.mdb file

i have so hundreds of csv files with name formate like....
123456.csv
456321.csv
741852.csv
963854.csv
789364.csv

and how can i get data from these files to access.mdb - one by one automatically..
0
Comment
Question by:crystalsoft
  • 12
  • 8
  • 3
  • +3
30 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33603976
crystalsoft,Please have a look at my answer at http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24566385.html#a24842897All you would need to do there is change the ".xls" search term to ".csv", and use DoCmd.TransferText instead of DoCmd.TransferSpreadsheet.Patrick
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 168 total points
ID: 33603981
Thus...
Dim fso As Object, fld As Object, fil As Object

Set fso = CreateObject("Scripting.FIleSystemObject
Set fld = fso.GetFolder("c:\folder\subfolder")

For Each fil In fld.Files
    If LCase(Right(fil.Name, 4)) = ".csv" Then
        DoCmd.TransferText TransferType:=acImport, _
            TableName:= "NameOfTable", FileName:=fil.Path, _
            HasFieldNames:=True
    End If
Next

Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33607323

Thanx for reply

I am getting "Compile Erro"

"Variable Not Defined"

and highlighted on "acImport"

        DoCmd.TransferText TransferType:=acImport, _


0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33607547
That code was written to be run from Access.  Were you perhaps trying to run it from VB6?
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33609774

yeh
i am trying to run it from vb6

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33610887
Import a Delimited Text File into a Database
http://www.freevbcode.com/ShowCode.Asp?ID=612
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33610894
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

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33610918
are all csv files should be imported to the same table?
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33611735

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

Expert Comment

by:sedgwick
ID: 33611829
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

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33611835
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

Open in new window

0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33612001


Variable not defined

Highlighted on  objWMIService =

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

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33612023
add:

dim objWMIService

before line 19
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33612065

same thing with Drive
Variable not defined

Highlighted on  Drive


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

Assisted Solution

by:sedgwick
sedgwick earned 166 total points
ID: 33612089
try now
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"

dim drive,objWMIService,path,line,objFile,oFile,columns,props,i

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

Open in new window

0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33612470
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)
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33612592

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

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33615235
did u initialize TABLE_COLUMNS  with the real columns from your table?
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33616001

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

Author Comment

by:crystalsoft
ID: 33618233

Hello sedgwick:

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

Thanks in advance

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33618269
sorry for the delay, i'll get back to you with solution asap
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33618291
Thanks sir
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33627262
Hello sedgwick:

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

Author Comment

by:crystalsoft
ID: 33628280

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

0
 
LVL 2

Assisted Solution

by:Bhupesh Singal
Bhupesh Singal earned 166 total points
ID: 34290771
Here is the cdoe to open the txt file and upload the data to .mdb database:

Open txtfilenum.Text For Input As nfilenum
            llinecount = 1
                If cn.State = 1 Then cn.Close
                cn.Open
                Dim STRSQLRates
                Dim rsdel2 As New ADODB.Recordset
                rsdel2.Open "Delete from  T1_FXRates where status=1", cn, adOpenDynamic, adLockOptimistic
                Do While Not EOF(nfilenum)
                    Line Input #nfilenum, snextline
                    stext = snextline
                    Data = Split(stext, vbTab)
                    STRSQLRates = "insert into T1_FXRates(Version_Type_CD,Period_yr,Period_DT,Curr_ID,Curr_Name,GL_RT,Recip_GL_RT) values ('"
                    For i = 0 To UBound(Data)
                    If i = UBound(Data) Then
                        STRSQLRates = STRSQLRates & Replace(Trim(Data(i)), "'", "")
                    Else
                        STRSQLRates = STRSQLRates & Replace(Trim(Data(i)), "'", "") & "','"
                    End If
                    Next i
                    STRSQLRates = STRSQLRates & "');"
                    'MsgBox (STRSQLRates)
                    cn.Execute STRSQLRates
                Loop
            MsgBox ("Rates Data Transfer Successfully")
        Call enabl
        Close nfilenum


rgds
Bhupesh
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 34290791
@crystalsoft

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34977865
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.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question