Domino 9 und frühere Versionen > ND8: Entwicklung

Export als csv

(1/4) > >>

Jens82:
Hallo zusammen,

zum Export einer Tabelle habe ich folgenden Agenten per LotusScript erstellt:

%REM
        Agent Export CCS
        Created 24.07.2014 by Jens/Firma/DE
        Description: Comments for Agent
%END REM
Option Public
Option Declare


Sub Initialize
        Dim ws As New NotesUIWorkspace
        Dim uiview As NotesUIView
        Dim view As NotesView
        Dim db As NotesDatabase
        Dim session As New NotesSession
        
        
        ' use the workspace to get the current view
        'Set uiview = ws.CurrentView  
        'Set view = uiview.View
        Set db = session.CurrentDatabase
        Set view = db.GetView( "CCSMA" )      
        
        Dim filenames As Variant
        
'Dim cn As Variant        
        
        ' Get filename from user using the current ViewName as the default file name
        filenames = ws.SaveFileDialog( _
        False,"File Name",, "C:\daten\", "Adressliste.csv")
        If Not(IsEmpty(filenames)) Then
                Call ViewCSVPrint (view, filenames(0) )
        End If
        
        
End Sub


Sub  ViewCSVPrint (view As NotesView, FileName As String )
        
        Dim fileNum As Integer  
        Dim entry As NotesViewEntry        
        Dim vc As NotesViewEntryCollection
        Dim rowstring As String        
        Dim cns As String
        
        fileNum% = FreeFile()
        Open filename For Output As fileNum%
        
        ' use the view column titles as the CSV headers
        ForAll c In view.Columns
                If cns = "" Then        
                        cns = c.title
                Else
                        cns = cns + +","  + c.title                  
                End If
        End ForAll                
        Print #fileNum%, cns
        
        
     ' now get and print the values for each row and column
        Set vc = view.AllEntries
        Set entry = vc.GetFirstEntry()
        While Not entry Is Nothing  
                rowstring = ""
                ForAll colval In entry.ColumnValues
                        If rowstring = "" Then
                                rowstring = colval
                        Else
                                rowstring = rowstring + +","  + colval
                        End If                        
                End ForAll
                Print #fileNum%, rowstring
                Set entry = vc.GetNextEntry(entry)
        Wend
        Close fileNum%
        
        
End Sub


Problem:
Bei Mehrfachwerten in Feldern werden diese durch ein Komma getrennt. Damit wird die Struktur beim Öffnen der CSV z.B. mit Excel zerschossen.

Wie kann ich das Script ändern, so dass ein anderes Symbol bei Mehrfachwerten mitgegeben wird?

Vorab vielen Dank.

Gruß

Jens

rambrand:
Hi Jens,

wie wäre es wenn Du colval auf "," untersuchst und durch ein beliebiges Zeichen Deiner Wahl ersetzt?

Thema wäre besser im Entwicklungsforum untergebracht ;)

Bye,
Markus

Jens82:
Hi Markus,

danke für die Antwort.

Wie könnte ich so etwas denn anstellen?

Viele Grüße

Jens

rambrand:
Ich hab mal vor ewiger Zeit folgende Funktion gefunden. Ich hab zwar noch kein Mehrwerte-Feld in ein CSV exportiert, aber da Du nur ein

--- Code: ---rowstring = rowstring + +","  + colval

--- Ende Code ---
machst, sollte das funktionieren.



--- Code: ---Function Findreplace(Byval wholestring As Variant, find As String, ireplace As String) As String
 checkstring=wholestring
 saveleft=""
 While Instr(1, checkstring, find)<>0
     n=Instr(1, checkstring, find)
     leftstring = Left(checkstring, n-1)
     rightstring=Right(checkstring, Len(checkstring)-n-Len(find)+1)
     saveleft=saveleft+leftstring+ireplace
     checkstring=rightstring
 Wend
 FindReplace= saveleft+checkstring
End Function

--- Ende Code ---

Aufrufen tust Du die Funktion dann
colval = findreplace(colval,",",";") <- oder ein anderes Zeichen als ";"
Das setzt Du dann einfach nach dem "Forall colval ..."

Bye,
Markus

Jens82:
Jetzt sieht das Ganze so aus:

%REM
        Agent Export CCS
        Created 24.07.2014 by jens
        Description: Comments for Agent
%END REM
Option Public
Option Declare


Sub Initialize
        Dim ws As New NotesUIWorkspace
        Dim uiview As NotesUIView
        Dim view As NotesView
        Dim db As NotesDatabase
        Dim session As New NotesSession
       
       
        ' use the workspace to get the current view
        'Set uiview = ws.CurrentView
        'Set view = uiview.View
        Set db = session.CurrentDatabase
        Set view = db.GetView( "CCSMA" )    
       
        Dim filenames As Variant
       
'Dim cn As Variant        
       
        ' Get filename from user using the current ViewName as the default file name
        filenames = ws.SaveFileDialog( _
        False,"File Name",, "C:\daten\", "Adressliste.csv")
        If Not(IsEmpty(filenames)) Then
                Call ViewCSVPrint (view, filenames(0) )
        End If
       
       
End Sub


Sub  ViewCSVPrint (view As NotesView, FileName As String )
       
        Dim fileNum As Integer
        Dim entry As NotesViewEntry        
        Dim vc As NotesViewEntryCollection
        Dim rowstring As String        
        Dim cns As String
       
        fileNum% = FreeFile()
        Open filename For Output As fileNum%
       
        ' use the view column titles as the CSV headers
        ForAll c In view.Columns
                If cns = "" Then        
                        cns = c.title
                Else
                        cns = cns + +","  + c.title                
                End If
        End ForAll                
        Print #fileNum%, cns
       
       
     ' now get and print the values for each row and column
        Set vc = view.AllEntries
        Set entry = vc.GetFirstEntry()
        While Not entry Is Nothing
                rowstring = ""
                ForAll colval In entry.ColumnValues
                        colval = findreplace(colval,",",";")
                        If rowstring = "" Then
                                rowstring = colval
                        Else
                                rowstring = rowstring + +","  + colval
                        End If                        
                End ForAll
                Print #fileNum%, rowstring
                Set entry = vc.GetNextEntry(entry)
        Wend
        Close fileNum%
       
       
End Sub


       
Function Findreplace(ByVal wholestring As Variant, find As String, ireplace As String) As String
       
        Dim checkstring As Variant
        Dim saveleft As Variant
        Dim n As Variant
        Dim leftstring As Variant
        Dim rightstring As Variant
       
        checkstring=wholestring
         saveleft=""
 While InStr(1, checkstring, find)<>0
     n=InStr(1, checkstring, find)
     leftstring = Left(checkstring, n-1)
     rightstring=Right(checkstring, Len(checkstring)-n-Len(find)+1)
     saveleft=saveleft+leftstring+ireplace
     checkstring=rightstring
 Wend
 FindReplace= saveleft+checkstring


Fehlermeldung:
Falscher Datentyp in Methode OP_CHECK_TOS_BYVAL: STRING wurde gefunden Unknown wurde erwartet

Wo liegt hier mein Fehler?

Viele Grüße

Jens

Navigation

[0] Themen-Index

[#] Nächste Seite