openCSV in LibreOffice

sub openCSV

' reqire a file with "fileNameDivider" in filename
' CSV file with column names and two rows with values
' ToDo: looks like file properties could be overridden by local properties, needs more testing
' Folke Larsson  Boden, Sweden 2017

' dialog, sheet variables
Dim myDialog as Object
Dim myFilepaths as Object
Dim myCurrFile as String
Dim myFileArray as Object
dim myFileProps(2) as new com.sun.star.beans.PropertyValue
Dim myNewWorkbook As Object
Dim mySheets as Object
Dim myCurrSheet as Object

' for storing in cells below table
Dim header1 as String
Dim header2 as String
Dim headerArray(10) as String

' save file variables
Dim fileName as String
Dim fileNameDivider as String
Dim fieldSeparator As Integer
Dim textDelimiter as Integer
Dim charset as Integer
Dim firstLine as Integer
Dim columnFormat as String
Dim languageId as Integer
Dim QuotedfFeldAsText as Boolean
Dim DetectSpecialNumbers as Boolean
Dim SaveCellContentsAsShown as Boolean
Dim filterOptionString as String

 fileNameDivider = "_-_"

 fieldSeparator = 44  ' ","
 textDelimiter =  34  ' """
 charset = 11         ' ASCII/US (Western)
 firstLine = 1
 columnFormat = "1/1/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10/10/10"
 languageId = 1033 '  English US
 quotedfFeldAsText = false
 DetectSpecialNumbers = false
 saveCellContentsAsShown = true

 ' TODO: ev make a loop with multiple files
 myDialog = createUnoService("com.sun.star.ui.dialogs.FilePicker")
 myDialog.setMultiSelectionMode(false)
 myDialog.appendFilter("CSV Files (.csv)", "*.csv" )
 myDialog.execute
 myFilepaths = myDialog.getFiles()
 myCurrFile = myFilepaths(0)

 myFileArray = split(myCurrFile, "/")
 fileName = myFileArray(UBound(myFileArray))
 headerArray = split(fileName, fileNameDivider)
 header1 = headerArray(0)
 header2 = Mid(headerArray(1), 1, Len(headerArray(1)) -4 )

' problems with file proprties, need to taste more detailde
 filterOptionString = "" + fieldSeparator + ", " + textDelimiter + ", " + charset + ", " + firstLine + ", " + columnFormat + ", " + languageId + ", " + quotedfFeldAsText + ", " + DetectSpecialNumbers + ""
 myFileProps(0).Name = "FilterName"
 myFileProps(0).Value = "Text - txt - csv (StarCalc)"
 myFileProps(1).Name = "FilterOptions"
 myFileProps(1).Value = filterOptionString
 myFileProps(2).Name = "MacroExecutionMode"
 myFileProps(2).Value = 4
 myNewWorkbook = starDeskTop.loadComponentFromURL(myCurrFile, "_blank", 0, myFileProps())

 mySheets = myNewWorkbook.getSheets()
 myCurrSheet = mySheets.getByIndex(0)
 myCurrSheet.getCellByPosition(0, 4).String = header1
 myCurrSheet.getCellByPosition(0, 5).String = header2
 myCurrSheet.getCellByPosition(0, 6).String = Mid(fileName, 1, Len(fileName)-4)

end sub ' openCSV