exporting problem.

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

chakrikaAsked:
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.

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

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


~Hemanth
0
chakrikaAuthor Commented:
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
Andrea ErcolinoCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Andrea ErcolinoCommented:
no sorry, I made a mistake... now editing, wait
0
Sjef BosmanGroupware ConsultantCommented:
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
Andrea ErcolinoCommented:
    ...
     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
chakrikaAuthor Commented:
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
chakrikaAuthor Commented:
Raputa,

I get type mismatch right here:
values = Vdoc1.ColumnValues
0
chakrikaAuthor Commented:
i tried also this.
values = val(vdoc1.columnvalues)

this dosen't work either

thanks
0
Sjef BosmanGroupware ConsultantCommented:
> 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
Sjef BosmanGroupware ConsultantCommented:
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
chakrikaAuthor Commented:
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
chakrikaAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
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
chakrikaAuthor Commented:
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
Andrea ErcolinoCommented:
try this for NewLine

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

0
Sjef BosmanGroupware ConsultantCommented:
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
Sjef BosmanGroupware ConsultantCommented:
RAPUTA! It's 23.26 at your place. Time to go, don't you think? ;)
0
chakrikaAuthor Commented:
Tried that..

NewLine = {}
but i get a type mismatchv  at
If Instr( Colmember, NewLine ) > 0 Then Colmember = Join( Split( x, NewLine ), " " )
0
Andrea ErcolinoCommented:
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
Sjef BosmanGroupware ConsultantCommented:
> 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

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
Sjef BosmanGroupware ConsultantCommented:
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
Andrea ErcolinoCommented:
myself !! it's a good suggestion
0
chakrikaAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
Gracias! I go to bed now...
0
chakrikaAuthor Commented:
good night
0
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.