Writing to spreadsheet - append columns not rows

[DarkLordOfG]

Hello - I am trying to do something easy in concept but not obvious in programming.  I am acquiring data that is say 60K doubles long.  I want to write the data to a tab delimited file so it can be read by anyone.  Currently we take ~200 measurements per sample.  I want to write the data in columns not rows in a spreadsheet file.  Is there a way to do this one at a time (as the data is acquired) versus buffering up all the measurements and then writing the whole array?  I also do not want to have to read the file each time because as it grows the performance of the loop is affected.  Any thoughts?

John

[GerdW]

Hi John,

files are usually written from start to end - in your case adding new data will add a new row to the file...

You want to insert into the file, which means either:
- loading old data, convert to array, add new column, write to csv file (getting slower with each iteration)
- use buffering to hold all data in memory and write just once at the end of DAQ

Ok, nothing new to you...

But what about this (when you really insist on writing to file in each iteration):
Write the file as described above adding new rows each iteration. When DAQ has finished you read in the whole file, transpose it's content and write back to (maybe a new) file... So you have all values on disk at each time and also get a file with data in columns...

But keep in mind:
Writing to file takes time - much more time than to hold 60k of values in memory. Unless you use some kind of FPGA/embedded/whatever a usual PC should have enough RAM to hold those values...

[BCL@Servodan]

Hi John.

If I understand Your task correctly, You want to write a tab delimited file with 200 Columns, and 60K Rows.

Is this correct???

[DarkLordOfG]

Yes that is correct.  Think of writing a spreadsheet file just in column appends instead of row appends.  I currently buffer and write at the end.  Problem is there is a noticable delay when this is done.  It just doesn't seem like "good" programming to tell the user to wait while I dumped the data.  They always ask - why can't you write it as it is taken.

John

[GerdW]

Hi John,

tell your users "Writing that amount of data takes time!" To make your current approach a "little bit better programming": show a progress bar while saving

Well, there's a way to write as it is taken - but this is complicated, takes a lot of processing time and needs some conditions:
Conditions are:
- knowing number of samples (rows & columns) in advance
- using a fixed-length data size for saving, for instance saving all numbers in exactly 16 chars (including TAB or CR).

Create an empty file at start. While measurement you can calculate the file offset to store the actual number, store exactly the number, calc next offset, store next number, and so on...
But again: heavy disk usage...


Message Edited by GerdW on 04-30-2008 04:30 PM

[DarkLordOfG]

Gerw,

Ah - the users never can seem to comprehend that storing data takes time!  Oh well, I now know why I despise file writing.  Long term we are going to a SQL DB.  So they will have to live until that is ready.  Thanks for the  replies.

John