Ask Your Question
0

How to start LibreOffice Calc and do some automatic tasks

asked 2018-09-27 13:33:30 +0200

pavion gravatar image

updated 2018-10-02 08:11:29 +0200

Hi,

on Windows 7 using LibreOffice 6.0.6 using Windows batch I retrieve data from database and save it in text file mydata.csv like this:

COUNTRY; CAPITAL; DATE; USER; NUMBER; DEC_COMMA_DELIMITERED; WIN_LATIN2; QUOTE_NUM
USA; Washington; "2018-07-10"; Jack; 1; 12,34; ČČ; "1"
Germany; Berlin;  "2018-07-10"; Jordan; 2; -1,23; ŠŠ; "2"
UK; London;  "2018-07-10"; Andrew; 3; +0,12; ŽŽ; "3"

Update: I updated sample with few more different type of columns.

Now I do the following manually:

  1. From Windows cmd I execute command: start mydata.csv
  2. LibreOffice Calc opens and recognizes this is csv type of file and opens Text Import dialog. I select: Separated by then select Semicolon and press OK button.
  3. File is opened in Calc just fine. Now I need to remove spaces at the end of each of the column. So I click on the field left of A column to select whole sheet. Then from menu Edit select Find & Replace.
  4. Find & Replace dialog opens in Find field I type: \s+$ (regular expression to select one or more spaces at the end of field). Replace field I leave empty. Then I click on Other options and check the Reqular expressions and then click on Replace all button and finally Close button.

How to automate all this tasks. Ideal would be to execute some command on Windows cmd and all of the above tasks performed automatically.

Regards

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2018-09-27 16:18:20 +0200

JohnSUN gravatar image

updated 2018-10-09 08:34:12 +0200

it's not very difficult. First, create a module in a standard library named (for example) AutoOpen

Create subroutine like this

Sub openAndTrimCSV(Optional sFileName As String)
Dim sUrl As String, oDoc As Variant
    If IsMissing(sFileName) Then Exit Sub 
    sUrl = convertToURL(sFileName)
    Dim OpenProp(1) as New com.sun.star.beans.PropertyValue
    OpenProp(0).name="FilterName"
    OpenProp(1).name="FilterOptions"
    OpenProp(0).value="Text - txt - csv (StarCalc)"
    OpenProp(1).value="59,34,76,1,,1033,false,false,true,false,true"
    If Not FileExists(sUrl) Then Exit Sub
    oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, OpenProp())
Rem If your version of LibreOffice can't remove trailing spaces then unrem this part
'Dim oSheet As Variant, oCursor As Variant, aData As Variant, i&, j&
'   oSheet = oDoc.getSheets().getByIndex(0)
'   oCursor = oSheet.createCursor()
'   oCursor.gotoEndOfUsedArea(True)
'   oData = oCursor.getDataArray()
'   For i = LBound(oData) To UBound(oData)
'       For j = LBound(oData(0)) To UBound(oData(0))
'           oData(i)(j) = RTrim(oData(i)(j))
'   Or not to touch the numbers
'           If VarType(oData(i)(j)) = V_STRING Then oData(i)(j) = RTrim(oData(i)(j))
'       Next j
'   Next i
'   oCursor.setDataArray(oData)
End Sub

UPDATED RTrim() cannot be applied to all cells, for text cells only!

Save it and close LibreOffice. Now you can type in the command line

[Full path to LibreOffice\program folder]\scalc.exe macro:///Standard.AutoOpen.openAndTrimCSV([Full path and filename of your CSV-file])

Update You can use this macro to create a code snippet

Sub getMeCode
Dim oDoc As Variant
Dim aArgs As Variant
Dim sResult As String
Dim sTemp As String
    oDoc = ThisComponent
    aArgs = oDoc.getArgs()
    sTemp = getValArg(aArgs, "URL")
    sResult = "Dim sUrl As String, oDoc As Variant" + Chr(10) + "sUrl = convertToURL(""" + convertFromURL(sTemp) +""")" + Chr(10)
    sResult = sResult + "Dim OpenProp(1) as New com.sun.star.beans.PropertyValue" + Chr(10)
    sResult = sResult + "OpenProp(0).name=""FilterName""" + Chr(10)
    sResult = sResult + "OpenProp(1).name=""FilterOptions""" + Chr(10)
    sTemp = getValArg(aArgs, "FilterName")
    sResult = sResult + "OpenProp(0).value=""" + sTemp + """" + Chr(10)
    sTemp = getValArg(aArgs, "FilterOptions")
    sResult = sResult + "OpenProp(1).value=""" + sTemp + """" + Chr(10)
    sResult = sResult + "If Not FileExists(sUrl) Then Exit Sub" + Chr(10)
    sResult = sResult + "oDoc = stardesktop.LoadComponentFromURL(sUrl, ""_blank"",0, OpenProp())"
    MsgBox(sResult,0,"Select, Press Ctrl+C and Paste to macro")
End Sub

Function getValArg(aArg As Variant, sNameArg As String) As Variant
Dim i As Long
Dim tmp As Variant
    getValArg = ""
    For i = LBound(aArg) To UBound(aArg)
        tmp = aArg(i)
        If UCase(tmp.Name) = UCase(sNameArg) Then
            getValArg = tmp.Value
            Exit Function
        EndIf
    Next i
End Function

Just open your .csv file, set such parameters so that the data is imported as you want. Run the procedure getMeCode

edit flag offensive delete link more

Comments

Hi,

I did as instructed. File is successfully opened from Windows cmd. But there are still spaces left at the end of each field, just like trimming is not working. I have now changed my csv file adding dummy spaces after USA, Germany and UK. Executing your code from cmd, file opens and when I click on one of the data field and press F2 key and spaces are still there. I can manually remove them like described in step 4 from my first post in this thread. Any idea how to remove spaces?

Thanks

pavion gravatar imagepavion ( 2018-09-28 08:42:18 +0200 )edit

Best way - update your LibreOffice to the latest version. My 6.1.1.2 has addition option of import - "Trim spaces" (Removes starting and trailing spaces from data fields)

JohnSUN gravatar imageJohnSUN ( 2018-09-28 09:08:37 +0200 )edit

...or see an updated code in my answer

JohnSUN gravatar imageJohnSUN ( 2018-09-28 09:20:36 +0200 )edit

I removed comments in your code and now it works perfectly. I use LibreOffice at my day job and need very stable environment. I usually upgrade when project officially states software is enterprise ready. Thanks a lot for you help.

pavion gravatar imagepavion ( 2018-09-28 09:36:23 +0200 )edit

I would be grateful if you check the box next to the answer - mark it as correct. This will allow other users to quickly find a solution to similar problems.

JohnSUN gravatar imageJohnSUN ( 2018-09-28 09:51:17 +0200 )edit

I have found out one problem. It looks like by default macro opens with UTF-8 character set.

Sample data:

ščžŠČŽ

are opened as:

�螊Ȏ

In my case in Text Import dialog I always select the following:

  • Character set: Eastern Europe (Windows-1250/WinLatin 2)

  • Language: Slovenian

  • From row: 1

  • Separator Options: Semicolor is checked, String delimiter: "

  • Other Options: Format quoted fields as text is checked

All other options are unselected or unchecked.

pavion gravatar imagepavion ( 2018-09-28 11:05:03 +0200 )edit

And unfortunately one more problem. Macro trims spaces at the beginning and at the end of field. I only want to trim spaces at the end of field. My regular expression "\s+$" means delete spaces at the end of field.

pavion gravatar imagepavion ( 2018-09-28 11:19:39 +0200 )edit

It isn't a problem :-) Just replace this line OpenProp(1).value="59,34,33,1,,1060,false,false,true,false,true" 33=Windows-1250, 1060=Slovenian. All codes (and some other information about FilterOptions) you can find here

JohnSUN gravatar imageJohnSUN ( 2018-09-28 11:21:52 +0200 )edit

I have solved the trim problem by replacing Trim function with RTrim in your macro. Also your above solution fixes character set problems.

I am marking your post as solution to the problem. Thanks a lot for your help.

pavion gravatar imagepavion ( 2018-09-28 11:22:58 +0200 )edit

I have found one more problem. If source csv file has numbers using macro are always imported as text.

Source file:

DATE;NAME;VALUE
"2018-07-10";Jack;1
"2018-07-11";Jordan;2
"2018-07-12";Andrew;3

I came out with this:

OpenProp(1).value="59,34,33,1,1/1/2/1/3/1,1060,true,false,true,false,true"

or without parameter 5

OpenProp(1).value="59,34,33,1,,1060,true,false,true,false,true"

But numbers are always imported as text. See: https://i.imgur.com/tlXY740.png

pavion gravatar imagepavion ( 2018-10-01 10:24:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-27 13:33:30 +0200

Seen: 152 times

Last updated: Oct 09 '18