Import from comma delimeted text file

Hi,

How can I easily import to a view a text file (MS Proxy Server Log File) that is comma delimeted?  It will be one document per line. And it must able to interpret Date/Time Field.


Reden
LVL 1
redenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArunkumarCommented:
Hi reden,

You got to have a form for this imported documents to be viewed.

Did you try importing txt files using the notes import feature File-Import

You can specify the form and also calculate the ields on document import.

You can improve your import by writing a format file with .col extn.

Also you can import using LotusScript
one line after another and creating a document for each read.

With which one you have a problem ?

-Arun
0
redenAuthor Commented:
I already have a form, I tried File-Import Feature but it only have Tabular, Structured and Lotus 123 formats supported, If I try Tabular it goes in to a single field "Body"

How will I create a .col file?
0
ArunkumarCommented:
Hi reden,

This is an example i have with me....

The text File has something like this...

======================================
aruna,27,09-15-72,3000
arunb,27,09-15-72,3022
arunc,27,09-15-72,3033
arund,27,09-15-72,3044
arune,27,09-15-72,3055
arunf,27,09-15-72,3066
arunh,27,09-15-72,30546
arunf,27,09-15-72,30345
arung,27,09-15-72,30432
aruns,27,09-15-72,30445
======================================

Write a view action that will contain the following code

====================================
Dim ss As New notessession
Dim ws as new notesuiworkspace
     Dim db As notesdatabase
     Set db = ss.CurrentDatabase    
     Dim doc As notesdocument
     Dim wholetxt As String
     Dim fileNum As Integer
     Dim counter As Integer
     Dim countRec As Integer
     
     fileNum% = Freefile()
     counter% = 0
'Specify the file    
     Open "c:\windows\desktop\import.txt" For Input As fileNum%
     Do While Not Eof(fileNum%)
   ' Read each line of the file.
          Line Input #fileNum%, wholetxt$
         
          pos = Instr(1,wholetxt,",")
          firsttxt = Left(wholetxt, pos -1)
          remtxt = Right(wholetxt , Len(wholetxt) - (Len(firsttxt) + 1))
         
          pos = Instr(1,remtxt,",")
          secondtxt = Left(remtxt , pos-1)
          remtxt = Right(remtxt , Len(remtxt) - (Len(secondtxt) + 1))
         
          pos = Instr(1,remtxt,",")
          thirdtxt = Left(remtxt , pos-1)          
          remtxt = Right(remtxt , Len(remtxt) - (Len(thirdtxt) + 1))
         
'          pos = pos = Instr(1,remtxt,",")
          fourthtxt = remtxt
'          remtxt = Right(remtxt , Len(remtxt) - (Len(fourthtxt) + 1))
         
         
          Set doc = db.CreateDocument
          doc.form = "import"
          doc.NameField = firsttxt
          doc.AgeField = secondtxt
          doc.dobField = Cdat(Format(thirdtxt,"MM/DD/YYYY"))
          doc.salField = fourthtxt
          Call doc.Save(True,False)          
          Delete doc
         
   ' Increment the line count.
          counter% = counter% + 1
     Loop

call ws.viewrefresh

====================================

Some tips:
1. Make sure you give the exact filepath for the location of the file
2. Make sure that you change the datatype of the fields that you want to create on the document (This decides the datatype of the fields)
3. The code works for only 4 fields and you may extend this to "n" just by cut and pasting the logic inside

Good Luck !

-Arun

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.