Solved

exporting problem.

Posted on 2004-10-26
302 Views
Last Modified: 2013-12-18
Hi,

I've an agent which extracts data from a view and sends it to the mainframe. Everything works fine except one small problem.

The agent is pretty simple:
Sub Initialize
      Dim Session As New notesSession    
      Dim View1 As NotesView      
      Dim Doc As NotesDocument
      Dim Vdoc1 As NotesDocument
      Dim Collection As NotesDocumentCollection
      Print "Processing - PLEASE WAIT"
      
      Set View1 = Session.CurrentDatabase.GetView("Ad\Export")
      
      Let CountRead = 0
      
      'Extracting Data from Ad\Export
      Set Vdoc1 = View1.GetFirstDocument           
      Open "C:\VAMFTP\ftp.txt" For Output As #01  
      While Not Vdoc1 Is Nothing          
            Forall x In Vdoc1.ColumnValues
                  Let CountRead = CountRead + 0.5                  
                  Print "Processing - PLEASE WAIT: Extracting Information " & CountRead & "-" & Vdoc1.ColumnValues(1)
                  If Isarray(x) Then
                        Forall Colmember In x
                              Print #01 , Colmember + "   ";
                        End Forall
                  Else
                        Print #01 , x + "   ";
                  End If
            End Forall
            Print #01 ,          
            Set Vdoc1 = View1.GetNextDocument(Vdoc1)
      Wend
      Close #01            
      
      'Exporting to Mainframe
      Print "Exporting to Mainframe"      
      If CountRead > 0 Then
            Messagebox "Exporting to Mainframe",,"Customer Profile"
            Dim Temp As Variant
            Temp = Shell("C:\FTP\bhini.bat")             
      Else
            Messagebox "Cannot Export to Mainframe as the Count Read is Zero",,"Customer Profile"
      End If      

Now the only problem is when any person, hits an enter key in the form when entering a value in a particular field, it is considered as another line and when the export is done
to the text file it puts the remaining information in the next line.

Let say the view has two columns. column1 is just 1 field. column2 is concatenation of fields.
Column1   Column2
123456     C1234{Yes{301-222-2134{Street 1{Street 2{
234555     C4565{No{301-222-2134{Street 1{Street 2{

Now if this is exported to a txt file using the above code is puts in the text file like this which is FINE.
123456   C1234{Yes{301-222-2134{Street 1{Street 2{
234555   C4565{No{301-222-2134{Street 1{Street 2{

But lets say if some has hit enter after entering Telephone number 301-222-2134 in the form and saved it.
Now if I export the same view to the text file using the above agent. Its puts it like this.
123456   C1234{Yes{301-222-2134
{Street 1{Street 2{
234555   C4565{No{301-222-2134
{Street 1{Street 2{

This is incorrect and how can i fix it.
Please advise
thanks

0
Question by:chakrika
    26 Comments
     
    LVL 24

    Assisted Solution

    by:HemanthaKumar
    In the Input translation of the field replace the enter char with space

    eg:
    @ReplaceSubstring(FIeldName; @Char(13); " ");


    ~Hemanth
    0
     

    Author Comment

    by:chakrika
    good idea. will this work If i use this in the column2 formula..

    right now column2 has mixture of fields..

    like
    accnum + "{" + custn + etc...etc...

    so can i put the above formula in here instead of putting in the form. Becuase I've to save the document again if I put this formula in input translation.
    0
     
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    If you do not want to change the docs, then you can use the following code replacement

         ...
         While Not Vdoc1 Is Nothing
              dim values as variant
              values = Vdoc1.ColumnValues
              Forall x In values
                   dim NewLine as string
                   NewLine = chr$( 13 )
                   if instr( x, NewLine ) > 0 then x = join( split( x, NewLine ), " " )
                   Let CountRead = CountRead + 0.5              
                   Print "Processing - PLEASE WAIT: Extracting Information " & CountRead & "-" & values(1)
                   If Isarray(x) Then
                        Forall Colmember In x
                              Print #01 , Colmember + "   ";
                        End Forall
                   Else
                        Print #01 , x + "   ";
                   End If
              End Forall
              Print #01 ,          
              Set Vdoc1 = View1.GetNextDocument(Vdoc1)
         Wend
         ...

    0
     
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    no sorry, I made a mistake... now editing, wait
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Is it only one field in which this happens? It seems as though you have set it to be a multi-value field. When concatenating fields, multi-value fields are handled differently. Maybe just switching off the multi-value capacity of that field will do?

    Otherwise, replacing won't do much good, you'll need to Implode your values in LotusScript (using Join or Implode, but only in R6)
    0
     
    LVL 3

    Expert Comment

    by:Andrea Ercolino
        ...
         dim NewLine as string
         NewLine = chr$( 13 )
         While Not Vdoc1 Is Nothing
              dim values as variant
              values = Vdoc1.ColumnValues
              Forall x In values
                   Let CountRead = CountRead + 0.5              
                   Print "Processing - PLEASE WAIT: Extracting Information " & CountRead & "-" & values(1)
                   If Isarray(x) Then
                        Forall Colmember In x
                             if instr( Colmember, NewLine ) > 0 then Colmember = join( split( x, NewLine ), " " )
                             Print #01 , Colmember + "   ";
                        End Forall
                   Else
                        if instr( x, NewLine ) > 0 then x = join( split( x, NewLine ), " " )
                        Print #01 , x + "   ";
                   End If
              End Forall
              Print #01 ,          
              Set Vdoc1 = View1.GetNextDocument(Vdoc1)
         Wend
         ...
    0
     

    Author Comment

    by:chakrika
    Hemanth,
    Its too time consuming to go save all the documents which are effected with this problem

    Sjef,
    I need all the fields to be present in column2. i cannot exclude some fields.

    Raputa,
    I'll try your code and let you know

    thanks all
    0
     

    Author Comment

    by:chakrika
    Raputa,

    I get type mismatch right here:
    values = Vdoc1.ColumnValues
    0
     

    Author Comment

    by:chakrika
    i tried also this.
    values = val(vdoc1.columnvalues)

    this dosen't work either

    thanks
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    > I need all the fields to be present in column2. i cannot exclude some fields.
    Is the field in the view a multi-value field (see Document Properties)? If not, ignore my earlier comment.

    Cn you see in the Document Properties that there is an Enter in the content of that field? If so, use Hemantha's suggestion, but then in LotusScript:
       ' @ReplaceSubstring(FIeldName; @Char(13); " ");
       Dim i As Integer
       i= InStr(fielval, Chr$(13))
       If i>0 Then
          Mid$(fieldval, i, 1)= " " ' replace Enter by space
       End If

    Or, if you have R6:
       fieldval= Join(Split(fieldval, Chr$(13)), " ")
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Eh, that's what Raputa suggested... Sorry.

    What I had in mind with my earlier suggestion was to change the VIEW, not the AGENT! Replacing things there is a LOT easier.
    0
     

    Author Comment

    by:chakrika
    ok, I changed it to the following:
    Dim NewLine As String
          NewLine = Chr$( 13 )
          While Not Vdoc1 Is Nothing
                Forall x In Vdoc1.ColumnValues
                     Let CountRead = CountRead + 0.5              
                     Print "Processing - PLEASE WAIT: Extracting Information " & CountRead & "-" & Vdoc1.ColumnValues(1)
                            If Isarray(x) Then
                            Forall Colmember In x
                                  If Instr( Colmember, NewLine ) > 0 Then Colmember = Join( Split( x, NewLine ), " " )
                                  Print #01 , Colmember + "   ";
                            End Forall
                      Else
                            If Instr( x, NewLine ) > 0 Then x = Join( Split( x, NewLine ), " " )
                            Print #01 , x + "   ";
                      End If
                End Forall
                Print #01 ,          
                Set Vdoc1 = View1.GetNextDocument(Vdoc1)
          Wend


    it works good, it is not pushing the record to next line.
    But its putting some junk value in the place of carriage return field.
    I uploaded the file to mainframe and checked the value in hex. It is not a period i guess. it is putting some junk value in there..
    123456   C1234{Yes{301-222-2134.{Street 1{Street 2{
    234555   C4565{No{301-222-2134.{Street 1{Street 2{




    0
     

    Author Comment

    by:chakrika
    sjef,

    the first column in the view is a multi-value field. and display multiple values as seperate entries is checked.

    the second column is just a mixture of fields. like fielda + "{" + fieldb "{" + fieldc etc.
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    I suppose it does not only contain a Chr$(13), but also a Chr$(10), which needs to be replaced as well.

    Did you try to adapt the view? And revert to your old agent?
    0
     

    Author Comment

    by:chakrika
    yes, i fixed one document and used my old code. it works fine....
    If I use raputa's code.. it works fine except that it is putting some junk.

    everything shows up in one line now.. but like i said it is putting some junk
    0
     
    LVL 3

    Assisted Solution

    by:Andrea Ercolino
    try this for NewLine

         dim NewLine as string
         NewLine = {
    } ' this brace on a new line

    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    I'm not quite sure, but I seem to remember that "display multiple values as separate entries" is only displayed, but NOT internally. In the view, when you walk through it using LotusScript, you will find only ONE document.

    You might use a NotesViewNavigator for this purpose, it will walk through the view as displayed, but you have to recognize/skip category-lines etc.
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    RAPUTA! It's 23.26 at your place. Time to go, don't you think? ;)
    0
     

    Author Comment

    by:chakrika
    Tried that..

    NewLine = {}
    but i get a type mismatchv  at
    If Instr( Colmember, NewLine ) > 0 Then Colmember = Join( Split( x, NewLine ), " " )
    0
     
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    it's true... my wife is waiting me at home!
    well, I hope you two will solve it, good night!

    just a little note, it's seems weird but you (chakrika) really need to write NewLine as an open brace + an ENTER + a closed brace, like in the example above... you cannot use an empty string!
    0
     
    LVL 46

    Accepted Solution

    by:
    > yes, i fixed one document and used my old code. it works fine....
    I didn't want to suggest to you to modify a document. Just modify the second view column, and use the original agent. The view column's formula should do a @ReplaceSubstring(field; @Newline; " ") for one field, or for the whole formula you have now. So
        @ReplaceSubstring(fielda + "{" + fieldb "{" + fieldc ; @Newline; " ")
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Well, it's the same time (+ some minutes) over here, so we'll continue in the morning. I am convinced that this Newline-business should not be handled in the agent but in the view. Who will support me? Please??
    0
     
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    myself !! it's a good suggestion
    0
     

    Author Comment

    by:chakrika
    Very good Sjef. it works fine.. Simple Solution.

    I'm going to split points as Raputa also did a good job here.

    thanks all
    0
     
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Gracias! I go to bed now...
    0
     

    Author Comment

    by:chakrika
    good night
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.

    856 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

    9 Experts available now in Live!

    Get 1:1 Help Now