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

It seems to me that your problem begins to go beyond the standard of this resource "question-answer", you are trying to discuss various options for the problem (in your initial question, nothing was said about Jack and Andrew with any numerical values). Be kind, use for this any forum - members of the community speak almost all the languages of the world.

JohnSUN gravatar imageJohnSUN ( 2018-10-01 21:09:13 +0200 )edit

Sorry it looks like I am adding new questions. My question sample data was really simple, because when I import data manually I only need to click on OK button all other settings are accepted from my previous session selections. I see macro does not accept my previous selections, so I never know what kind of real life data are going to be incorrectly imported using macro. Now I updated sample data with more real life columns. I studied Filter_options, but numbers are imported as strings. Bug?

pavion gravatar imagepavion ( 2018-10-02 08:09:52 +0200 )edit

String "59,34,33,1,1/2/2/2/3/5/4/2/5/10/7/2/8/10,1060,false,false,true,false,false" with your updated example work fine

JohnSUN gravatar imageJohnSUN ( 2018-10-02 09:09:53 +0200 )edit

JonhSUN, I did as you have instructed, but it does not work. When manually import csv file number fields are correctly imported, but when macro is run numbers are imported as "strings". I have recorded video to prove this. This looks like a bug to me.

Can you please see the short video animation: https://i.imgur.com/twGTWa3.gif

pavion gravatar imagepavion ( 2018-10-03 13:12:30 +0200 )edit

There is no last part - how the macro doesn't work. In addition, with such a manual import (without setting the format of any columns), all fields remained the default "Standard". In my last comment, the numeric fields are imported as "US English"... Please write to johnsun na-znak i dot ua

JohnSUN gravatar imageJohnSUN ( 2018-10-03 14:55:39 +0200 )edit

Sorry not to be clear enough. When I manually import (and leave all fields as "Standard") the numeric fields are imported as numbers (right aligned) and on them I can execute mathematical functions like sum. But when doing the same with macro numbers are imported as strings (left aligned) and on such fields it is impossible to do mathematical functions like sum, they always return 0. I have recorded one more video to prove this: https://i.imgur.com/uCHg4se.gif

pavion gravatar imagepavion ( 2018-10-04 10:22:16 +0200 )edit

You are wrong in thinking that not clear enough - even from the first video, it was clear where you are making a mistake. When importing, in the filter settings window, you immediately click on the OK button. Select the columns with numbers that are imported incorrectly, from the "Column type" drop-down list select "US English", and then start the import. And once again - such a long discussion in the comments, we'll break the format of this resource, it’s not customary to do so here.

JohnSUN gravatar imageJohnSUN ( 2018-10-04 11:59:14 +0200 )edit

John, now I see what you mean selecting "Column type = US English" for two numeric fields. I did this and got from getMeCode the string: "59,34,33,1,5/10/6/10,1060,true,false". I replaced the "OpenProp(1).value" with this value in openAndTrimCSV macro, save it and run from cmd and the problem is the same - macro opens strings instead of numbers. It is still weird to me why should we chose "US English" in the first place, Standard should work, IMHO.

pavion gravatar imagepavion ( 2018-10-04 14:31:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 365 times

Last updated: Oct 09 '18