Thread: editing a csv file using a macro help

I havent found the macro capability of libre office to be that great. I could help much more if it was in excel. Does it have to be in libreoffice?

Do you have a worksheet you can post up for people to have a look at for you?

theres a way of doing it excel where you just import the data one chunk at a time and it will append the time log, the problem is...

if you go over the 24 hour day you might wind up with some funky numbers and date management issues.

its also a manual process but it should be automateble by modifying the converter to read the time date column as a sequence rather than a list... (as in row 2 = row 1+ the value in row 2, row 3 = the value now in row 2 + the value in row 3. yes I know that sounds like a rcurring loop but there is away of doing it on import so it only process each line once and in order.)

As Disco-tastic suggests posting an example file up here would be useful.

I can think of a number of approachs, but they may or may not work depending on the time field format and reading rate, so having an actual file to tinker with would be great. Is your time field formatted as minutes.seconds or decimal minutes and are the readings within a session regularly spaced in time?

Cheers

Steve

Originally Posted by Disco-tastic

I havent found the macro capability of libre office to be that great. I could help much more if it was in excel....

Yeah. kind'a.
Don't have Office anymore(and TBH don't think I need it).
I once tried Open Office (proper) and it ruined my (important) xls and xlsx files. Libre Office has been no issue for me since I started using it.

I've converted two of the log files into a single xls file.
At line 94 of the sheet is where the time changes.
The original second sheet had a header row as well, but I delete them as it confuses things, so you'll see the time(column) rises up to about 24.54 and then at line 94 is the start of the second log at about 5.09 again.

I generally get about 200 log files per month, and I merge them all with the copy command(quick and easy) and make one large csv file.
I didn't see any point in continuing to add more data and inflate the size of the xls file(note that odf is so much smaller for many spreadsheets by comparison too!).

So the basic idea is where the time reaches 24 on line 93 then changes to 5.09 on 94 .. I'm trying to add 5.09 to 24.54(ie. 29.63) and continue on recursively adding the time values to the previous one to get a continuous time line.

If I don't do this, merging more than 2 data sets into a graph becomes messy where the graph then fold back onto itself and makes it impossible to read the data.

ps. the time value is not a time value as in day date or actual time.
This is my biggest regret and complaint about the 1Gauge, in that it's great that it logs the sensor data, but it would have been greater if it could put that sensor data into some sort of context.
So the time value is a simple number value only, which I assume is in seconds.

And I just realised another problem with what I wanted to do.
If I simply add the value of the next cell below to the previous cell, it stuffs the actual time constant up.
What I actually need to do is to add the time values of the next sheet to the final time value of the previous sheet.
The csv files are all named coherently LOG0001, LOG0002, etc.

How much manual work do you currently do importing the values? Could you add an extra column which just adds the times together, and use that as your horizontal axis?

I've attached a file showing what I mean. It was writtent in excel, so you may need to translate into Libre-speak.

Cheers

Dan

Looking at the files, I can't suggest anything more elegant that Dan's formula of comparing the readings and adding the previous reading or the difference.

The only other thought would be to have a dummy time column. The time difference between readings is always either 0.21 or 0.22, so you could also populate a column with a start reading of 0 and a sequence of 0.21, 0.42, 0.63 etc

Cheers

Steve

Originally Posted by Rok_Dr

.... The time difference between readings is always either 0.21 or 0.22, so you could also populate a column with a start reading of 0 and a sequence of 0.21, 0.42, 0.63 etc

Cheers

Steve

Thanks for the help guys.
That was one of the things I was thinking too, but even more simple with rising integer values starting at 1 .. 2, 3, etc.

Work done to import the values is minimal.
Only really the time taken to type: copy *.csv file1.csv in the command prompt.
This amalgamates however many csv files into one large one.
The number of files varies but can be as many as 300 or more files per mth.
Work to append all those files into one large file is about 30sec.
Then I open in a text editor just to remove all the header rows using find and replace(delete), another 10 sec or so.

I'll have a go at Dan's method and see if it'll work in Libre.

ps. the other reason I'm doing this is not just to get the data into a usable format, but also to expand my knowledge a little further(ie. a bit of programming know how never hurt anyone! )

Oh! .... and of course the alternator on the Tdi decided to take it's long service leave an hour or so ago and all the shops are closed/closing and don't even know what a Landrover is!

Shop assistant: Landrover! how do you spell that.
Me: L for Larry, A for apple, N for November ...
Shop assistant: What model is it?
Me: 300 Tdi diesel, 2.5 Lt turbo diesel 98 model Discovery.
Shop Assistant: is that a V8?
Me: .. err, I think so
Shop assistant: Doesn't look like we have them in stock, but I can order one in.
Me: I'll get back to 'ya.

Anyhow. once again, thanks for the help so far.
I'll get back with any results.

If you're happy doing that them I'd see if the extra column works and make a template out of it. Import your csv files as per above, then copy the values into the template. The extra row will calculate itself and your graph should update automatically it will take an extra 5 secs.

If you're really keen on learning join a site like [URL = "http://stackoverflow.com/q/11638170/5266309"]Stack Exchange.[/URL] there is heaps of info on that site