?
Solved

how to get data from csv to mdb file

Posted on 2010-09-04
30
Medium Priority
?
783 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 3
  • +3
30 Comments
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 672 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 93

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 664 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 664 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 143

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

765 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