# How to start LibreOffice Calc and do some automatic tasks

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 close merge delete ## 1 Answer Sort by » oldest newest most voted 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 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 ( 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) ( 2018-09-28 09:08:37 +0200 )edit ...or see an updated code in my answer ( 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. ( 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. ( 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. ( 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.

( 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

( 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.

( 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

( 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.

( 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?

( 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

( 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

( 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

( 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

( 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.

( 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.

( 2018-10-04 14:31:41 +0200 )edit