Shop OBEX P1 Docs P2 Docs Learn Events
Excel FTDI Serial Data Logger — Parallax Forums

Excel FTDI Serial Data Logger

BeanBean Posts: 8,129
edited 2019-11-12 01:15 in General Discussion
There seems to be some interest in logging serial data into excel.

This is an Excel sheet that will log serial data from an FTDI chip which is used in Parallax USB-To-Serial devices.

Any questions please ask.

I have posted version 1.1 of the data logger.
I have posted version 1.2 of the data logger.

Bean

Comments

  • Bean,
    Thanks very much.
    This is all that is needed to capture csv serial streams into Excel for the Prop user.
    No need to mess around with the unreliable Microsoft Excel Data Streamer!
    I look forward to seeing some really impressive data presentation graphs and data management using this tool with Excel.
  • BeanBean Posts: 8,129
    Thanks, I am already working on the next version which will allow receiving binary data (don't have to convert to ASCII).

    CHR(1)CHR(x) = Log "x" as unsigned byte value
    CHR(2)CHR(xh)CHR(xl) = Log "x" as unsigned word
    CHR(3)CHR(xh)CHR(xl) = Log "x" as signed integer
    CHR(4)CHR(xmsb)CHR(xhl)CHR(xlh)CHR(xlsb) = Log "x" as signed long

    These binary values do not require a TAB CHR(9) after them, it is assumed.

    This is tricky because the binary data could contain a CHR(13).

    Bean
  • WBA ConsultingWBA Consulting Posts: 2,933
    edited 2019-11-10 23:15
    Excellent work Bean! Checking it out now and so far doing exactly what I need, PERFECTLY, every connection. No more hokey Excel or PLX-DAQ mess.

    Comments:

    The "=Now()" operator in Excel will update itself if the sheet recalculates (IE: opening the file later will show the later timestamp when it is opened)
    When I use that function in macros at work, I immediately select the same cell then do a "Copy" and "Paste Values" so that the timestamp becomes text instead of a formula, so I modified your VBA code this way:
    If fieldStr = "[TIME]" Then
              Cells(row, col) = Now()
              Cells(row, col).Select                                                              edit by WBA
              Selection.Copy                                                                       edit by WBA
              Selection.PasteSpecial Paste:=xlPasteValues                           edit by WBA
            Else
              Cells(row, col) = fieldStr
            End If ' fieldStr = "[TIME]"
    

    Also, if a knucklehead like me uses this incorrectly a few times, the timestamp changes the cell it lands in to a date format, so when you dump data in that cell on the next "start" run, a simple decimal "88" from the Prop will show up as 3/28/1900, LOL. Anyhow, that's my mistake obviously, but one way to prevent that is to change the Clear Contents portion of the Start sequence to a Delete Rows function, so any formatting from the previous data collection gets wiped out.

    Anyhow, beautiful job Bean and thanks so much for taking this on. Your VBA skills are impressive.
  • BeanBean Posts: 8,129
    The code puts the constant value of "=now()" into the cell.
    It does not put the "=now()" function into the cell.
    The value should not change when recalculated, unless there is something I'm missing.

    Anyway, here the a new version that allows binary values.

    Bean
  • Just tried out the latest version and yes, it is inputting the value of Now() as opposed to the function as I stated. I am not sure why my first runs were planting the function in the column, but working fantastic otherwise. I will try out the binary functions this week as well.

    The only issue I have is that I cannot get it to work unless I program my board at least once after I plug it in. If I take my programmed board that works perfectly dumping into excel and unplug it, it will not work when plugged back in. I either get random garbage data in Excel every 15-20 seconds or Excel will hang until I unplug the board (and then one row will have a long stream of junk data). This occurs on both Win7 Enterprise at work and Windows 10 at home. I am currently using a Propeller ASC+ with an FT231XS. The FT231 is slightly different than the usual FT232 used by most, so I wonder if that has something to do with it. I will try out a different board that uses an FT232.
  • Is there sample software that outputs data, for either BS2 or Propeller, that I can try this with?
  • WBA ConsultingWBA Consulting Posts: 2,933
    edited 2019-11-11 23:20
    Well, I have just been playing around with chunks of sample code, but here is a super simple demo using FullDuplexSerial and the RealRandom object in the Propeller Library. It uses the defaults that Bean has set up in his file (IE: only 10 records). Load this into a Prop and then hit the Start button in his file within 5 seconds. You should get output like the first attached "000" picture. In the "002" picture, I changed cell B5 to have the same formatting as cell B6 so that the 27 displays correctly and columns were widened to 145 pixels to fit in the timestamp. In no way does it truly show the power of Bean's work, but it does show the simplicity of using it.

    Bean, cell B5 in your last revision is formatted as a date field, so my "27" shows up as 1/27/1900. If I simply copy the format from cell B6 before I hit start everything works perfectly.

    Full code archive attached, but here is the entire program:
    {{
          Quick and dirty demo of Bean's FTDI_Data_Logger_1_1.xlsm
          Uses the default settings in the spreadsheet
          Some columns may need to be widened in Excel to show the full data
          WBA Consulting, 11/11/2019          
    }}
    
    CON
    
      _clkmode = xtal1 + pll16x
      _xinfreq = 5_000_000              '5 MHz clock * 16x PLL = 80 MHz system clock speed
    
    VAR
      'Globally accessible variables
      
      
    OBJ
      serial        : "FullDuplexSerial"
      rr            : "RealRandom"
      
    PUB Main
    
      'start the FullDuplexSerial object
      serial.Start(31, 30, %0000, 9_600)                    'requires 1 cog for operation
    
      'start RealRandom
      rr.start
    
      waitcnt(cnt + (5 * clkfreq))                          'wait 5 seconds for the FTDI_Data_Logger_1_1.xlsm Start button to be clicked
            
     ' Send 10 sets of data spanning 5 columns
      Repeat 10
      
         serial.Str(STRING("[TIME]"))             ' put a timestamp send as ASCii string
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Str(STRING("27"))                 ' send the number 27
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Str(STRING("Propeller"))         ' send "Propeller" as text
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Dec(42)                           ' use serial.dec to send the number 42
         serial.Tx(9)                             ' send a tab CHR(9)     
         serial.hex(rr.random, 8)                 ' send a randomly generated number in hex
         
         serial.Tx($0D)                           'print a new line
    
          waitcnt(cnt + (1 * clkfreq))            'wait 1 second 
    
      serial.Stop                                 'Stop the object
    

  • BeanBean Posts: 8,129
    I will fix cell B5 for the next upload.

    Also I don't know why you have to program the board before it will work ? Maybe the CTS or RTS line is not set properly ?
    I will put a "RESET" button on the next release, maybe that will fix it.

    Bean
  • BeanBean Posts: 8,129
    I have posted version 1.2 that has a "RESET" button.

    Bean
  • Bean and WBA,
    Thank you both for your selfless continuing development of the rock-solid FTDI Excel csv serial data logger.
    Your contribution really is a breakthrough open application for Prop 1 users, and I am surprised there is not more interest.
    I would have thought the Prop1 education sector would be all over this! Students can capture their project data straight into Excel and use it for debugging, improvements, presentation and so on!
    Although the Microsoft Data Streamer is very unreliable, at least it has a useful data capture page, with the following features;
    - a row presenting the current captured serial stream.
    - a rolling "data window" consisting of a range of previously captured serial stream rows which scrolls with the last captured stream on the top row.
    This is very useful for visualizing, graphing and saving the entire scrolling "data window" using normal Excel functions.
    Do you think this is a useful development direction? Does it involve a lot of complex VBA coding?
    I was going to give it a try, but I am a real VBA novice and I suspect it will be too much for me.



  • BeanBean Posts: 8,129
    I have added (for the next version not posted yet) the status line at the bottom of the sheet will show the current serial line being evaluated.
    Scrolling data is not easy unless it is a limited number of row (maybe 100) because each cell must be moved (unless there is a function I don't know about).

    Thanks for the suggestions.

    Bean
  • Bean,
    Thanks, your status line showing the current serial line being evaluated will be a useful improvement.
    For the scrolling "data window", I was going to use the same "max lines" cell variable you use in your latest version (say max lines = 100), then incorporate the simplified VBA bulk copy function within the same Sheet, something like;
    Range ("A5:J104"). Copy Range ("A6:J105")
    just before the current serial line is transferred into row 5.
    This assumes a scrolling range from cell A5 to cell J105, i.e. 100 rows and 10 columns.
    The columns would be fixed, or also set by cell variable, say "max data channels".
    Is this worth trying? I may be wrong about the simplified bulk copy - I saw it on a VBA training spruik webpage.
  • WBA ConsultingWBA Consulting Posts: 2,933
    edited 2019-11-12 17:45
    Bean, 1.2 with my ASC+ and a Quickstart do the same thing. I did find out that I only need to do a "Identify Hardware" with the Propeller Tool to get it to work, not actually program anything. That's really odd.
    Otherwise, 1.2 is working perfect. (and I don't think the issue is with your VBA, but rather something with what the FTDI DLL is doing)

    As for the scrolling data topic, you could have the VBA code insert a row at the top of the data and the newest data always goes on row 5. Then, if you want to graph the last 20 data points, you just have the graph reference rows 5-24. But....
    You would need to convince Excel to not update formulas when you insert the row, which is close to impossible. (IE: a reference to B5 would get updated to B6 after the insert, but you want your graph to be from the same rows always) To get past this, you can use the Indirect function within Excel to address that. It's a little more Excel work with the formulas, but you only need to set it up once for however you are using Bean's sheet.

    Also, with Bean's added status line at the bottom of the sheet, you could harvest that for the indirect function to grab the most recent group of rows. So there are some options.

    I didn't get to play with this much tonight, I spent my free time working on an RPi/ESP8266 MQTT setup. Surprisingly, when from nothing to a working setup all in one night. That never happens.... I may see about making some code that will provide data usable for a graph tomorrow on a new tab of the sheet to try out my above ideas.
  • WBA ConsultingWBA Consulting Posts: 2,933
    edited 2019-11-16 08:59
    Sorry for the delay, but here is a Graph demo using Bean's FTDI sheet. I added one line of VBA code to give me the current row number. This allows me to leverage the use of Indirect references so that the graph always shows the last 10 readings.

    The SPIN code creates a data set every 2 seconds that includes a Timestamp, the chart title, logging number (used for axis labels), series names, and two "readings" from the real random object just to give the chart something to show. There is a 5 second pause at the beginning to allow you time to hit start on the DTA tab then switch to the GRAPH tab to watch the magic happen.
    Leaving everything else as defaults, this code and Excel sheet will create a graph of the last 10 data points. The code and sheet attached is setup for only 10, but you can change the Repeat line in the code to 20 and cell B2 on the DATA tab to 20 to see how the graph follows the latest 10 records.

    FYI: The project archive includes the modified data logger Excel file with the added GRAPH tab.
  • This is one of those "DRAT! I wish I thought of that first scenarios.", and I know @Bean you are considering responding, "Because I did!". I'm going to try these examples over the course of the few weeks and come back with a good understanding of what works for me, and what does not. (Probably nothing.) And then post a good review.

    ---
    Mascot occupied. Please leave your messages with the Yak and then with the Yeti.
  • Okay, I had some free time yesterday, and I did indeed try the variant that you, @"WBA Consulting" contributed to @Bean 's good idea. It worked exactly as described.

    In fact, I first ran the Spin code in Ram on the QS that I selected for the job. Worked fine until I pressed the reset button on it, and found it was running the regular example when the thing is first connected. So I sent the Spin code to it, and chose the run from EPROM one, and off it went.

    Both of you have a great idea there. But a suggestion, add one option that can be enabled at the time it is loaded into the editor, the option is that of the user could enable the board to accept digital data from the user's external hardware examples. Of course given that the Prop uses 3V3 logic and all of mine is TTL stuff at 5V, so I would need to insert a level shifting tool between them. Just a thought, and I do not expect it to be offered.
  • Both of you have a great idea there. But a suggestion, add one option that can be enabled at the time it is loaded into the editor, the option is that of the user could enable the board to accept digital data from the user's external hardware examples. Of course given that the Prop uses 3V3 logic and all of mine is TTL stuff at 5V, so I would need to insert a level shifting tool between them. Just a thought, and I do not expect it to be offered.

    The demo I created is just a demo and can be modified for all sorts of things very easily, but since you are asking, I am led to believe that something may need to change within the VBA code to get what you are after. Unfortunately, I am not quite following your question, so I could use some clarification. Do you mean that a demo that simply logs the state of some input pins that are being fed from an external device would be beneficial?

  • Buck RogersBuck Rogers Posts: 2,160
    edited 2019-11-23 00:40
    Both of you have a great idea there. But a suggestion, add one option that can be enabled at the time it is loaded into the editor, the option is that of the user could enable the board to accept digital data from the user's external hardware examples. Of course given that the Prop uses 3V3 logic and all of mine is TTL stuff at 5V, so I would need to insert a level shifting tool between them. Just a thought, and I do not expect it to be offered.

    The demo I created is just a demo and can be modified for all sorts of things very easily, but since you are asking, I am led to believe that something may need to change within the VBA code to get what you are after. Unfortunately, I am not quite following your question, so I could use some clarification. Do you mean that a demo that simply logs the state of some input pins that are being fed from an external device would be beneficial?

    Yes as matter of fact. That is indeed what I was thinking of. I believe I did frame it rather confusingly. I did try to do that with the Stamp and the plotting thing that was written back in the day for Windows from last century. Which never came off the right way around, the BASIC code worked, but the stuff for Excel did not seem to understand it.

    --
    Mascot offers, "Life needs more sleep. Wake me later.", said as he goes off for a nap.
  • Hi
    Hope this comments fits in with this topic.

    I have 2 or more RFID readers, all identical. All USB on the sing HID to place data in Excel 2016. However all come in to the same column of spreadsheet with no identification as to which USB device it came from. Only way to id is to know which was the originating port on the tree.

    As each read comes thru it would be tagged (prefix or suffix or separate column) with the device number position or such. From there can be manipulated in Excel.

    Can anyone head me in the right direction using PLX latest 32 bit.
    Thankyou
    Charles Harris
  • JustinTehJustinTeh Posts: 1
    edited 2021-04-27 01:16

    Hi @Bean,

    I am currently using the ESP32-Camera for some maker projects as part of the course work too. However, I am currently faced with a "Run-time Error 53: ftd2xx.dll not found", and while I have tried finding means to change the file path of the Lib Clause to locate where my "ftd2xx.dll" file is in my laptop, as I am unfamiliar with Microsoft Visual Basic language, it has not been very fruitful.

    Hence, may I kindly request any advice on how I might be able to resolve this error? Thank you very much for your time in advance. J!

    Additionally, I was able to locate the exact file path of the physical dll file under "C:\Windows\System32\DriverStore\FileRepository\ftdibus.inf_amd64_49b3e24305b20ada\i386". Therefore, I am just interested in editing the file path on Microsoft Visual Basic's debugging window.

    Justin

Sign In or Register to comment.