My CopyRange macro is pasting numbers as text instead of numbers

Wilop91 · October 14, 2024, 2:20pm

Hello
Below is my macro which is copying the “used range” of data from a sheet in my source file to a similar sheet in my target file. The macro works fine, but after pasting is completed, my data is now shown as “text” while originally it are numbers (shown as “blue” in Calc). As a result my “graphs” does not work anymore…
Can anyone tell me whats wrong with my macro?
Thanks in advance for any assistance
Willy

Function rangecopy (oDocT as Object, myTargetIdx as Integer, mySourceUrl as String)
    Dim oDocS As Object, oSheetS As Object, oRangeS as object
    Dim oSheetT As Object, oRangeT as object
    Dim Dummy()
	
	dim args1(5) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "Flags"
	args1(0).Value = "SV"
	args1(1).Name = "FormulaCommand"
	args1(1).Value = 0
	args1(2).Name = "SkipEmptyCells"
	args1(2).Value = false
	args1(3).Name = "Transpose"
	args1(3).Value = false
	args1(4).Name = "AsLink"
	args1(4).Value = false
	args1(5).Name = "MoveMode"
	args1(5).Value = 4
    
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
	oDocS = OpenCsvAsText (mySourceUrl ) 'Open my CSV source file
 rem ----------------------------------------------------------------------
    oSheetS = oDocS.getSheets.getByIndex(0) 'The CSV source file contains a single sheet
    c = oSheetS.createCursor
    c.gotoEndOfUsedArea(false)
    LastRow = c.RangeAddress.EndRow 
    LastColumn = c.RangeAddress.EndColumn 
    oRangeS = oSheetS.getCellRangeByPosition(0,0,LastColumn,LastRow)
    oDocS.CurrentController.Select(oRangeS)
    oFrameS = oDocS.CurrentController.Frame
 rem ----------------------------------------------------------------------
    oSheetT = oDocT.getSheets.getByIndex(myTargetIdx) 'The target file contains several sheets identified by  myTargetIdx given as argument
    c = oSheetT.createCursor
    oRangeT = oSheetT.getCellRangeByPosition(0,0,LastColumn,LastRow)
    oDocT.CurrentController.Select(oRangeT)
    oFrameT = oDocT.CurrentController.Frame
 rem ----------------------------------------------------------------------
    oDispatcher.executeDispatch(oFrameS, ".uno:Copy", "", 0, Dummy() )
    oDispatcher.executeDispatch(oFrameT, ".uno:Paste", "", 0, args1() )
rem ----------------------------------------------------------------------
    oDocS.close(true)

End Function

Villeroy · October 14, 2024, 2:31pm

oRangeT.setDataArray(oRangeS.getDataArray()

elmau · October 14, 2024, 4:10pm

Missing parenthesis

range_target.setDataArray(range_soource.getDataArray())

Wilop91 · October 15, 2024, 7:01am

Hello elmau
Thanks for your reply.
It did not solve the issue but in the mean time I could narrow down the issue.
If i open my CSV file manually the data is shown as numbers (i.e. blue) but if I open the file via my macro the data is shown as text (i.e. grey). So its normal the copy/paste function results in text.
The function I use to open my CSV file I took from internet and is called OpenCsvAsText.
While reading the code of this function I tought it would open the CSV file in a similar way we do manually but that seems not to be the case.
I use this function as follows:
oDocS = OpenCsvAsText (mySourceUrl ) '
Any idea what iI’m doing wrong?
Thanks in advance
Willy
Here is the function:

Function OpenCsvAsText (Optional ByVal fileName As String, Optional ByVal tokens As String) As Object
   Dim args(1) as new com.sun.star.beans.PropertyValue
   Dim s As String, i As Long, oFileDialog
   Const MaxFields As Long=200  ' largest possible number of fields 
   
   OpenCsvAsText=Nothing   
   If IsMissing(fileName) Then fileName=""
   If IsMissing(tokens) Then tokens="" 
   
   If tokens="" Then tokens="44,34,UTF8,1"  ' comma, double quotes, UTF8, from 1st line
   If fileName="" Then
     oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
     With oFileDialog
       .appendFilter "Csv-files", "*.csv"
       If .Execute=0 Then Exit Function 
       fileName=.selectedFiles(0)
     End With  
   End If

   For i=1 To MaxFields 
     s=s & "/" & i & "/2"
   Next i
   If IsMissing(tokens) Then tokens="44,34,UTF8,1"  ' see https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
   args(0).Name="FilterName"   : args(0).Value="Text - txt - csv (StarCalc)"
   args(1).Name="FilterOptions": args(1).Value=tokens & "," & Mid(s, 2)
   OpenCsvAsText = StarDesktop.loadComponentFromURL(ConvertToUrl(fileName), "_blank", 0, args)   
End Function

Wilop91 · October 15, 2024, 8:11am

Hello
I could narrow down a bit more. Now the fuction OpenCsvAsText opens my CSV file showing values as numbers (i.e. blue) and strings as text (grey) but date/time became "numbers (i.e. blue), so thats a problem in my graphs.
Here is the change I made, i.e; in the line args1, I commented out “’& “,” & Mid(s, 2)” so that I use a standard token with 4 fields.
The first line of my CSV file contains date/time in the format of 2024-10-15, 2024-10-15, etc. (its an export of a Domoticz database), the relaining lines are numbers or text.
Can anyone help me here?
Thanks in advance
Willy

If IsMissing(tokens) Then tokens="44,34,UTF8,1"  
args(0).Name="FilterName"   : args(0).Value="Text - txt - csv (StarCalc)"
args(1).Name="FilterOptions": args(1).Value=tokens '& "," & Mid(s, 2)
OpenCsvAsText = StarDesktop.loadComponentFromURL(ConvertToUrl(fileName), "_blank", 0, args)

Villeroy · October 15, 2024, 8:20am

Without any data samples, nobody can answer any csv related questions. There are thousands of different csv variants.

It is possible to connect directly to the SQLite database, so you don’t have to struggle with csv and silly macros.

Wilop91 · October 15, 2024, 10:56am

Hello Villeroy
Thanks for your reply.
To export data from Domoticz, I’m using a dzVents script which I found on internet and this script generates CSV files. To be honest I think the Domoticz database structure is far too complex for me and beyond my competences to be able to query directly from SQLite into Excel.
Here is an example of a CSV file:

Meter,Name,2024-03-30,2024-03-31,2024-04-01,2024-04-02
15,EUbiomassGen,8012,8082,8345,8441
16,EUfossilGasGen,44886,43811,19159,16821
18,EUfossilOilGen,4470,3763,5040,2999
20,EUhydroRoRGen,157305,143750,139823,139295

Willy

Villeroy · October 15, 2024, 12:23pm

For the above csv, Text - txt - csv (StarCalc) is the FilterName property and the FilterOptions property is 44,0,76,1,,2057,false,false,true,false,false,0,false,false,true. You can find the correct parameters by inspecting the arguments (method getArgs) of the correctly opened csv file.

sokol92 · October 15, 2024, 12:25pm

Hello Willy!
This is correct, because I wrote the OpenCsvAsText function, which interprets all fields of the csv file as texts.
In your example, there is an accident: some cells of the first row, which contain the field headers, are converted into a date.
As far as I remember, there is currently no parameter that would allow special treatment of the first line of the file.

Add the following text after the line

 oSheetS = oDocS.getSheets.getByIndex(0) 'The CSV source file contains a single sheet

of the macro from the starting message.

  Dim j As Long, oCell As Object, arr(9999)
  Do While True
    oCell=oSheetS.getCellByPosition(j, 0)
    If oCell.string="" Then Exit Do
    arr(j)=oCell.string
    j=j+1
  Loop  
  If j>0 Then
    ReDim Preserve arr(j-1)
    oSheetS.getCellRangeByPosition(0, 0, j-1, 0).setDataArray Array(arr)
  End If

This should convert the cells in the first row to text.

Wilop91 · October 15, 2024, 3:34pm

Hello sokol92
Thanks for your reply.
That seems to work fine but I need to do more testing tomorrow…
However I export from Domoticz 3 more types of data:in separate CSV file and having a different stucture:
Daily electrical consumption:

Name,Value1,Value2,Date
"Wops - Linky",11897,0,2020-11-15
"Wops - Linky",13989,0,2020-11-16
"Wops - Linky",14843,0,2020-11-17
"Wops - Linky",14640,0,2020-11-18
"Wops - Linky",14127,0,2020-11-19

Daily Temparature:

Temperature,Name,avg,max,Date
4,TempHumBaro,12.16,12.6,2022-01-02
4,TempHumBaro,11.1,12.3,2022-01-03
4,TempHumBaro,8.42,11.1,2022-01-04
4,TempHumBaro,3.01,6.5,2022-01-05
4,TempHumBaro,3.94,6.2,2022-01-06

Hourly Temparature

ID,Name,Humidity,Temperature,Date
4,TempHumBaro,96,13.2,"2024-10-08 08:30:00"
4,TempHumBaro,96,13.0,"2024-10-08 08:35:00"
4,TempHumBaro,96,13.0,"2024-10-08 08:40:00"
4,TempHumBaro,96,13.1,"2024-10-08 08:45:00"
4,TempHumBaro,96,13.1,"2024-10-08 08:50:00"

Is a more general solution possible?

PS: after my macro completed the copy/paste of my 5 CSV files, I’m replacing the dot used in decimals by a comma to have my numbers in french format. Would there be a way to do the same for date/time?

Thanks a lot for your help

Willy

sokol92 · October 15, 2024, 3:48pm

We have two sets of settings:

Description of the csv file format.
Locale settings for LibreOffice.

When importing, we describe the parameters for the csv file. These parameters should work in any locale installed in LibreOffice.
In general, the set of import parameters is individual for each csv file.

If you attach a full set of samples of imported csv files, we will try to come up with an effective solution.

Wilop91 · October 15, 2024, 4:31pm

Hi sokol92
Thanks for your help.
Here are the CSV files of today (I export every day the data from Domoticz via a script. running periodically)
Domoticz2ExcelExports.zip (36.3 KB)

PS: I could fix the “hourly temperature” by rebuilding my graph.
Willy

sokol92 · October 15, 2024, 5:40pm

Let’s try this (see Test):

Option Explicit

' Open csv file.
' - fileName       path to file (OS or URL формат)
' - filterOptions  csv filter parameters (https://help.libreoffice.org/latest/en-GB/text/shared/guide/csv_params.html)
Function OpenCsv(Optional ByVal fileName As String, Optional ByVal filterOptions As String) As Object
   Dim args(1) as new com.sun.star.beans.PropertyValue
   Dim oFileDialog As Object
   Dim oDoc As Object 
   Dim j As Long, oCell As Object, arr(9999)
   
   OpenCsv=Nothing   
   If IsMissing(fileName) Then fileName=""
   If IsMissing(filterOptions) Then filterOptions="" 
   If filterOptions="" Then filterOptions="44,34,76,1"  ' comma, double quotes, UTF8, from 1st line
   
   If fileName="" Then
     oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
     With oFileDialog
       .appendFilter "Csv-files", "*.csv"
       If .Execute=0 Then Exit Function 
       fileName=.selectedFiles(0)
     End With  
   End If

   args(0).Name="FilterName"    : args(0).Value="Text - txt - csv (StarCalc)"
   args(1).Name="FilterOptions" : args(1).Value=FilterOptions
   oDoc = StarDesktop.loadComponentFromURL(ConvertToUrl(fileName), "_blank", 0, args)
   
   ' Convert the cells of the first row to text
   Do While True
     oCell=oDoc.sheets(0).getCellByPosition(j, 0)
     If oCell.string="" Then Exit Do
     arr(j)=oCell.String
     j=j+1
   Loop  
   If j>0 Then
     ReDim Preserve arr(j-1)
     oDoc.sheets(0).getCellRangeByPosition(0, 0, j-1, 0).setDataArray Array(arr)
   End If  
   
   OpenCsv=oDoc
End Function

Sub Test
  Dim opt As String, myFolder, oDoc As Object
  opt="44,34,76,1,,1033,false,true,true,false,false,0,false,false,true"
  myFolder="C:\temp"
  oDoc=OpenCsv(myFolder & "\domoticz2excelDailyLinky.csv", opt)
  oDoc=OpenCsv(myFolder & "\domoticz2excelDailyTemp.csv", opt)
  oDoc=OpenCsv(myFolder & "\domoticz2excelHourlyTemp.csv", opt)
  oDoc=OpenCsv(myFolder & "\domoticz2excelHourlyTemp.csv", opt)
  oDoc=OpenCsv(myFolder & "\domoticz2excelDaily_export.csv", opt)
  oDoc=OpenCsv(myFolder & "\domoticz2excelHourly_export.csv", opt)
End Sub

Villeroy · October 15, 2024, 6:21pm

If the names of the csv files are always the same, create a new spreadsheet and use menu:Sheet>“Insert sheet from file” with “Link” option for each one of the 5 file names in your zip sample.
Tomorrow, simply replace the old files with the new ones before opening the spreadsheet with the 5 links.

Wilop91 · October 15, 2024, 7:06pm

haha simple but efficient.
I have tested with one file and it works but will test more tomorrow
Thanks a lot Villeroy for your help
Willy

Villeroy · October 15, 2024, 9:31pm

Well, it’s just a spreadsheet. Therefore, it appears to be simple. However, nothing is simple with a spreadsheet and far from being efficient. Learning a programming language from the 90ies for the main purpose of adding a little bit of database functionality to a spreadsheet is neither efficient.

First of all, replacing the old files with new ones removes the old information. If today’s database dump always includes all the data of the past, this is not an issue. However, the amount of data loaded entirely into memory may become very large over time. Calc becomes very slow with large amounts of data. At least, the original data are stored safe and warm in a database.
You must not add anything to the linked sheets because the entire sheets will be replaced tomorrow, deleting all your formulas, charts, (conditional) formattings, pivot tables etc. you may have added to yesterday’s sheets. You have to use extra sheets for your own stuff. Keep a backup of the extra sheets because of the issue in point 1. When everything just crashes, leaving a corrupt spreadsheet, you can restore the links and add the additional sheets from backup.
Any reference as in formulas like =SUM('domoticz2excelHourly_export.csv'.$A$2:$A100) may be wrong tomorrow, when the new sheet has more than 99 rows of data below the header row.
3.1. “Simple solution”: =SUM('domoticz2excelHourly_export.csv'.$A$A) which sums up the entire column. However, some of your text files have numeric headers, so the dates in the column headers will be summed up with the actual data.
3.2. =SUM('domoticz2excelHourly_export.csv'.$A$2:$A1048576) including all rows except the column header A1.

There is an alternative way of linking csv data, still with no macro code, eliminating above outlined issues.

Wilop91 · October 17, 2024, 8:12am

Hello all
Some feedback after 2 days testing (I export data from Domoticz only once a day)
The “import sheet” function works fine for me.
My graphs and the imported data are NOT in the same sheets.
The amount of minute and hourly data in Domoticz is fixed (7 days max after which it rollsover) so that will not be an issue en Calc.
I still need to look into why my numerical data has US format (dot instead of comma) but this is an SQLite issue (even if i make SELECT FROM WHERE in the CLI I get the same result). I need to check why.
PS: I need to work first on weather data because OpenStreetMap stopped its free API on last 14th october so I dont have data anymore
Willy

Villeroy · October 17, 2024, 10:12am

Are you sure that your numerical data have been imported correctly? =ISNUMBER(A2) should return TRUE if the value in A2 is a number.
Wrong csv import may be the most frequent Calc issue ever.
If the text contains point decimals, you need to choose some English import locale. In the below screenshot I use “English(USA)” because of the point decimals, Month/Day/Year dates and am/pm times.

Wilop91 · October 17, 2024, 10:34am

Hi Villeroy
Thanks for your reply.
The weather data was coming from OpenStreetMap and is recorded by Domoticz running on my Rspbarry Pi. I dont realy have control over this feature. But when I start SQLite on my RPI and perform a SELECT, this is what I see:

sqlite> SELECT a.ID ID, a.Name Name, b.Humidity Humidity, b.Temperature Temperature, b.Date Date FROM DeviceStatus a , Temperature b WHERE a.ID = b.DeviceRowID AND a.ID =39 ORDER BY a.ID;
39|Meteo-VLB-New|79|25.9|2024-10-16 10:15:00
39|Meteo-VLB-New|79|25.9|2024-10-16 10:20:00
39|Meteo-VLB-New|70|21.6|2024-10-16 10:25:00
39|Meteo-VLB-New|70|21.6|2024-10-16 10:30:00
39|Meteo-VLB-New|70|21.6|2024-10-16 10:35:00
39|Meteo-VLB-New|70|21.6|2024-10-16 10:40:00

My Raspberry PI and Domoticz have been installed using “french” language, but I need yet to investigate if SQLlite is respecting this setting.
Anyway I will check your suggestion to see if I can fix this
Thanks again.
Willy

Villeroy · October 17, 2024, 11:56am

This is why Calc pops up that import dialog where you have total control over any flavour of csv. That dialog does not describe what you want to get. In that dialog, you tell Calc what all the text is about. With any English language setting in that dialog and with “Detect special numbers”, you get the dates, times, decimals of your SQL output imported correctly.
When I import the screenshot sample into my German LibreOffice setup, I get comma decimal 95,92, German date 20.06.2010, time 12:25 and comma decimals 0,59,96,01, 96,57 and 95,62. But first I have to tell Calc that all this text needs to be interpreted in US-English context, including any “special numbers” such as dates, times, currencies, percents.

P.S. If you get wrong data from linked csv sheets, you need to delete the sheets and link the files again in order to get the import dialog.