Import a csv to an exsiting sheet

English
#1

I have five fields: day, date, time, systolic, diastolic.
I want to automate: day, date, time and just hand-enter Systolic and Diastolic, each day, for 60 days.

I see online that calc might have issues with automating and custom formatting date/time fields. I’ve seen a lot of VB work-arounds.

So I considered simply coding a C/C++ routine to handle day, date and time with the format I want, and hand-entering just the BP fields and saving it out as a CSV, appending to the file each day until the series is complete.

Then, I’d import this to calc and print it out, I just need calc for the formatting. Which brings me to the question: can calc append data to an open sheet which has formatting already applied?

It’d be great if it could, but not a deal-breaker if it can’t.

Or is there a sort of command-line API to format cells before importing to a sheet? That would be ideal.
Not that big of a deal, though, I could always format post-import, just thought it was worth asking.

#4

While there are a lot of users stumbling on problems I don’t see any big problems in Calc. But often Spreadsheets don’t do what the average user expect. (And there are even more users having problems to import dates properly from csv.)
.
In your case:

  • Set the formatting for your complete columns BEFORE you enter data.
  • For date/time you can use key-codes to enter a current time or you might even use a formula if you prefer a fixed schedule.
  • Note, there are two ways to integrate a date. One is for templates like invoices, where you wish the current date automatically written and adapting, if you open the file again. The other is a fixed date/time for your logs.

Just to mention it: LibreOffice also has forms to fill and several databases are availabe to store this simple data. Queries can be dragged to Calc and refreshed there.

#10

When generating your own flavor of csv (which is a database exchange format), you should always export flat decimals with points as decimal separator and ISO dates (yyyy-mm-dd).
When designing a spreadsheet as a database surrogate, you should nevertheless proceed as if it were a true database. Splitting equal structures with same meaning into separate tables makes everything more difficult.
No matter how simple the task, I would always prefer a database. LibreOffice makes it very easy to use linked database data with spreadsheets.
blood_pressure_hsql.odb (36.0 KB) (just open the form)

#19

What I ended up doing was a lot easier than I expected it to be. As Wanderer pointed out, I formatted the sheet the way I wanted it first, entering some test data manually. Then I ran the csv data entry, created dummy data and simply copied an pasted it into the next row after the test entries - done.
.

sheet
sheet926×626 288 KB

.
I did update the csv code. Links to the updated code, and spreadsheet below:
.
bp.c
bp.ods
.
I gotta say, this is one of the most helpful forums I’ve experienced, I thank you all for walking me through this and sharing your knowledge, you’ve given me a lot of ideas to pursue, thanks again.

#2

I have done this in both google sheets and LO Calc. I don’t see a need to complicate things with csv files.

image
image992×673 88.7 KB

Simply set up your 60 day counter in Column A starting (say) at line 4 as shown with the year in A2 and month in A3.
Set up you columns for time, systo,Diast & pulse for direct entry as according to results obtained.
Then the formula for inserting the day in column F (could be column B if preferred) will be =TEXT(DATE($A$2,$A$3,$A4),“dddd”).
Copy down the formula in the day column. et voila!
possible enhancements:

  • format the cells as required
  • create one sheet per month
  • add columns across the date line for additional measurements each day

Changing the values in A2 & A3 will ensure that the correct values are inserted in the day column.

#3

Avoid this. IMHO: One Sheet for all data is better. One can filter current month/last week etc. much easier than combining data again…

3 Likes
#5

It depends! Here’s an example with spreadsheets every month and automatic updating of the annual overview. It’s certainly a bit complex, but you might be able to use some of the references. My file is worth studying :student::
000_LO-CALC_csv to ods_021430.ods (23.0 KB)
minor corrections:
000_LO-CALC_csv to ods_v02_022705.ods (25.8 KB)

#6

I’d be interested to know if you consider there to be any unfavourable overhead having one sheet with many monthly tabs (basically my original proposal) over one master sheet as suggested by @Wanderer
My working sheet in Google Sheets (from which I drew the suggestion) is constructed much as your suggestion with a summary tab as the final tab for the year in view with each month’s results averaged to give my Cardio an overview of the results based on the times of day the readings are taken. These results are assembled on a colour coded basis (using conditional formatting to reflect 0000-0959, 1000-1559,1600-0000 daily) and has served it’s purpose well for the past at least 5 years.

#7

Just ignore that “it depends” with nonsense summing up as “it is possible to use kludges to overcome huge problems coming from the “data in separate tabs” approach”, which can’t sanely justify that it may be as useful as the @Wanderer’s superior approach.

2 Likes
#8

A seemingly endless sheet with numerous monthly overviews: quick to create, not very complex, very fast initial presentation, but increasingly confusing the more extensive it becomes.
Many sheets with overview pages necessitate a structured approach that needs to be developed initially: elaborate and time-consuming to create, very late initial presentation, far more complex due to its interconnected nature, but very clear and relatively simple, and very quick and easy to modify. This aligns with my experience in PLC programming.
The program in the first-mentioned form is sold cheaply, but every subsequent change is very expensive. The program structured according to a concept is sold at a high price, and every change is implemented faster than the invoice can be written. All the cheapskates commission the first version. The significant advantage of the second version always becomes apparent with the first change. At the latest, the second change more than justifies the high purchase price, accompanied by only a short machine downtime.

#9

This piece is wrong.

#11

That’s why LibreOffice is a perpetual work in progress. Every hypocritical solution to one problem introduces ten new ones. It’s hardly appetizing.

#12

:thinking:
HYPOCRITICAL | English meaning - Cambridge Dictionary

saying that you have particular moral beliefs but behaving in a way that shows these are not sincere

#13

Wow, You guys are all over it! Thanks to everybody who chimed-in.

I think I’m out of my depth as far as a lot of the suggestions go, so I elected to keep it ‘simple’, do my formatting in C and just import the resulting CSV, I think the old addage is ‘do what you know’, so I did.

Yeah, this doesn’t require any more complexity than that and it works just fine, for me anyway.

So every day I do this: C:>bpcsv 130 83 67
Simple, three arguments, no formulae, just data.

It appends the entries to a file: bpcsv.csv
The formatted csv looks like this: Sun,11/02/2025,01:37PM,120,83,65

I don’t have to think about day, date and time, just the bp readings.
When I’m ready to save or print a nice formatted spreadsheet, I bring the data in and format the cells.

It really doesn’t need any more than that. But thank you all for your expertise, you guys are impressive!

Code: https://www.dropbox.com/scl/fi/f3yx33cqjl5cxsgo4cojs/bpcsv.c?rlkey=abgx65zck5i3seixj1trsixdd&st=wukz429k&dl=0

#14

Decision is yours. So you will use Calc as a tool for formatted output of text. (I would directly generate html or TeX-code instead.)
What i did in this cases, was importing in one (first) sheet, wich is referenced from a sevond sheet. =IF(Sheet1.A1<>""; A1;"") wich you can drag over the sheet. My purpose was usually using additional formulas, but you can also add your formatting in the second sheet.
.
So you have one sheet for your import and one for formatting.

PS:

No there is no command-line API, but it would be possible to start LibreOffice with accept-parameter to steer it from your c-code…

#15

Direct to HTML makes a lot of sense, but the doctor asked for a spreadsheet file (xls/xlsx, actually). I guess for compatibility with his records. The layered sheet technique is a new wrinkle for me, I’ll have to play with that.
.
I hardly ever use spreadsheets, so I’m not aware of all that can be done (and frankly, this particular application doesn’t warrant anything more than what I’ve outlined), but I’m amazed at the techniques mentioned in this thread, it’s certainly eye opening.
.
Thanks again, I’ll be back if I have more questions.

#16

For me that would be:
Open the sheet: One double-click
Select cell: Ctrl+End then Enter
Shift-Crtl-. (enters current timestamp), then Tab
130 Tab
83 Tab
67 Tab
Close sheet with saving.

No csv, no need for import etc. No formulas needed.

1 Like
#17

… make it a macro :wink:
Execute macro in Calc from terminal

Console command to break links to external files
#18
  • Call Tools>Options>Base>Databases and register my database under some name.
  • Open a (new) Calc document (odt).
  • Open the data source window (Ctrl+Shift+F4). It shows all registered databases with their tables and queries.
  • Drag icon of table “DATA” from the left pane onto your sheet.

You get a linked import range that can be refreshed with Data>Refresh while the cell cursor is in that range.
Keep the link in the original odt.
Save a copy in foreign file format whenever needed.

All that remains to be done is Data>refresh and File>Save As…

In order to remove my dummy data:
Tools>SQL…
DELETE FROM DATA

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