Advertisement
FolkeLarsson

openCSV in LibreOffice

Jan 3rd, 2017
1,114
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. sub openCSV
  2.  
  3. ' reqire a file with "fileNameDivider" in filename
  4. ' CSV file with column names and two rows with values
  5. ' ToDo: looks like file properties could be overridden by local properties, needs more testing  
  6. ' Folke Larsson  Boden, Sweden 2017
  7.  
  8. ' dialog, sheet variables
  9. Dim myDialog as Object
  10. Dim myFilepaths as Object
  11. Dim myCurrFile as String
  12. Dim myFileArray as Object
  13. dim myFileProps(2) as new com.sun.star.beans.PropertyValue
  14. Dim myNewWorkbook As Object
  15. Dim mySheets as Object
  16. Dim myCurrSheet as Object
  17.  
  18. ' for storing in cells below table
  19. Dim header1 as String
  20. Dim header2 as String
  21. Dim headerArray(10) as String
  22.  
  23. ' save file variables
  24. Dim fileName as String
  25. Dim fileNameDivider as String
  26. Dim fieldSeparator As Integer
  27. Dim textDelimiter as Integer
  28. Dim charset as Integer
  29. Dim firstLine as Integer
  30. Dim columnFormat as String
  31. Dim languageId as Integer
  32. Dim QuotedfFeldAsText as Boolean
  33. Dim DetectSpecialNumbers as Boolean
  34. Dim SaveCellContentsAsShown as Boolean
  35. Dim filterOptionString as String
  36.  
  37.  fileNameDivider = "_-_"
  38.  
  39.  fieldSeparator = 44  ' ","
  40.  textDelimiter =  34  ' """
  41.  charset = 11         ' ASCII/US (Western)
  42.  firstLine = 1
  43.  columnFormat = "1/1/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10/10/10"
  44.  languageId = 1033 '  English US
  45.  quotedfFeldAsText = false
  46.  DetectSpecialNumbers = false
  47.  saveCellContentsAsShown = true
  48.  
  49.  ' TODO: ev make a loop with multiple files
  50.  myDialog = createUnoService("com.sun.star.ui.dialogs.FilePicker")
  51.  myDialog.setMultiSelectionMode(false)
  52.  myDialog.appendFilter("CSV Files (.csv)", "*.csv" )   
  53.  myDialog.execute
  54.  myFilepaths = myDialog.getFiles()
  55.  myCurrFile = myFilepaths(0)
  56.  
  57.  myFileArray = split(myCurrFile, "/")
  58.  fileName = myFileArray(UBound(myFileArray))
  59.  headerArray = split(fileName, fileNameDivider)
  60.  header1 = headerArray(0)
  61.  header2 = Mid(headerArray(1), 1, Len(headerArray(1)) -4 )
  62.  
  63. ' problems with file proprties, need to taste more detailde
  64.  filterOptionString = "" + fieldSeparator + ", " + textDelimiter + ", " + charset + ", " + firstLine + ", " + columnFormat + ", " + languageId + ", " + quotedfFeldAsText + ", " + DetectSpecialNumbers + ""
  65.  myFileProps(0).Name = "FilterName"
  66.  myFileProps(0).Value = "Text - txt - csv (StarCalc)"
  67.  myFileProps(1).Name = "FilterOptions"
  68.  myFileProps(1).Value = filterOptionString
  69.  myFileProps(2).Name = "MacroExecutionMode"
  70.  myFileProps(2).Value = 4
  71.  myNewWorkbook = starDeskTop.loadComponentFromURL(myCurrFile, "_blank", 0, myFileProps())
  72.  
  73.  mySheets = myNewWorkbook.getSheets()
  74.  myCurrSheet = mySheets.getByIndex(0)
  75.  myCurrSheet.getCellByPosition(0, 4).String = header1
  76.  myCurrSheet.getCellByPosition(0, 5).String = header2
  77.  myCurrSheet.getCellByPosition(0, 6).String = Mid(fileName, 1, Len(fileName)-4)
  78.  
  79. end sub ' openCSV
Advertisement
RAW Paste Data Copied
Advertisement