How to open *csv in Calc Macro with tabulation

[eugenefoxx]

Hello,
how to open *csv in Calc Macro with tabulation (file is export from SAP in uncorrect format *xls - really it's *csv) -

Code: Select all

Sub testCSV
Dim oDoc1, oDoc2 as Object
Dim oFile1, oFile2 as String
Dim oRange1 , oRange2 as Object
Dim oURL1, oURL2 as String
Dim oCell as Object
Dim oSheet as Object
Dim i,r,b,c,d,y,x as Integer
dim aProps(1) As new com.sun.star.beans.PropertyValue	
	oFile1 = "file:///media/user/Data/Folder/FILE.XLS" 
	oURL1 = convertToUrl  (oFile1)
	aProps(0).name = "FilterName"
	aProps(0).Value = "Text - txt - csv (StarCalc)"
	aProps(1).name = "FilterData"
	aProps(1).Value = "<TAB>,,76,1"
	oDoc1 = starDeskTop.loadComponentFromURL (oURL1, "_blank", 0, aProps() )
  	                                                       
End Sub

[Villeroy]

Open the file manually with all required options for proper import. Then run the following macro to get the FilterOptions for this type of csv file. "FilterData" are either deprecated or not appropriate for the csv filter.
http://www.openoffice.org/api/docs/comm ... FilterData

Code: Select all

Sub showFilterOptions
Dim args(),i%
   args() = thisComponent.getArgs
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
   next
End Sub 

[eugenefoxx]

I find the solution (yes, "FilterData" not appropriate)

Code: Select all

Sub testCSV
Dim oDoc1, oDoc2 as Object
Dim oFile1, oFile2 as String
Dim oRange1 , oRange2 as Object
Dim oURL1, oURL2 as String
Dim oCell as Object
Dim oSheet as Object
Dim i,r,b,c,d,y,x as Integer
dim aProps(1) As new com.sun.star.beans.PropertyValue   
   oFile1 = "file:///media/user/Data/Folder/FILE.XLS"
   oURL1 = convertToUrl  (oFile1)
   aProps(0).name = "FilterName"
   aProps(0).Value = "scalc: Text - txt - csv (StarCalc)"
   aProps(1).name = "FilterOptions"
   aProps(1).Value = "9,,76,1" '9 - To specify that some fields are separated by a tab would use "9" for the filter options.
   oDoc1 = starDeskTop.loadComponentFromURL (oURL1, "_blank", 0, aProps() )
                                                            
End Sub

I have a set of data which has to be converted to csv file using UTF-16, to keep special characters. Anyone having a solution to this? (For example, Character Set UTF-8 = index "76". https://wiki.openoffice.org/wiki/Docume ... er_Options)

[mauriciobaeza]

Try EasyDev: http://easydev.readthedocs.org/en/lates ... import-csv

Code: Select all

Dim options(0) As New com.sun.star.beans.NamedValue

util = createUnoService("org.universolibre.EasyDev")

range = ThisComponent.CurrentSelection
path = "/home/USER/test.csv"
options(0).Name = "delimiter"
options(0).Value = CHR(9)
data = util.importCSV(path, options)
range.setDataArray(data)

Best regards

[Villeroy]

The answer remains the same. Use the GUI and then run my code.