Import csv into existing sheet/tab

English
#1

Apologies that this has been oft asked before but I have not been able to find any answer that is comprehensive or to the point.

I have weekly/monthly updated/new data. I have previously imported historic data into a tab in existing sheet.

For (a bad example), I have monthly interest rate from Big Bank and in my sheet I have
01 Jan 2023, 10%
01 Feb 2023, 20%
01 Mar 2023. 3 %

Now I have a csv file with “01 Apr 2023, 5%”, I want to import this into my existing tab in my existing sheet…

I know I can import it into another tab, then copy and paste the relevant row. But I would much rather import directly into my existing tab.

Many thanks for any suggestions.

Using CALC to create list of DVDs - text formatting vs Print Preview
Dynamic Linking?
#5

Here we go again:

  1. menu::File>New>Database…
  2. Connect to existing database
  3. Type: Text
  4. Specify the directory where the text file(s) are stored and the import parameters.
  5. Check “Register database”
  6. Save the database document.
  7. Close and forget the database. No data are stored in that database document. Your data are in the text file(s). Each file is presented as a table of this pseudo-database.
  8. In Calc hit Ctrl+Shift+F4 for the data source window.
  9. In the left pane of that window, double-click the database name, then “Tables” and drag the table name into Calc.
  10. under menu:Data>Define… you click the name of the new database range, propably named “Import1”. Click “Options” and check 2 additional options “insert/delete cells” and “keep formatting”.

All the above steps need to be done only once.
Now you have a linked database range that can be refreshed with menu:Data>Refresh after clicking any single cell in that range.

1 Like
Using CALC to create list of DVDs - text formatting vs Print Preview
#6

Note that you may have to use Data>Refresh after re-opening your spreadsheet. That is, the document may not refresh these data ranges on load.

#7

Check the database range option “Do not save imported data”. You will be prompted to update the data when loading the document.

ThisComponent.DatabaseRanges.getByName("Import1").refresh() does the job by means of StarBasic.

Libre Office Calc – opmaak verdwijnt bij opnieuw inlezen van CSV bestand
#8

Many thanks to bothe Villeroy and joshua4 for various suggestions.

I am very sorry I did not mention one other requirement (which I thought was not relevant when I posted the original query). I need to send the spreadsheet as exported Excel to someone else. This means that accessing programmatically accessing external files and using supplementary programs (in this case Libreoffice Base) is not feasible.

From all the suggestions so far, it appears it is not possible in Libreoffice to incrementally add data, albeit in same format, to an existing sheet/tab.I am sure this is a very useful feature which I hope will be considered by the Libreoffice Calc team.

Had I not needed to send the (exported Excep) sheet to a 3rd party, your suggestions will fit exactly what I need.

Again apologies for not stating the requirement up front.

#9

Sorry to say it so bluntly, but it seems you didn’t understood much of the posts above.
.
I know no direct function to append csv to a spreadsheet - neither for excel, nor for calc. Both need a macro to hide the process you already described (load, copy and paste apoending).
.
Described were methods to access csv directly via Base This data is accessible by Calc. You simply drag a table or query from the list of registered datasources to cell A1 of a spreadsheet. Now you save as .ods or you can export to excel .xlsx. For obvious reasons Excel can not update/refresh the data, but will contain the needed data.
.
For my projects in this area I have used an approach using sqlite-database, because the kind of sql-queries I can use on csv/text-database is quite restricted.
So I have all data accessible via Base and can extract to Calc or create Database-reports. As sqlite is a file-based database I can also send this around quite easily. But I have to add an odbc-driver for sqlite3 to windows.
All quite convenient after the initial setup is done.
.
One reference to importing (new) csv to update an existing database follows. This kind of tools exist for several databases and it is also possible to do this for “identical” formatted csv-files to update without the header (basically a tail +2 file.csv)

Maybe useful for others who try to access csv directly (Note especially the link proded by villeroy at the end):
https://forum.openoffice.org/en/forum/viewtopic.php?t=23260

And preprocessing of csv-files for appending (linux):

#10

Yes, I did mention previously that I am very lazy and was hoping for a “simple” solution. I also know that nothing currently does what I want. And I do understand and appreciate very much the effort villeroy and joshua4 had made to help me.

I did have a look at the “indirect” solution. As soon as I opened the ods, it asks if I want to update the sheet, and when I said yes, I got an error since the files didn’t exist. I am sure it works for me if I think about it more. But sending it to a 3rd party may cause them problems since there are values (directories, drive letters) that need to be modified. And if there are many updates, may end up sending them many files or I end up combining and manipulating the csv files.

Re suggestion using a database and any number of manipulation, they are no simpler than me importing increments into to new tab and then copy paste new rows to existing tab; taking into account that the database needs to be updated. Having said that, I may end up having to write macro(s) to import the incremental data.

The question may sound stupid and trivial, but you yourself had to go through hoops to get this done. If there are 2 of us (and probably villeroy and joshua4) wanting to do this, I am sure there are many others wanting to do this. Hence the suggestion that Calc (and maybe other spreadsheet developers) may want to consider makes sense.

#11

There are many others, hoping to avoid “to go through hoops”. But for csv this can not be avoided completely. The file does not contain information on Charset, formatting of dates and also currencies can be a problem. YOU have to set this on import, or when setting up the database-connection. (And using locales will not help. German banks for example are sometimes sending out with german locale, some have their software on american style settings, and some have iso-dates…)
.
So it is possible to create a “simple” macro (import in separate sheet, copy all, append to end of some other table), but everybody will have to adapt csv-style and location of the csv.
.
Also you never really answered to joshuas assumption you only wish one line from a csv or, if you need to append the complete file.

#12

And now you have a method to import the update automatically by mere configuration.
All you need to do (once a week) is a simple copy and paste operation.

#13

I would like to express my sincere thanks to all who have helped and pointed out things I have to look out for. And I would like to have this topic closed as it is starting to diverge from my original query.

However, I still believe “a simple solution”, by the Calc developers, is achievable based on the following:

  1. the data is increment and therefore we can assume issues with locale, currency, date format are already known have already been catered for
  2. there is already the functionality to import csv into a new tab
  3. so unless there are fundamental issues that escapes my simple mind, should be possible to import/added to existing tab.
  4. hence, importing/adding incremental data in csv (file) into (cell in) existing tab should a simple case of “import (or) add csv to here” and perform same action as importing to new tab.

Again many thanks and much appreciation to all who have contributed.

1 Like
#2

Check “detect special numbers” in the import dialog.

#3

If I understand, you want to do an INDIRECT right into a CSV file. Yes you can. The trick is to use INDIRECT() and use the file name without extension as the tab name. There are lots of fine details.

See the attached ODS.

CellDirectFromCSV.ods (11.4 KB)

#4

Thanks joshua4. The suggestion/solution appears to be more involved that I had hoped for (but then this is me being very lazy).
I will give this a try adn see how I get along.

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