Solved

How to transfer data from csv to access.mdb

Posted on 2010-09-15
15
805 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
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!

 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
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…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Six Sigma Control Plans

728 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