[Solved] Load data from csv and copy to a sheet

Keyboard macros or custom scripts

[Solved] Load data from csv and copy to a sheet

Postby maxbre » Fri Aug 16, 2019 11:50 am

I have worked oout by sneeking around the web this macro that is loading a csv file into a new instace of libreoffice

Code: Select all   Expand viewCollapse view
sub load_csv

   fname =  "file:////home/max/mytab.csv"
    if len(fname)>0 then
        dim fileProps(1) as new com.sun.star.beans.PropertyValue
        fileProps(0).Name = "FilterName"
        fileProps(0).Value = "Text - txt - csv (StarCalc)"
        fileProps(1).Name = "FilterOptions"
        fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
        document = StarDesktop.loadComponentFromURL(fname, "_blank", 0, fileProps())       
    end if
    msgbox "Done"
end sub


the code is working fine but I need to improve it a little in order to copy the whole contento of the imported into a new sheet of the existing file from which I'm executing the macro.

I've been playing around the parameters of
Code: Select all   Expand viewCollapse view
StarDesktop.loadComponentFromURL(fname, "_blank", 0, fileProps())

by following what is here reported https://wiki.openoffice.org/wiki/IT/Doc ... tarDesktop but without any success...

Any help for that?

thank you
Last edited by RoryOF on Tue Sep 03, 2019 5:57 pm, edited 3 times in total.
Reason: Added green tick [RoryOF, Moderator]
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Lupp » Fri Aug 16, 2019 2:02 pm

Did you already study the thread
viewtopic.php?f=21&t=77069 ?
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2533
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 9:51 am

Lupp wrote:Did you already study the thread
viewtopic.php?f=21&t=77069 ?


yes I did, there is plenty of good stuff there but it is definitely too much complicated (for me)

I just need to modify my original macro so that the target of the imported csv is a given sheet rather than a new file and I could not extrapolate that bit properly, at least up to now...

thanks anyway for pointing me that big resource of code snippets
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 10:14 am

maxbre wrote:I just need to modify my original macro so that the target of the imported csv is a given sheet rather than a new file and I could not extrapolate that bit properly, at least up to now...
Simply open that template as is.
Add the right import options for csv to named cell "FilterOptions", put the named cell "Target" somewhere and other named cells as needed. You can place the named cells anywhere within the same document.
Design the rest of the workbook any way you want.
Add your own cell styles, page styles, your own code and all the bells and whistles. If you call my macro from your macro, your macro from my macro, from a button, keystroke, toolbar or any other event simply does not matter.
Finally save the file with all its settings in the same directory as the csv files or anywhere you like if you specify the source directory.
My code only needs the target cell and the filter options for csv. It looks so complex because it is easy to use.

Using a database for database data (valid csv is always a database row set) would be even easier, however it looks even more complex.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 12:12 pm

yes I managed to work the template

I just wanted to copy a snippet of the code and insert it into my file...

....anyway by using your file "as is" (after setting up the necessary filter options) I have the additional problem that imported data need to be always overwritten in the same position and not appended

how can I do that?

thanks
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 12:46 pm

Sure, that macro collects data from incoming files.

You don't need any macro at all.
menu:Insert Sheet From File...
Specify the csv file and import options.
Check the "Link" option.
Next time when a new file comes in, simply replace the old file with the new file and refresh the link (re-open the spreadsheet) or via menu:Edit>Links... or change the link target via menu:Edit>Links...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 1:19 pm

I set the options as follows

Code: Select all   Expand viewCollapse view
TopInsert   TRUE   Insert newly imported ranges on top of the target area (TRUE) or below the current import list (0, FALSE or empty).   


but stil newly imported data are appended below the previuos one

there must be something I'm missing
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 1:38 pm

Possibly you found a bug. I'll check later.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby Bidouille » Tue Sep 03, 2019 2:09 pm

Depends of CSV data.
If these are malformed, you can have error.
User avatar
Bidouille
Volunteer
 
Posts: 285
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 2:10 pm

I downloaded the template from this web site, used the settings that are still in the template (my test files still exist), entered a TRUE* value into the cell named "TopInsert", imported all my test files and then one of them again. The second import is inserted on top of the other rows between the header row and the previously imported ones.

* a TRUE value is any number that is not zero. Depending on the language context, the word "TRUE" entered as a text value may or may not be interpreted as a boolean.
1, -1, 9999, -23 are all TRUE but 0 is not true.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 2:13 pm

Bidouille wrote:Depends of CSV data.
If these are malformed, you can have error.

Indeed, there must be millions of invalid csv files out there. Like with all csv related questions, this one can not be solved without seeing some actual text data
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 3:14 pm

here it is my example
Attachments
mytable.csv
file to be imported
(1.12 KiB) Downloaded 9 times
macro_basic.ods
my file based on your template
(29.69 KiB) Downloaded 7 times
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 4:12 pm

Your import options are wrong. All the values are imported as text. You get correct spreadsheet values with 44,34,76,1,,3081,false,true
.
Apart from that, everything works as expected.
I run the macro, get 31 rows, rename the file and get another 31 rows on top of the previous ones.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 4:49 pm

ah, ok I see now what you mean...

...but I need something different:
- no need to deal with the overhead to rename the file (the file name must be always the same while the content is changing) and then
- overwite the imported content of the file into the very same range cells (and not put on top of it)

thanks anyway

best
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 5:17 pm

Yes, simply create a linked sheet from file and forget all macros.

I attach my import result for documentation.
Attachments
macro_basic.ods
(27.24 KiB) Downloaded 9 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Load data from csv and copy to a sheet

Postby maxbre » Tue Sep 03, 2019 5:32 pm

ok, that's a valid solution

....even if I was looking to sort out all that programmatically, by means of some taylored macros

and certainly there must be a way to do it starting from my first coding attempt

anyway, thread closed now

thanks & best
LibreOffice: 6.1.5.2 on Debian 9
maxbre
 
Posts: 7
Joined: Fri Aug 16, 2019 11:40 am

Re: [SOLVED] Load data from csv and copy to a sheet

Postby Villeroy » Tue Sep 03, 2019 5:57 pm

Code: Select all   Expand viewCollapse view
Sub refreshAllSheetLinks()
oEnum = thisComponent.SheetLinks.createEnumeration()
while oEnum.hasMoreElements
   oLink = oEnum.NextElement
   oLink.refresh
wend
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests