DATA TO .CSV FILE

[SILVESTROS]

Hello!
I would like to send a data string ( sensors data samples) with serial to usb module to pc , and then to Excel ..I send data to pc with UART component in serial port , but how can I convert data string to .csv to import in Excel (with Data Streamer). is there an FC example on how to do that?

[mnfisher]

The easiest way is to write the data to a file and then import it into excel.

Something like 'copy COM5 data.csv' at a command prompt should do it. Then out put datum1, datum2, datum3 from mcu use "\r\n" to put out an end line and start a new row in excel.

Alternatively use python and openpyxl to send data directly to a spreadsheet file.

Martin

[Steve-Matrix]

Another option is a Flowcode App Developer project.

Use a COM port component to receive the data and then write it to a CSV file. Or alternatively, write it to a chart component and you can see in within Flowcode (plus you can save as a CSV file by right-clicking the chart).

[SILVESTROS]

Thanks Steve , no data in Excel..is there an example ?

[Steve-Matrix]

There's no full example and it would depend on the nature of your data. But I've created a very simple chart in the attached project. If you simulate the program it will clear the chart, then add 5 data points and then enter a continuous loop. At this point, right-click the chart and select "show stored data". This will open a box which will allow you to view and export the data as shown in the image below.

You would need to do something similar, but it should be an App Developer app. And you would need to use a COM component to access the data coming into the PC.

chart example.png (144.44 KiB) Viewed 5822 times

[SILVESTROS]

thanks Steve , but I prefer not a chart, but as in the following tutorial

https://i0.wp.com/randomnerdtutorials.c ... =all&ssl=1

I'll use Data Streamer to insert CSV file to Excel...what is the format of data string that must be converted to CSV and imported to Excel, that contains sensors data , and date-time ? as example below...

-- Next is the properly formated string that Excel needs to show Date,Time,Wind speed,Wind direction and Temp. cl:send("XLS,write,Example,A"..counter..",%date%\nXLS,write,Example,B"..counter..",%time%\nXLS,write,Example,C"..counter..","..windSpeed.."\nXLS,write,Example,D"..counter..","..windDirection.."\nXLS,write,Example,E"..counter..","..temp)
above syntax is in lua...
data must record to Excel in real time .

[Steve-Matrix]

CSV format is essentially data separated by commas. If strings have spaces (or commas) then the strings need to be within quotes. There is no implicit formatting information in CSV, so the import process into excess will need to specify which numeric values are dates (etc). But that is a manual process.

If you want real-time input into Excel then an old technique is to use DDE but I don't know if that is still used and there are likely other methods too, but I've not looked into this for a long time. But this is beyond what Flowcode can currently do. At least not easily. There are ways of calling external DLLs from within a Flowcode project that could help, but I have little experience in doing that myself.

[chipfryer27]

Hi

I've not used Excel Data Streamer myself (yet) but as there have been a few questions on it recently it has got my interest. Biggest problem for me is that you need W10 and Office 365 and I use an older version of Office (licensed) and see no compelling reason to "upgrade". That said you do get a month's free trial, so it's my intent to look at it soon.

My understanding, and I could be wrong, is that within Excel you set a Com Port that Excel monitors for incoming data. The data is csv with a newline to indicate end of packet.

A PIC (or whatever) can easily send this via the UART, so could possibly connect via a TTL Serial-USB converter or if further away via a transparent link.

I may be wrong about the above but I intend to have a play to find out

Regards

[SILVESTROS]

many thanks for info,,,below is a tutorial where sensors data send to Excel using ESP8266 and within an windows app Things Gateway...codes is simple but are in language lua...I don't know that language but I think that is easy to convert to Flowcode ..that app needs only serial data and specific syntax of data string .
https://randomnerdtutorials.com/esp8266 ... -to-excel/

..I think that it is interesting to convert to FC mainly the code of second ESP8266 (that set as Access Point)..code is a few lines as follows...

-- Rui Santos - ESP8266 Server
-- Modified by Yves Arbour to ennable print string to go directly to an Excel sheet with Things Gateway

print("ESP8266 Server")
wifi.setmode(wifi.STATIONAP);
wifi.ap.config({ssid="test",pwd="12345678"});
print("Server IP Address:",wifi.ap.getip())

sv = net.createServer(net.TCP)
sv:listen(80, function(conn)
conn:on("receive", function(conn, receivedData)
--print("Received Data"..receivedData)
print(receivedData)-- string "Received Data" removed...
--Things Gateway ignores strings that do not start with proper command...
--XLS in this case for Excel sheet
end)
conn:on("sent", function(conn)
collectgarbage()
end)
end)
,

I've set a ESP8266 as AP (demo FC) ...connection with serial port of PC is ok...I use a serial to usb module...if above conversion is not possible
I'll try to convert serial data to CSV file and send to Excel..how can I do that , with what component ? DSP component ( CSV in/out) can do that, and how can I handle in FC? ..an example in FC to convert a data string that sent to PC to CSV will helps .

[chipfryer27]

Hi

After a bit of faffing about I managed to install Excel 2016 on my W10 machine. This does not come with data streamer but you can download it from Microsoft. You MUST be using W10 or newer.

Once installed, when you open Excel you should then see a Data Streamer tab on the ribbon. If not go to File>Options>Add Ins, then go to Manage Options and select Coms then click "Go". You should then see Data Streamer in the list. Make sure it is selected.

For testing I had my PC and laptop connected using two Serial-USB adapters connected together (Tx-Rx) running 9600 8-1-N and everything default.

My laptop had Terminal running but any should work (e.g. PuTTY).

The format of the data stream is CSV followed by a Carraige Return to signal the end of packet.

In Excel I wasn't interested in creating nice fancy charts, only to see if it worked.

Clicking the Data Streamer button on the ribbon brings up the DS Header with a few numbered options. 1-Data Sources, 2=Data Steaming and 3=Data Recording.

Clicking Connect a Device brings up your available options. I selected my Serial-USB device which then brought up a window informing of my choice and to click Start Data to begin recording.

Clicking Start Data changes the sheet to the below. This is default, customise to suit your application. You can see it is a 11 x Columns (Time Stamp + 10 fields) by 15 x Rows.

DS-1.jpg (61.7 KiB) Viewed 5774 times

It is now awaiting your data. It is worthwhile formatting cells to suit your data, I chose Number to two decimal places.

As mentioned it uses CSV with CR to signal the end of packet.

In Terminal (remember to connect, won't tell you how long it took to remember that) I first sent 1 + CR, then 1,12 +CR then 1,12,123.45+CR

Terminal-1.JPG (95.02 KiB) Viewed 5774 times

and got the following

Captured Data.jpg (60.33 KiB) Viewed 5774 times

So it is quite simple really to use.

At the bottom is a Data Out tab and any data you enter here will be sent to your connected device immediately as CSV. You will need accept and process within your MCU though.

I'll knockup a FC chart later and test when I'm in front of hardware, but I'm not expecting much problems.

Regards

EDIT
In TimeStamp above, the default cell format was selected which is showing elapsed time from starting to record. You can change that to 24Hr (for example) and it gives you the time based on OS time (e.g. 13:06:52).

Edit (again)
Created a little chart to post data from a DHT11 to Data Streamer. Will post once I test, but that may not be until a day or two.