We help IT Professionals succeed at work.
Get Started

Reading from CSV file problem

kspuea
kspuea asked
on
305 Views
Last Modified: 2013-12-18
hi all,

I have a LotusScripts program to read from a CSV file.
In the CSV file, it contains my user's IC no., name, contact no., and residential address (3 lines).
I used "tab" as the delimiter for my CSV file.
I have no problem to read data from the CSV file so far.

However, if the CSV file contains "commas" in the data, then my program can't read the data after the "comma" anymore.

For example, in my 1st line of Residential Address, I have "Apt 40, Duxton Road".

My program only able to pick up "Apt 40", and the "Duxton Road" is not been picked up at all.

Here is my program:

fileNum = Freefile()
      Open fileName For Input As fileNum
      
      Do While Not Eof(fileNum)
            Line Input #fileNum, txt
            If Trim(txt) <> "" Then
                  counter = counter + 1
            End If
      Loop
      
      Seek fileNum, 1
      Redim arrOfRecs(1 To counter)
      For countRec = 1 To counter
            Input #fileNum, arrOfRecs(countRec).eachLineTxt
      Next
      Close fileNum

For countRec = 1 To counter
            tmpTxt = arrOfRecs(countRec).eachLineTxt

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpCltIC = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client NRIC
            End If

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpClientNm = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client Name
            End If

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpCltTelNo = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client's Telephone Number
            End If

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpCltAddr1 = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client's Residential Address Line1
            End If

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpCltAddr2 = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client's Residential Address Line2
            End If

            posit = Instr(1, tmpTxt, Chr(9))
            If posit <> 0 Then
                  tmpCltAddr3 = Trim(Mid$(tmpTxt, 1, posit-1))
                  tmpTxt = Mid$(tmpTxt, posit+1)   'truncate the Client's Residential Address Line3
            End If
Next

Anyone knows what happened?
Did I miss out anything?

Thanks very much in advance!

~kspuea
Comment
Watch Question
Groupware Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE