The File-Open-filename sequence (a common, but not useful answer) always opens a NEW instance of LibraCalc.
I need the data to be IMPORTED (not opened in a new instance) into the current TAB in the existing spreadsheet for use.
Thank you
Merge sheets of spreadsheet documents
A customizable template with a macro library to store your csv import options and archive already imported files. The macro inserts imported rows into the existing list range, expanding all references.
- Store your document in a trusted directory according to Tools>Options>Security>[Macro Security…]
- Open my template, copy the sheet named “Setup” into your own. Sheet name does not matter. The macro makes use of named cells. Close my file.
-
Name the top-left cell of your list as “Target”.. If your list starts in column A, insert a column before. The target cell should be the first cell before the header row above the data rows. Click that cell and type “Target” into the name box left of formula bar. The macro will operate within the current region below that cell. Edit: Sorry I forgot that the macro records the file name in the very first column. - Tools>Macros>Organize>Basic… button [Organizer…], tab “Libraries”, import library “SheetMerge” from the downloaded template.
- With your document still loaded, open a sample of your csv files interactively with all the required import options and call Tools>Macros>Run … [your document]>SheetMerge>FilterOptions>setFilterOptions. This macro writes the csv import options of the active csv document into the cell named “FilterOptions” of your document. Close the sample csv.
- Specify the directory and file name pattern of the files to be imported, the count of header rows and optionally some archive directory where to move the processed files.
- You may add a toolbar button, create a toolbar, menu, shortcut, add some push button to the sheet or use menu:Tools>Macros>Run to call macro [your document]>SheetMerge>Module1>Main. It will import specified files into the current region below the target cell, inserting new rows, expanding references and optionally moving the processed files to the specified archive directory.
The macro keeps track of the data origin by filling the very first column with the file name which helps to identify/prevent duplicate imports.
newData.csv.odt (367 Bytes)
mergeDemo.ods (55.4 KB)
- put both files in the same directory.
- add some formula like =SUM($Data.$G$5:$G$988), sorry, I forgot.
- open the spreadsheet and click the go button.
To get it into the current tab use Menu/Sheet/Link to external data.
Help Link external data since a few versions ago, serves also to import csv files.
2 Likes
“Link to external data” works with html and spreadsheets, not with plain text.
Oh, that was new to me. I used to fail in older versions. However, it won’t insert new cells, and you have to specify all the details every time. And it always imports with header row.
1 Like
No, I didn’t know this feature. Nevertheless, it is poorly implemented.
Sometimes it is better to say nothing at all.
Often it is better to store data in a database.
It is completely confusing.
Just now, I added a link to a CSV to my ODS; in that link’s Text Import dialog, I selected to start from row 5; and in the link properties, I configured updating every 60 seconds. Then I edited the CSV in a text editor, and added another record. In a minute, that new record got automatically appended to my ODS.
What do I do wrong?
Turn the test document into a full blown spreadsheet with charts, formulas, all kinds of references. None of these references will take the imported data into account.
People desperately want databases on sheets. For instance, they want to import their csv bank account data on a daily basis. Virtually nobody wants to calculate with confined input data.
It looks as if Excel followed this desire for databases on sheets.
Please be specific. What should I click, enter, create on what specific data to see the problem? Please don’t act as all those newcomers that don’t yet know what reproduction steps means.
I don’t understand what do you expect me to see in your example, that doesn’t link any CSV using the discussed methid. I assume that you ignored the explicit request on the OP here, and simply decided to show some specific cases where the solution proposed by @mariosv won’t do some unasked thing.
The csv.odt is plain text. This place requires wrong extensions.
I understand that 
The point was - there is no External Link in your ODS, so how can I see that feature’s problems?
No, my sample was on topic.
Maybe - but can you please describe me where to look?