Solved

How to transfer data from csv to access.mdb

Posted on 2010-09-15
15
793 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
  • 9
  • 6
15 Comments
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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
Comment Utility
Read Text File (txt, csv, log, tab, fixed length)
http://www.codeproject.com/KB/database/ReadTextFile.aspx
0
 
LVL 1

Author Comment

by:crystalsoft
Comment Utility
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
 
LVL 1

Author Comment

by:crystalsoft
Comment Utility

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
Comment Utility
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
Comment Utility
''''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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:crystalsoft
Comment Utility
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
Comment Utility
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
Comment Utility

Ooh Yehhh... Genius  :-D)

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



0
 
LVL 1

Author Comment

by:crystalsoft
Comment Utility

Ooh Yehhh... Genius  :-D)

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



0
 
LVL 1

Author Comment

by:crystalsoft
Comment Utility

Ooh Yehhh... Genius  :-D)

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



0
 
LVL 53

Expert Comment

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

Author Comment

by:crystalsoft
Comment Utility
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
Comment Utility

Thanks Dhaest:

Finally its working

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

762 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

9 Experts available now in Live!

Get 1:1 Help Now