B4J Question Best way to read a csv file?

[Mark Read]

I have a SPS control system which generates a CSV file continuously and sends it to a remote PC. The new data is added to the end of the file as a block.

Spoiler: Sample

NH4_ISE_ZU;18.04.2017 14:51;1229,861;1;42843618791;0
HEIZ_SOLL;18.04.2017 14:51;60;1;42843618791;0
VISU_PV_1_SOLL;18.04.2017 14:51;0;1;42843618791;0
PV_1_2;18.04.2017 14:51;100;1;42843618791;0
pH_501;18.04.2017 14:51;11,30208;1;42843618791;0
T_411;18.04.2017 14:51;52,95139;1;42843618791;0
pH_502;18.04.2017 14:51;7,413195;1;42843618791;0
P2_IST_2;18.04.2017 14:51;0;1;42843618791;0
T_401;18.04.2017 14:51;10,40038;1;42843618791;0
T_402;18.04.2017 14:51;10,20037;1;42843618791;0
T_403;18.04.2017 14:51;17,90065;1;42843618791;0
T_404;18.04.2017 14:51;47,60172;1;42843618791;0
T_405;18.04.2017 14:51;53,40193;1;42843618791;0
T_406;18.04.2017 14:51;16,10058;1;42843618791;0
T_407;18.04.2017 14:51;14,30052;1;42843618791;0
T_408;18.04.2017 14:51;18,70068;1;42843618791;0
T_409;18.04.2017 14:51;53,90195;1;42843618791;0
T_410;18.04.2017 14:51;61,60223;1;42843618791;0
ZÄHL_GÄRREST;18.04.2017 14:51;0;1;42843618791;0
P4_IST_2;18.04.2017 14:51;658,2755;1;42843618791;0

On the remote PC I would like to have a B4J program read the file and put the data into an Excel file containing pre-prepared diagrams to show the data realtime.

My question is how to read the file:

1. Read the whole file each time and overwrite the data?

or

2. Read only the last block and add the data to the bottom of the excel worksheet.

I know how many readings are in a block. The data is acquired roughly every 10 seconds for a period of 2 hours. Which method would give the best result?

Many thanks

 

[eps]

Can you copy or flip it?

So that you are reading a facsimile - so you'll be just behind the curve, but at least it means that you "can't" read part written records.

 

[Mark Read]

Can you copy or flip it?

So that you are reading a facsimile - so you'll be just behind the curve, but at least it means that you "can't" read part written records.

Sorry eps, I have no idea what you mean.

In my last post I said, my program works only if the existing excel file (template) is not open. If it is closed then all is well. Unfortunately, this is not the case which I need.

I need to have my excel file open and be looking at the pre-formatted charts on one sheet. The B4J program should fetch the SPS-data and parse it into the data sheet in my open excel file. I can then see the changes immediately.

 

[eps]

You have file 1 and file 2.

File 1 is written to.

File 2 gets created or updated by copying File 1 (after it has been written to).

Your App or whatever uses File 2.

That should minimise the time that the file is unavailable.. As File 2 is never open - but will get copied to or replaced.

 

[Mark Read]

I am still not with you!

Situation now (windows PC):

1. SPS-Control (a remote system) generates a horrible csv file which is not readable with Excel and sends via usb or internet to my PC.
2. My B4J app copies this file to my excel directory to prevent read/write collisions. It opens the copy, parses the data into the sheet "data". A second sheet in the same file is full of diagrams preformatted for the data.
3. After a certain interval eg 10 seconds, the process is repeated from step 1.

I want to have the excel file open and be looking at the diagrams whilst the data is being updated. This does not seem to be possible, as the file must be closed to update it. Unless I have missed a fundamental point in the jpoi library.

I cannot copy a modified excel file (file 1?) to an open excel file (file 2?)!

 

[eps]

Ah right - sorry I thought the problem was that you couldn't read it whilst it was being written to...

I think I understand now.

Can you not have a separate Excel/CSV File which is open and references the data in the First CSV file, which is closed...?

There's definitely a way round this.

 

[OliverA]

I want to have the excel file open and be looking at the diagrams whilst the data is being updated. This does not seem to be possible, as the file must be closed to update it. Unless I have missed a fundamental point in the jpoi library.

How about creating a data source for Excel and then use that data source in Excel (instead of writing the data directly into an open Excel spreadsheet). Then you should only have to hit refresh in Excel and Excel should update. If you use a database (such as MySQL) not a file, then you would have no open file issues. Am I making sense?

It looks like you can even tell Excel to refresh automatically at certain intervals: https://askdrexel.drexel.edu/app/answers/detail/a_id/2513/~/how-to:-refresh-data-automatically-at-regular-time-intervals-in-excel-2007.

Edit: Ninja'd by @eps . Please note, if you are doing an auto refresh, you want a database based source not a file source (else you'll have conflicts again).

 

[Mark Read]

It looks like you can even tell Excel to refresh automatically at certain intervals.

Correct! Been there and done that!

I have a work around which I will try next week:

Instead of writing to the excel file, I will write a standard ASCII file which I can import into excel. This new and proper ASCII file can be used as a data source for excel and at a certain interval read into the data sheet. This is not complicated as I have done it before. No programming is required, only a few commands from the menu.

Edit: Ninja'd by @eps . Please note, if you are doing an auto refresh, you want a database based source not a file source (else you'll have conflicts again).

Haha, is nothing safe these days!

I should not have any trouble with conflicts. The original file can be copied no problem. This copy is parsed by b4j in less than 2 seconds and the new excel source created. I would set excel to import/update every 10-15 seconds. Excel does not seem to have any problems reading a file which is already open. Had that problem before.

Can you not have a separate Excel/CSV File which is open and references the data in the First CSV file, which is closed...?

This might be a posibility. I will look into it.

 

[Knoppi]

Another possible solution is jCharts,
then you dont need an excel file
and you can do everything with b4j.
https://www.b4x.com/android/forum/threads/jcharts.71975/

but I have not tested yet.

is it possible to get a screenshot of your excel chart
and perhaps a complete data-set to test?

 

[Mark Read]

Another possible solution is jCharts,

Yes that did crosss my mind but I need to be able to make changes to the diagrams real time which would then not be possible.

is it possible to get a screenshot of your excel chart

The charts are not ready yet, as I have no REAL data to test. I only have data from another application which is similar. Therefore I am writing my code very generally so that it is easy to change.

 

[Richard O'Sullivan]

I had a similar request from a client recently and my solution was to write the data from the csv into an Access database.

Using Excel PowerPivot I created the DataModel Link to this Access database and was able to create a Business Intelligence Dashboard
with all the Charts and Graphs available to PowerPivot. A simple DataRefresh would populate the vertipaq table from the database and
all Charts updated. It worked real fast.

If you need any pointers just ask.

Richard