[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to get data from csv to mdb file

Posted on 2010-09-04
30
Medium Priority
?
787 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
Meir Rivkin 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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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

Industry Leaders: 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

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Six Sigma Control Plans
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

649 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