Solved

how to get data from csv to mdb file

Posted on 2010-09-04
30
780 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 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Starting up a Project
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…

695 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