Solved

how to get data from csv to mdb file

Posted on 2010-09-04
30
732 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A short article about problems I had with the new location API and permissions in Marshmallow
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now