Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

exporting problem.

Posted on 2004-10-26
26
Medium Priority
?
308 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
Comment
Question by:chakrika
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 6
  • +1
26 Comments
 
LVL 24

Assisted Solution

by:HemanthaKumar
HemanthaKumar earned 80 total points
ID: 12412329
In the Input translation of the field replace the enter char with space

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


~Hemanth
0
 

Author Comment

by:chakrika
ID: 12412729
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
ID: 12412977
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

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

Expert Comment

by:Sjef Bosman
ID: 12413128
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
ID: 12413157
    ...
     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
ID: 12415496
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
ID: 12415688
Raputa,

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

Author Comment

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

this dosen't work either

thanks
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12415785
> 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
ID: 12415842
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
ID: 12415843
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
ID: 12415864
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
ID: 12415879
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
ID: 12415905
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
Andrea Ercolino earned 160 total points
ID: 12415907
try this for NewLine

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

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12415920
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
ID: 12415944
RAPUTA! It's 23.26 at your place. Time to go, don't you think? ;)
0
 

Author Comment

by:chakrika
ID: 12415946
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
ID: 12415974
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:
Sjef Bosman earned 160 total points
ID: 12415992
> 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
ID: 12416014
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
ID: 12416022
myself !! it's a good suggestion
0
 

Author Comment

by:chakrika
ID: 12416034
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
ID: 12416074
Gracias! I go to bed now...
0
 

Author Comment

by:chakrika
ID: 12416168
good night
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

610 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