How to set delimiter to be TAB in SF_Array.ImportFromCSVFile for Calc

[zvegelj]

I’m trying to import txt file into array variable, and I can’t find the way to define delimiter to be TAB. I tried delimiter := Chr$(8) , delimiter := "\t" delimiter := "TAB" and some others but no luck. I have simple code like following.

Sub ImportTxtArray
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
	Dim arrC as Variant
	Dim dDoc as Object
	Set dDoc = CreateScriptService("Document", "MyStat.ods")
	arrC = SF_Array.ImportFromCSVFile("/tmp/List.txt", Delimiter := Chr$(9), DateFormat := "YYYY-MM-DD")
	dDoc.Activate("Sh1")
        If MsBox(arrC(0), 1) = 2 Then Exit Sub
	dDoc.SetArray("Sh1.B3", arrC)
End Sub

I tried also different delimiters ie “$” but it returns an error, probably only ones allowed are comma, semicolon, tab

[Wanderer]

Wich language uses := for parameter or is that something fir ScriptForge?

[Villeroy]

As always when dealing with csv, everything depends on the actual data in your List.txt.
As always when dealing with stupid StarBasic, anything depends on YOUR ability to utilize the StarBasic IDE to debug a Basic script.

[zvegelj]

@Wanderer

Wich language uses := for parameter or is that something fir ScriptForge?

It should be BASIC as is suggested in the manual for ScriptForge Array service
see below: (DateFormat :=…)

[zvegelj]

@Villeroy

As always when dealing with csv, everything depends on the actual data in your List.txt.
As always when dealing with stupid StarBasic, anything depends on YOUR ability to utilize the StarBasic IDE to debug a Basic script.

I’m trying to utilize new ScriptForge Library which seemed very practical to use, avoiding old APIs, but didn’t find many examples online

[Lupp]

@zvegelj

define delimiter to be TAB. I tried delimiter := Chr$(8)

HT is ASCII number 9 (and also U+0009).

@zvegelj

..."\t" delimiter := "TAB" and some others but no luck. …

How the separator needs to be defined must not be a question of luck with guessing, but of a specification. If the ScriptForge guys don’t specify the needed parameters/arguments explicitly, it must either be clear from reading the (commented) code (needing < 30 min for all of them) or the routines aren’t usable. Now having the help from CSV Filter parameters at hand, a selfmade solution based on the csv import filter may be more reliable and even simpler.
If you want to get the data into an array, you can (e.g.) open a (hidden?) sheet document based on the csv (your .txt file) filter Text - txt - csv (StarCalc) (all 13 filter options available), fill your array from the .DataArray there or use that sequence directly, and close the helper document again.

Example code:

Sub importCSVstyleFileAsSheetAndTakeArray
dim args(2) As New com.sun.star.beans.PropertyValue
args(0).Name  = "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
args(1).Name  = "FilterOptions"
args(1).Value = "9,34,0,1," REM The ommitted fifth token would spcify formatting/recognition.
args(2).Name  = "Hidden"
args(2).Value = False 'True
filePath = "C:\Users\thisbody\hisdirectory\downloaded\yourfile.txt" REM Just as an example for a Win user.
URL = ConvertToURL(filePath)

idoc = StarDesktop.loadComponentFromURL(URL, "_blank", 0, args()) 
ish  = idoc.Sheets(0)
cc = ish.createCursor()
cc.gotoStartOfUsedArea(False)
cc.gotoEndOfUsedArea(True)
myArray = cc.getDataArray()
idoc.close(True)
REM You may shape thie myArray now as a 2D-array.
End Sub

[Villeroy]

@zvegelj

I’m trying to utilize new ScriptForge Library which seemed very practical to use, avoiding old APIs, but didn’t find many examples online

Well, you are the pioneer exploring unknown territory. You have to do something. Programming is debugging. In my honest opinion, all these wrappers do not help anybody. They just add another layer and when something goes wrong you end up digging below that layer. I ignore this stuff, so I can not tell you anything about it.

[zvegelj]

@Lupp

@zvegelj

define delimiter to be TAB. I tried delimiter := Chr$(8)

HT is ASCII number 9 (and also U+0009).

Thanks for the heads up on Chr(9) which I had in old routine, but for the new call SF_Array.ImportFromCSVFile I mistyped.

@Lupp

Example code:

Sub importCSVstyleFileAsSheetAndTakeArray
dim args(2) As New com.sun.star.beans.PropertyValue
args(0).Name  = "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
args(1).Name  = "FilterOptions"
args(1).Value = "9,34,0,1," REM The ommitted fifth token would spcify formatting/recognition.
args(2).Name  = "Hidden"
args(2).Value = False 'True
filePath = "C:\Users\thisbody\hisdirectory\downloaded\yourfile.txt" REM Just as an example for a Win user.
URL = ConvertToURL(filePath)

idoc = StarDesktop.loadComponentFromURL(URL, "_blank", 0, args()) 
ish  = idoc.Sheets(0)
cc = ish.createCursor()
cc.gotoStartOfUsedArea(False)
cc.gotoEndOfUsedArea(True)
myArray = cc.getDataArray()
idoc.close(True)
REM You may shape thie myArray now as a 2D-array.
End Sub

And also for the workaround. I had it my self with looping through each field and the cells, and fill up the Array, but it was just lots of “unnecessary” code, but it works

[zvegelj]

I finally got it to work.
1.) TAB \t delimiter is defined as Delimiter := Chr(9) not Chr$(9) or “\t”
2.) In order for import to work well it needs to have exact number of fields in each row (mine had a few double TABs in the Header (1row) ) for formatting reasons when viewing txt file.
so working solution looks like this:

Sub ImportTxtArray
	GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
	Dim arrC as Variant
	Dim dDoc as Object
	Set dDoc = CreateScriptService("Document", "MyStat.ods")
'  ==> make sure to not have multiple TABS between fields
	arrC = SF_Array.ImportFromCSVFile("/tmp/List.txt", Delimiter := Chr(9), DateFormat := "YYYY-MM-DD")
	dDoc.Activate("Sh1")
        If MsBox(arrC(0), 1) = 2 Then Exit Sub
	dDoc.SetArray("Sh1.B3", arrC)
End Sub

Hopefully it will save some time to someone.