Solved

Import CSV File data to MDB in VB6

Posted on 2000-02-20
22
3,605 Views
Last Modified: 2008-02-26
How to import CSV TEXT file data
to MDB in VB6.
Do not use the ODBC.
0
Comment
Question by:hxb
22 Comments
 
LVL 1

Expert Comment

by:georgeman
ID: 2541190
hxb

- open MDB
- create recordset in MDB with CSV file structure. For example, if CSV file looks:
field1, field2, field3 etc.

recordset will look:
field1, field2, field3 etc.

- open CSV file to read
- read CSV file line by line
- upload it in recordset
- save MDB

That's it
Regards
George
0
 

Author Comment

by:hxb
ID: 2541210
can you give me the sample code?

If there are some special char
in the field Data, for example:
   " , etc
I do not know how to do with them.
   
   
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2541251
'You should create My.MDB with InfoFromCSV table (from Access for instance).

'10 - number fields in the CSV file
Dim sFields(10) as String
Dim dbCSV as Database
Dim rs as Recordset

'Connect to MDB
 Set dbCSV = OpenDatabase("c:\My.MDB")
 Set rs = dbCSV.OpenRecordset("InfoFromCSV", dbOpenDynaset)

'Open CSV file to read
 Open "c:\My.CSV" For Input As #1
 
'Start read CSV line by line and upload it in the recordset
 Do While Not EOF(1)
   
   'Get fields from CSV file into the array
   for i=1 to 10
      Line Input #1, sFields(i)
   next

   'Add new record to MDB table  
    rs.AddNew

       For i = 1 To 10
          rs.Fields(i) = sFields(i)
       Next

    rs.Update

 Loop

'Close CSV file  
 Close #1

'Close MDB
 rs.close
 set dbCSV = Nothing
0
 

Author Comment

by:hxb
ID: 2544489
Thank for your help.
I have a problem in getting the
Fields(i).
For example,
 In excel file:
  Name   Address       Sex
  John   San,Road1     Male
  Smith  "Road1"       Male
and then Export to CSV File (myCSV.csv) in excel.

MyCSV.csv:
   Name,Address,Sex
   John,"San,Road1",Male
   Smith,"""Road1""",Male
I can get the LineText, but
how to get the fields data from the
LineText?
I try to resolve the problem, but
it is too difficult for me.
I will increase the points if you
can give me a more detail sample
about getting the  fields(i) data.

Sample1:
LineText: John,"San,Road1",Male
FieldsData: Name=John
            Address=San,Road1
            Sex=Male
Sample2:
LineText: Smith,"""Road1""",Male
FieldsData: Name=Smith
            Address="Road1"
            Sex=male



 

0
 
LVL 1

Expert Comment

by:georgeman
ID: 2544570
Try this code.
Just create new project, form1, command1 and place below code in the command1_click event.
N.B. Code does work for exactly your csv file structure which you gave me
Regards
George

Private Sub Command1_Click()
'
'3 - number fields in the CSV file
 Dim sFields(3) As String
 Dim dbCSV As Database
 Dim rs As Recordset
 Dim sTemp As String
 Dim iPos1, iPos2, iTemp As Integer

'Connect to MDB
 Set dbCSV = OpenDatabase(App.Path & "\My.MDB")
 Set rs = dbCSV.OpenRecordset("InfoFromCSV", dbOpenDynaset)

'Open CSV file to read application path
 Open App.Path & "\My.CSV" For Input As #1
 
'Start read CSV line by line and upload it in the recordset
 Do While Not EOF(1)
   
   'Use this line if you need skip heads (Name,Address,Sex) from CSV file
   'Line Input #1, sTemp

   'Get line from CSV file into string
   Line Input #1, sTemp

   'extract 1st field - Name
   iPos1 = InStr(1, sTemp, ",", 1)
   sFields(1) = Left(sTemp, iPos1 - 1)

   'extract 3rd field - Male
   For iTemp = 1 To Len(sTemp) - 1
      If Mid(sTemp, Len(sTemp) - iTemp + 1, 1) = "'" Then
        sFields(3) = Right(sTemp, iTemp - 1)
        iPos2 = Len(sTemp) - iTemp
        Exit For
      End If
   Next

   'extract 2nd field - Address
   sFields(2) = Mid(sTemp, iPos1 + 1, Len(sTemp) - iPos1 - iPos2)
 
   MsgBox ">" & sFields(1) & "<>" & sFields(2) & "<>" & sFields(3) & "<"
   
   'Add new record to MDB table
    rs.AddNew
       For i = 1 To 3
          rs.Fields(i) = sFields(i)
       Next
    rs.Update
'
 Loop

'Close CSV file
 Close #1

'Close MDB
 rs.Close
 Set dbCSV = Nothing
'
End Sub

0
 

Author Comment

by:hxb
ID: 2544658
How to deal with the char , and "
when it exist in the Excel file?
When the Excel file be export to
csv, It will add "" to the CSV file.


If the Excel Field have the char of ","
it will add the "" to the CSV field
If the Excel Field have the char " it
will be """ in CSV file.

But how to delete the "" that add
to the CSV Field  by Excel

for example:
LineText: John,"San,Road1",Male
Result:     Name=John
            Address=San,Road1
            Sex=Male
Sample2:
LineText: Smith,"""Road1""",Male
FieldsData: Name=Smith
            Address="Road1"
            Sex=male
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2544727
after extraction all fields from one line of csv file you can use filter do delete all " characters, for example:

 for i=1 to 3

    sTemp = String(Len(sFields(i)))
    iTemp = 0

    for k = 1 to Len(sFields(i)))
       if Mid(sFields(i),k,1) <> chr(34) then
         iTemp = iTemp + 1
         Mid(sTemp,iTemp,1) = mid(sFields(i),k,1)
       endif
    next

    'new value
    sFields(i) = Trim(sTemp)

 next

Regards
George
0
 

Author Comment

by:hxb
ID: 2544988
If there is
a char Like (") or (,)
in the Address field,
and then I can get one line text,
but can not extraction all fields
correctly.
How to get the address field text
from line text like this:
   john, "san,Road1", male

The correct Address is (san,Road1)
not ("san).

and Sample2:
  john,"""san,Road1""",male
the correct Address is ("san,Road1")
not ("""san)
   
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548521
Ooopps
A little glitches:

1 Instead line:
 sTemp = String(Len(sFields(i)))
must be line:
 sTemp = String(Len(sFields(i)), " ")

2 Instead line:
    for k = 1 to Len(sFields(i)))
must be line        
    for k = 1 to Len(sFields(i))
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548538
OK hxb
Below source code for whole process (which was tested by myself).
Regards
George

LineText: John,"San,Road1",Male
Result:     Name=John
            Address=San,Road1
            Sex=Male
Sample2:
LineText: Smith,"""Road1""",Male
FieldsData: Name=Smith
            Address=Road1
            Sex=male

Private Sub Command1_Click()
'
'3 - number fields in the CSV file
 Dim sFields(3) As String
' Dim dbCSV As Database
' Dim rs As Recordset
 Dim sTemp As String
 Dim iPos1, iPos2, iTemp As Integer

'Connect to MDB
' Set dbCSV = OpenDatabase(App.Path & "\My.MDB")
' Set rs = dbCSV.OpenRecordset("InfoFromCSV", dbOpenDynaset)

'Open CSV file to read application path
 Open App.Path & "\My.CSV" For Input As #1
 
'Start read CSV line by line and upload it in the recordset
 Do While Not EOF(1)
   
   'Use this line if you need skip heads (Name,Address,Sex) from CSV file
   'Line Input #1, sTemp

   'Get line from CSV file into string
   Line Input #1, sTemp

   'extract 1st field - Name
   iPos1 = InStr(1, sTemp, ",", 1)
   sFields(1) = Left(sTemp, iPos1 - 1)

   'extract 3rd field - Male
   For iTemp = 1 To Len(sTemp) - 1
      If Mid(sTemp, Len(sTemp) - iTemp + 1, 1) = "'" Then
        sFields(3) = Right(sTemp, iTemp - 1)
        iPos2 = Len(sTemp) - iTemp
        Exit For
      End If
   Next

   'extract 2nd field - Address
   sFields(2) = Mid(sTemp, iPos1 + 1, Len(sTemp) - iPos1 - iPos2)
 
   'Add new record to MDB table
'    rs.AddNew
'       For i = 1 To 3
'          rs.Fields(i) = sFields(i)
'       Next
'    rs.Update
'
For i = 1 To 3

    sTemp = String(Len(sFields(i)), " ")
    iTemp = 0
'
    For k = 1 To Len(sFields(i))
       If Mid(sFields(i), k, 1) <> Chr(34) Then
         iTemp = iTemp + 1
         Mid(sTemp, iTemp, 1) = Mid(sFields(i), k, 1)
       End If
    Next

    'new value
    sFields(i) = Trim(sTemp)

 Next

 MsgBox ">" & sFields(1) & "<>" & sFields(2) & "<>" & sFields(3) & "<"

 Loop

'Close CSV file
 Close #1

'Close MDB
' rs.Close
' Set dbCSV = Nothing
'
End Sub



0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548556
Latest version

Private Sub Command1_Click()
'
'3 - number fields in the CSV file
 Dim sFields(3) As String
' Dim dbCSV As Database
' Dim rs As Recordset
 Dim sTemp As String
 Dim iPos1, iPos2, iTemp As Integer

'Connect to MDB
' Set dbCSV = OpenDatabase(App.Path & "\My.MDB")
' Set rs = dbCSV.OpenRecordset("InfoFromCSV", dbOpenDynaset)

'Open CSV file to read application path
 Open App.Path & "\My.CSV" For Input As #1
 
'Start read CSV line by line and upload it in the recordset
 Do While Not EOF(1)
   
   'Use this line if you need skip heads (Name,Address,Sex) from CSV file
   'Line Input #1, sTemp

   'Get line from CSV file into string
   Line Input #1, sTemp
   sTemp = Trim(sTemp)

   'extract 1st field - Name
   iPos1 = InStr(1, sTemp, ",", 1)
   sFields(1) = Left(sTemp, iPos1 - 1)

   'extract 3rd field - Male
   For iTemp = 1 To Len(sTemp) - 1
      If Mid(sTemp, Len(sTemp) - iTemp + 1, 1) = "," Then
        sFields(3) = Right(sTemp, iTemp - 1)
        iPos2 = iTemp
        Exit For
      End If
   Next

   'extract 2nd field - Address
   sFields(2) = Mid(sTemp, iPos1 + 1, Len(sTemp) - iPos1 - iPos2)
'   MsgBox sTemp & "<>" & Len(sTemp) & "<>" & iPos1 & "<>" & iPos2 & "<>" & sFields(2)
 
   'Add new record to MDB table
'    rs.AddNew
'       For i = 1 To 3
'          rs.Fields(i) = sFields(i)
'       Next
'    rs.Update
'
For i = 1 To 3

    sTemp = String(Len(sFields(i)), " ")
    iTemp = 0
'
    For k = 1 To Len(sFields(i))
       If Mid(sFields(i), k, 1) <> Chr(34) Then
         iTemp = iTemp + 1
         Mid(sTemp, iTemp, 1) = Mid(sFields(i), k, 1)
       End If
    Next

    'new value
    sFields(i) = Trim(sTemp)

 Next

 MsgBox ">" & sFields(1) & "<>" & sFields(2) & "<>" & sFields(3) & "<"

 Loop

'Close CSV file
 Close #1

'Close MDB
' rs.Close
' Set dbCSV = Nothing
'
End Sub

0
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

 

Author Comment

by:hxb
ID: 2548588
Please try this:
 
 In office 97 (Excel) input the text
   Name    Address     Sex
  John     San,Road1   Male
  Smith    "San,"Road1 Male

 And then Export to CSV File by Excel.

And try your code to Import the
CSV File again,
I want to get the Result:
   Record1:  Name=john
             Address=San,Road1
             Sex=Male
   Record2:  Name=Smith
             Address="San,"Road1
             Sex=Male
Can your code do it?

Best Regards
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548602
Just quick question:
how exactly field Address = San,Road1 looks in the Excel and how it looks in the CSV file
0
 

Author Comment

by:hxb
ID: 2548632
In the CSV File,
It will be:

  Name,Address,Sex
  John,"San,Road1",Male
  Smith,"""San,""Road1",Male
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548655
Try my source code a latest version - it works fine for this case.
I can't understand one thing: why address field looks so strange sometimes
(I mean """San,""Road1")
But try anyway because on output you will have clear data like San,Road1.

0
 

Author Comment

by:hxb
ID: 2548709
If the address include some char
like ("), for example ("San,"Road1)
IN EXCEL.

when it is Export to CSV,
the String will be: ("""San,""Road1")

When I import it from CSV,
I want to get the Address ("San,"Road1), same as in I input
in Excel file.

What I means is that how to
deal with the (")?
0
 
LVL 1

Expert Comment

by:georgeman
ID: 2548735
maybe easy to organise your Excel issue to standart mode before export ?
0
 

Author Comment

by:hxb
ID: 2548827
Thank for your help,

You can Lock the Question,
So I can give the points to you.
0
 
LVL 1

Accepted Solution

by:
georgeman earned 100 total points
ID: 2548915
That's up to you because you are question owner.
Regards
George
0
 

Author Comment

by:hxb
ID: 2549000
Thank
0
 

Expert Comment

by:phantomh
ID: 9013960
What reference do u use to set this variable as a Database object?  e.g. Dim dbCSV As Database
0
 

Expert Comment

by:Halon
ID: 9140437
DAO 3.6
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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

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

18 Experts available now in Live!

Get 1:1 Help Now