Solved

How to transfer data from csv to access.mdb

Posted on 2010-09-15
15
796 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

919 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

19 Experts available now in Live!

Get 1:1 Help Now