How to start LibreOffice Calc and do some automatic tasks

English
#1

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

Multiple csv/ods files into individual tabs in single spreadsheet [closed]
#2

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

#3

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

#4

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)

#5

…or see an updated code in my answer

#6

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.

#7

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.

#8

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.

#9

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.

#10

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.

#11

It isn’t a problem :slight_smile: 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

#12

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.

#13

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

#14

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.

#15

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?

#16

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

#17

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:

#18

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

#19

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:

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.