Solved

Import CSV File data to MDB in VB6

Posted on 2000-02-20
22
3,662 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 10 start screen issues 9 55
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 47
Advice in Xamarin 21 79
IF ELSE Statement in Excel Macro VBA 16 64
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

773 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