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

[maxbre]

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

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

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

[Lupp]

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

[maxbre]

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

[Villeroy]

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.

[maxbre]

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

[Villeroy]

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

[maxbre]

I set the options as follows

Code: Select all

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

[Villeroy]

Possibly you found a bug. I'll check later.

[Bidouille]

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

[Villeroy]

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.

[Villeroy]

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

[maxbre]

here it is my example

[Villeroy]

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.

[maxbre]

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

[Villeroy]

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

I attach my import result for documentation.

[maxbre]

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

[Villeroy]

Code: Select all

Sub refreshAllSheetLinks()
oEnum = thisComponent.SheetLinks.createEnumeration()
while oEnum.hasMoreElements
   oLink = oEnum.NextElement
   oLink.refresh
wend
End Sub