OpenOffice Calc macro: loading multiple CSV into calc

sub importPayPal
    GlobalScope.BasicLibraries.loadLibrary("Tools")
    basePath = "file:///Volumes/HDA/Users/skwong/Documents/2013/09/130908b/workplaceCSV/" 
    ' Create new workbook
    oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 

  rem loading first document 

  Dim NextFile As String

  NextFile = Dir(basePath + "*", 0)

    While NextFile  <> ""
	FileN=GetFileNameWithoutExtension(NextFile,"/")
	MyResult = Right(FileN, 1)
	if (MyResult = "a") then
	    ImportCSVIntoTable(oDoc, "saikee", basePath + NextFile)
	endif
    NextFile = Dir
    Wend
    oDoc.getSheets().removeByName( "Sheet1" )
end sub

Sub ImportCSVIntoTable(oDoc as object, tableName as String, fileName as String)
Rem http://www.oooforum.org/forum/viewtopic.phtml?p=365860
	Dim oSheet as Object 
	Dim oPlan as Object 

   oSheet = oDoc.createInstance ( "com.sun.star.sheet.Spreadsheet" ) 
   oDoc.Sheets.insertByName ( tableName, oSheet ) 
   oPlan = oDoc.Sheets.getByName(tableName) 

      sURL = ConvertToURL ( fileName ) 
      sOrigin = "" 
      sFilter = "Text - txt - csv (StarCalc)"
'	See https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options for the options
'	44 = use comma as delimiter
'	34 = use " as Text delimiter
      sOpc = "44,34,0,1,1/2/2/1/3/2" 
'	following use "|" as delimiter - ("|" = 124) 
'      sOpc = "124,,0,1,1/2/2/1/3/2" 
      nModo = com.sun.star.sheet.SheetLinkMode.NORMAL 
      ' link file 
      oPlan.link(sURL, sOrigin, sFilter, sOpc, nModo) 
      ' reset link 
      oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE) 
End Sub

Another example:

sub import1csv(oDoc as object, oIndex as integer, fileName as string)
Dim Doc as Object
Dim Url As String
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="scalc: Text - txt - csv (StarOffice Calc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).Value ="59,34,33,8,"
rem kodovanie 33 je latin2 - win-1250

Doc = StarDesktop.loadComponentFromURL(fileName, "_blank", 0, FileProperties())
end sub

Reference:

 OpenOffice Calc macro: loading multiple CSV into calc




Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>