Solved

How to transfer data from csv to access.mdb

Posted on 2010-09-15
15
804 Views
Last Modified: 2012-06-27
Hi
I am using following code to create csv file

in the access.mdb table i have more than 70 columns,
but the data in the csv is for different different colums

So how can i transfer appropriate data to appropriate filed in access.mdb table

i have created connection with database
con is my connection string

''''''''creating csv file

      Dim sFileText As String
      Dim iFileNo As Integer
      Dim csvflname As String
      Dim origfile As String
      csvflname = Text1.text & ".csv"
        iFileNo = FreeFile
            'open the file for writing
        Open csvflname For Output As #iFileNo
        
      'please note, if this file already exists it will be overwritten!
            'write some example text to the file
        Print #iFileNo, Text1.text, Combo2.text, DTPicker1.Value, 0, 0, Text4.text, Text5.text
'''''close the file (if you dont do this, you wont be able to open it again!)
        Close #iFileNo

Open in new window

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
  • 9
  • 6
15 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33681550
You'll have to do it in several steps:
1. Open the csv-file and read it line by line
2. insert into a dataset (which is connected to the database)
3. Save mdb

Example for step 2 and 3

AddNew Method Example (VB)
http://msdn.microsoft.com/en-us/library/ms680885(VS.85).aspx
Public Sub Main()
    On Error GoTo ErrorHandler

    'recordset and connection variables
    Dim Cnxn As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQL As String
     'record variables
    Dim strID As String
    Dim strFirstName As String
    Dim strLastName As String
    Dim blnRecordAdded As Boolean

    ' Open a connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Northwind';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
       
    ' Open Employees Table with a cursor that allows updates
    Set rstEmployees = New ADODB.Recordset
    strSQL = "Employees"
    rstEmployees.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    ' Get data from the user
    strFirstName = Trim(InputBox("Enter first name:"))
    strLastName = Trim(InputBox("Enter last name:"))
    
    ' Proceed only if the user actually entered something
    ' for both the first and last names
    If strFirstName <> "" And strLastName <> "" Then
    
        rstEmployees.AddNew
        rstEmployees!firstname = strFirstName
        rstEmployees!LastName = strLastName
        rstEmployees.Update
        blnRecordAdded = True
        
        ' Show the newly added data
        MsgBox "New record: " & rstEmployees!EmployeeId & " " & _
        rstEmployees!firstname & " " & rstEmployees!LastName
        
    Else
        MsgBox "Please enter a first name and last name."
    End If
          
    ' Delete the new record because this is a demonstration
    Cnxn.Execute "DELETE FROM Employees WHERE EmployeeID = '" & strID & "'"
     
    ' clean up
    rstEmployees.Close
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
   ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33681565
Read Text File (txt, csv, log, tab, fixed length)
http://www.codeproject.com/KB/database/ReadTextFile.aspx
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33682820
Sir i got this code from Surone1

Problem is when i am adding more field with space ( Name with surname or company name) then its split it with two field and through an error Run Time Error "Number of Query Values and Destination Fields aren't the same on debug highlighted on following line
        con.Execute "insert into Bilty_Detail (BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To) values (" & valuelist & ")"

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Const MDB_FILE = "D:\Crystal\Auto_Trans\Trans.mdb"
Const TABLE_NAME = "Bilty_Detail"
Const TABLE_COLUMNS = "BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To"
''',From_City,To,Article,Description,Weight,Rate,ToPay,Paid,Pymt_mode,CR,HC,AOC,BC,Others,Total,Pvt_Mark,Declaration_val,Delivery_at,Narration,GroupName,Service_Tex,User_Name,Chg_Weight,TotToPay,TotPaid,Art_Type,Rate_Type,CNorTinNo,CNeeTinNo,STaxPayBy,FOV,Door_Delv,Invo_No,Status_Rec,CnorPNo,CneePno
Const CSV_SEARCH_PATH = "D:\Crystal\Auto_Trans"

Dim fso As New FileSystemObject
Dim objConnection As New ADODB.Connection
Dim objRecordSet As New ADODB.Recordset
Dim drive, objWMIService, path, line, objFile, oFile, i, props, colFiles, columns

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open "SELECT * FROM " & TABLE_NAME, _
    con, 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)
    Dim valuelist
    For Each line In Split(objFile.ReadAll, vbNewLine)
        objRecordSet.AddNew
        columns = Split(TABLE_COLUMNS, ",")
        
        ''''''''''''''''''''''''''''''''''''''
        props = Split(line, ",")

For i = 0 To UBound(props)
If Not IsNull(props(i)) And props(i) <> "" Then
If valuelist = "" Then
valuelist = valuelist & "'" & props(i) & "'"
Else
valuelist = valuelist & ",'" & props(i) & "'"
End If
End If
Next i
        
If valuelist <> "" Then
'objConnection.Execute "insert into  Bilty_Detail (BiltyNo,Mode,BDate,from_City,To) values (" & valuelist & ")"
        con.Execute "insert into Bilty_Detail (BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To) values (" & valuelist & ")"
        '''',From_City,To,Article,Description,Weight,Rate,ToPay,Paid,Pymt_mode,CR,HC,AOC,BC,Others,Total,Pvt_Mark,Declaration_val,Delivery_at,Narration,GroupName,Service_Tex,User_Name,Chg_Weight,TotToPay,TotPaid,Art_Type,Rate_Type,CNorTinNo,CNeeTinNo,STaxPayBy,FOV,Door_Delv,Invo_No,Status_Rec,CnorPNo,CneePno
End If

Open in new window

284750.csv
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 1

Author Comment

by:crystalsoft
ID: 33688888

I think problem is here

replace
props = Split(line, ",")
to
props = Split(line, " ")
so
some value is like this "south zone" for single filed
above line is split "south zone" to two field

Please check
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33688908
How are your csv-lines splitted ? Are the lines comma-separated, space-separated, ...

Can you give an example of such a line where it goes good and where it goes wrong (in debugging, just get the value of line, before splitting it)
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33689634
''''creating csv file using following code

      Dim sFileText As String
      Dim iFileNo As Integer
      Dim csvflname As String
      Dim origfile As String
      csvflname = Text1.text & ".csv"
''''      origfile = "Test.csv"
        iFileNo = FreeFile
            'open the file for writing
        Open csvflname For Output As #iFileNo
       
      'please note, if this file already exists it will be overwritten!
            'write some example text to the file
        Print #iFileNo, Text1.text, Combo2.text, DTPicker1.Value, 0, 0, Text4.text, Text5.text, Text2.text, Text3.text
        '''BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To
        '''BiltyNo,Mode,BDate,Consignor,Consignee,From_City,To
            ''''close the file (if you dont do this, you wont be able to open it again!)
        Close #iFileNo


Open in new window

284751.csv
Value.doc
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33689700
I would suggest to create your csv with ";" as separator. Now you don't know exactly where you have values with a space or not

Suppose the following:
You have following values: name = Crystal Soft
                                           function= Programmer
When you save it in csv with " " as separator you get
Crystal Soft Programmer   --> if you split this back (with space), you'll get 3 fields
When you save it in csv with ";" as separator you get
Crystal Soft;Programmer -->  if you split this back (with ;), you'll get 2 fields

So adjust your print-statement
Print #iFileNo, Text1.text, Combo2.text, DTPicker1.Value, 0, 0, Text4.text, Text5.text, Text2.text, Text3.text

Your text should get something like this:
Old:
284751        N/A           12-Aug-10      0             0            A ONE MARKETING             COMP TECH CORP              KOLKATA       AHMEDNAGAR
New:
284751;N/A;12-Aug-10;0;0;A ONE MARKETING;COMP TECH CORP;KOLKATA ;AHMEDNAGAR
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33689812
old
Print #iFileNo, Text1.text, Combo2.text, DTPicker1.Value, 0, 0, Text4.text, Text5.text, Text2.text, Text3.text

new
Print #iFileNo, Text1.text, ; Combo2.text, ; DTPicker1.Value, ; 0, ; 0, ; Text4.text, ; Text5.text, ; Text2.text, ; Text3.text

''''and on csv to access function, i changed following line
props = Split(line, ";")

but no luck same error


0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33689892
It goes wrong because of your "," in your print statement. Try the following

old
Print #iFileNo, Text1.text, Combo2.text, DTPicker1.Value, 0, 0, Text4.text, Text5.text, Text2.text, Text3.text

new
Print #iFileNo, Text1.text &";" & Combo2.text  &";" & DTPicker1.Value  & ";0;0;"  &  Text4.text  &";" &  Text5.text  &";" &  Text2.text  &";" &  Text3.text


props = Split(line, ";")
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33689996

Ooh Yehhh... Genius  :-D)

Its working,
Before i close this question  let me add all fields check...



0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33690029

Ooh Yehhh... Genius  :-D)

Its working,
Before i close this question  let me add all fields check...



0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33690371

Ooh Yehhh... Genius  :-D)

Its working,
Before i close this question  let me add all fields check...



0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33690386
Why are you always posting the same comment ;)
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33690428
Nooo, i am using wireless net connection
and its disconnect again and again cause of signal problem,
on reconnect and refreshing page same comment posting automatically,
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33707710

Thanks Dhaest:

Finally its working

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

In this post we will learn different types of Android Layout and some basics of an Android App.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

726 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