Shop OBEX P1 Docs P2 Docs Learn Events
Write a variable to SD >csv file — Parallax Forums

Write a variable to SD >csv file

AGCBAGCB Posts: 330
edited 2014-05-21 05:03 in Learn with BlocklyProp
It's time to ask for help. I've been trying for 3 days to figure out how to write a variable to a SD .csv . I have no problem writing a string of comma separated values and then displaying them on MS Excell. However I would like to substitute variables for the values. I have looked at many threads and objects and believe I have done my home work but still fail to get anything but gibberish on the spreadsheet for the variables.
Here is my code
CON

  _clkmode = xtal1 + pll16x      '80 MHz system clock
  _xinfreq = 5_000_000

  DO  = 0
  CLK = 1
  DI  = 2
  CS  = 3 
           
OBJ
 sdfat : "fsrw"
 pst   : "parallax serial terminal"  
  
VAR
byte  hour,minute,day 
PUB  demo  |  mount
  pst.start(115200)
  waitcnt(clkfreq+cnt)
  day:=1
  hour:=33
  minute:=12
  mount := \sdfat.mount_explicit(DO,CLK,DI,CS)
  if  mount < 0
    pst.str(string(13,"failed to mount"))
    abort

  pst.str(string(13,"SD card was found and mounted fine",13))
  sdfat.popen(string("tryelev.csv"),"w")
  
  
  sdfat.pputs(string("AGG,PJG,JRG,TLJ,Tom", 13,10 ))              'works good up thru here
  sdfat.pwrite(day,2)
  sdfat.pputc(",")
  sdfat.pwrite(hour,2)
  sdfat.pclose
  sdfat.unmount  
   
Once I can do this I will substitute variable values from a RTC and TC74
Thanks
Aaron

Comments

  • Duane DegnDuane Degn Posts: 10,588
    edited 2014-05-03 08:35
    You need to convert the values you want written to ASCII characters.

    Here's one thread with some explanations. Post #3 has an example of creating a numeric string to use as a SD card file name. The same principle applies to strings you want to write to the card.

    The "Dec" method of many serial objects are other examples of converting numbers to ASCII characters. Make sure and read about ASCII on Wikipedia too.

    The jest is, numbers and characters are stored and transmitted differently. The number zero held in a variable has all it bits set to zero. The character "0" is stored and transmitted as the number 48. While a single byte can contain a number between 0 and 255, the same byte used to store a character can only hold a single digit. If you were to send the number 13 on the serial line to the terminal, if you sent the raw number as a single byte you'd get a carriage return. If you sent "13" as characters "1" and "3" you'd need to send two bytes with the values 49 and 51. 49 and 51 are the ASCII values for "1" and "3". Notice 49 (48 + 1) and 51 (48 + 3) are the ASCII value of zero (48) plus the numeric value of the character.

    You need to use these same ASCII characters when you write to the SD card for Excel to be able to read them.
  • AGCBAGCB Posts: 330
    edited 2014-05-03 09:05
    Thanks Duane
    I'll study that out. BTW you have helped me numerous times before and I look for your posts as I read others questions. Thanks for taking the time to help people!
    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-05 09:54
    I don't understand the parameters in the fdec method of StrFmt. Comments are non existent!
    fdec(ptr, value, len, dp) | p, fnumbuf[4]
    .
    

    ptr is ??
    value is ??
    length is the total length of output string ??w/ or w/o decimal places
    dp is the number of decimal places

    Fill me in where I'm missing or just plain wrong. This may lead to other questions.
    Thanks
    Aaron
  • Duane DegnDuane Degn Posts: 10,588
    edited 2014-05-05 12:29
    AGCB wrote: »
    I don't understand the parameters in the fdec method of StrFmt. Comments are non existent!
    fdec(ptr, value, len, dp) | p, fnumbuf[4]
    .
    

    ptr is ??
    value is ??
    length is the total length of output string ??w/ or w/o decimal places
    dp is the number of decimal places

    Fill me in where I'm missing or just plain wrong. This may lead to other questions.
    Thanks
    Aaron

    It took me a bit to figure this out. There's an example of using the method in post #3 of the thread I linked to above.

    "ptr" is the address of the buffer where you want the string stored. In the example I gave this was initially the first byte/character in the buffer "FileName".
    result := @FileName ' "result" could be any temporary variable at least a word in size
    

    This buffer can be a local temporary buffer. "value" is the variable you want converted to ASCII characters., "len" is the number of characters you want to output. "dp" can be used to add zero padding if you use a negative number or it can be used to display decimal points if you use a positive number. I think to get "normal" output, set "len" to the largest possible size the number can be (11 should be the largest you'd need) and set "dp" to zero.

    The method returns the address of the next byte/character in the buffer where the characters had just been stored.


    In the example (as shown in the above code block), "result" started as the address of "FileName".

    The value of "result" will change after the first call to "fdec":
    result := Format.fdec(result, month, 2, -2)
    

    The value of "result" will have been increased by two since two characters were added to the original pointer.

    The remaining calls to "fdec" continued to increase the value of "result". Keep in mind the value of "result" is the location in RAM where the next byte/character will be stored.
    result := Format.fdec(result, date, 2, -2)
    result := Format.fdec(result, hour, 2, -2)
    

    So now which address location does "result" point to?

    To find out, you can add this line to the code in post #3 (make sure it's after the calls to "fdec").
    term.str(result)
    

    Once you have your number stored as ASCII characters in a buffer, you can use the SD object's "str" method to write the string to the card in a similar way the string had been sent to the terminal.

    Do you need help turning the example code into a working program to display the string "FileName" and the string stored in location pointed to by "result"?
  • AGCBAGCB Posts: 330
    edited 2014-05-05 13:31
    Thanks again Duane.
    I think I'm getting it. I don't have much time left today and will be out of town for a couple days but I will write my code with what I understand and let you know of my success or else additional wonderments as soon as I can and I will post what I have.
    Thanks again
    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-06 03:45
    I've played w/ the fdec method and written a code to help myself learn what's going on and I think I have a good handle on it. Thanks Duane for your tutoring.
    CON
    
      _clkmode = xtal1 + pll16x      '80 MHz system clock
      _xinfreq = 5_000_000
    
     
    
    OBJ
      pst : "parallax serial terminal"
      fmt : "StrFmt"
    VAR
      word  hours,minutes
      word  tempo 
    PUB  name
      pst.start(115200)
      hours:=%0000_1011 'hour
      minutes:=%0000_0111
      
      pst.str(@filename)      'displays 00000  or however many places there are in @FileName              
      tempo:=@FileName
      pst.newline
      pst.hex(tempo,2)        'displays the RAM address of 1st byte
      pst.newline
      pst.str(@filename)      'still displays 00000
      
      tempo:=fmt.fdec(tempo,hours,2,0)
      pst.newline
      pst.hex(tempo,2)        'displays the RAM address of 2nd byte
      pst.newline
      pst.str(@filename)
      
      tempo:=fmt.fdec(tempo,minutes,2,0)
      pst.newline
      pst.hex(tempo,2)         'displays the RAM address of 3rd byte
      pst.newline
      pst.str(@filename)       'displays 11
      pst.newline
      pst.str(@filename)       'displays 11 space  7   the hours and minutes
      pst.newline
      pst.str(tempo)           'displays nothing
      pst.newline
      pst.newline
      pst.newline
      
      pst.char(byte[@FileName][0])       'displays 1
      pst.newline
      pst.char(byte[@FileName][1])        'displays 1
      pst.newline
      pst.char(byte[@FileName][2])        'displays space
      pst.newline
      pst.char(byte[@FileName][3])        'displays 7
      pst.newline
      'pst.char(byte[@FileName][4])       'causes display to blank   ????
      
       
    DAT
      mydata      byte "00000", 0     'adding this line increases the RAM address of tempo by $6
      FileName    byte "000000000000", 0 '(12) 2 ea. for yr, mo, hr, mn, sc, 1 for day + 6 spaces
                                             'I think fdec expects up to 14 bytes
    
    CON
    {{            This is what the PST screen looks like
    000000000000
    26
    000000000000
    28
    11
    2A
    11 7
    11 7
    
    1
    1
    
    7
    }}
    


    In my extremely novice mind, I think I would now look at something like the ASCII0 String object to build a string of comma separated values to write to the SD card but I'd like suggestions if there’s a better way to do this. Maybe just writing the individual characters to the SD with a comma between each so it can be displayed on an Excell spread sheet.

    Several ideas for using this are
    Record the times of hot water use in my house, the temperature of the water in the
    tank and how long it takes for the water to return to max temp (I heat entirely w/ wood)
    Record the amount and length of sunlight in my greenhouse
    Record the time of events of my homemade trail/security camera

    Thanks again
    Aaron
  • Duane DegnDuane Degn Posts: 10,588
    edited 2014-05-06 07:05
    I'm not familiar with the object "ASCII0 String". My guess is it's pretty much the same thing as the "StrFmt" method.

    You can add a comma to your "fileName" string by using the "ch" method of "StrFmt".
    tempo:=fmt.ch(tempo, ",")
    

    I'm not sure if you're aware that the various types of numbers, binary, hexadecimal, etc., is just for us human's convenience. The numbers are stored the same inside the Propeller.

    The following three blocks of code will produce the same compiled code and run exactly the same.
    hours := %0000_1011
      minutes := %0000_0111
    
    
    
    hours := 11
      minutes := 7
    
    
    
    hours := $B
      minutes:= $7
    
    
    
  • AGCBAGCB Posts: 330
    edited 2014-05-07 13:46
    Thanks again Duane.

    I do know the base makes no difference to the Propeller, I was just using the RTC output format.

    I'm sure that StrFmt is very useful but the total lack of comments makes it difficult for the novice.
  • JLockeJLocke Posts: 354
    edited 2014-05-08 06:31
    I posted some code over in the Projects forum that shows how I write to a .csv file on a micro-SD card in a data logger project. See post #3.
    My code is liberally commented; hope it helps you out.
  • AGCBAGCB Posts: 330
    edited 2014-05-08 15:37
    Thanks, I'll take a look at that.
    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-12 03:17
    Moving along but not there yet! Bare w/ me please.

    All works fine except that the write to sd card and that displayed on the PST are in decimal equivalents of the BCD (I think) values output by the RTC.

    I have tried multiple ways to convert these so as to get proper output but I have failed in all. I basically know what the problem is but don’t know how to fix it. I’ve also looked at the code provided in posts above.

    At the bottom of my code I have included the PST screen to show what I mean.

    Thanks again for your time.
    Aaron
    PUB  start
      pst.start(115200)
      I2C.start(SCLpin,SDApin,Bitrate)
      rtc.startx(sclpin, sdapin)
      waitcnt(clkfreq+cnt)
      
      main  
    PUB  main  |  mount
      repeat
        if  ina [25]
          
          I2C.read_page(I2C#RTC,Seconds,@RTCbuf,7) '(device,address,dataAddress,bytes)
          pst.newline
          pst.newline
            'display date/time on PST
          pst.str(string("current date and time in hours,minutes,seconds",13))  
          pst.hex(RTCbuf[Date],2)
          pst.char("/")
          pst.hex(RTCbuf[hours],2)
          pst.char("/")
          pst.hex(RTCbuf[minutes],2)
          pst.char("/")
          pst.hex(RTCbuf[seconds],2)
          waitcnt(clkfreq+cnt)
          
                'Here is where the variables are ASCII-atized
          tempo:=@timebuf                'give tempo beginning RAM address
          tempo:=fmt.fdec(tempo,RTCbuf[date],2,0)     'convert date to ASCII and put in RAM address
          tempo:=fmt.ch(tempo,",")          'add comma
          tempo:=fmt.fdec(tempo,RTCbuf[hours],2,0)   'convert hour to ASCII and put in next RAM address
          tempo:=fmt.ch(tempo,",")
          tempo:=fmt.fdec(tempo,RTCbuf[minutes],2,0)  'convert minute to ASCII and put in next RAM address
          tempo:=fmt.ch(tempo,",")
          tempo:=fmt.fdec(tempo,RTCbuf[seconds],2,0)  'convert second to ASCII and put in next RAM address
           
          repeat  7
            tempo:=fmt.ch(tempo,",")    '7 commas to pad remaining unused bytes w/ spaces   
           
                  'write to SD card
          mount := \sdfat.mount_explicit(DO,CLK,DI,CS)
          if  mount < 0
            pst.str(string(13,"failed to mount"))
            abort
           
          pst.str(string(13,"SD card was found and mounted fine",13))
          sdfat.popen(string("wrttim.csv"),"w")       
          sdfat.pputs(string(13,13,"Date,Hour,Minute,Second", 13,10 )) 'Title line for Excel
           
          index:=0
          repeat  index from 0 to 14
            sdfat.pputc(byte[@timebuf][index])    '1 byte at a time comma separated
                                                                    
          pst.str(string(13,"this is the data stored in Timebuf",13))
          pst.str(@timebuf)     'this should be date/time stored in DAT timebuf and SD card
          
          pst.str(string(13,"data should be written",13))        'debug
          sdfat.pclose
          sdfat.unmount      
          pst.str(string("all closed down"))               'debug 
      
    
         
    
    DAT
      timebuf    byte "00000000000000", 0   'buffer to store ASCII-atized variables (14 bytes + nul)
    
    CON
    {{
    This is the PST display
    
    current date and time in hours,minutes,seconds
    12/04/44/36
    SD card was found and mounted fine
    
    this is the data stored in Timebuf
    18, 4,68,54,,,,,,,
    data should be written
    all closed down
    }}
    
  • kuronekokuroneko Posts: 3,623
    edited 2014-05-12 06:43
    AGCB wrote: »
    I have tried multiple ways to convert these so as to get proper output but I have failed in all. I basically know what the problem is but don’t know how to fix it.
    Looks like e.g. tempo:=fmt.hex(tempo, RTCbuf[seconds], 2) should do the trick provided the input is BCD.
  • JLockeJLocke Posts: 354
    edited 2014-05-12 06:48
    What RTC are you using? Maybe I missed it.
  • AGCBAGCB Posts: 330
    edited 2014-05-12 13:07
    kuroneko Thanks, I'll try that

    JLOCKE It's a DS1307

    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-12 13:58
    That did it!

    I failed to inform of my RTC chip so the output format and use of (fdec vs hex) was what caused it to not work. As JLocke was alluding to.

    As I've seen many times on this forum "we can't help you if you don't tell us exactly what you have". I'm guilty but now happy that I learned something extra. I usually do learn more than bargained for.

    Thanks to all who replied

    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-18 03:51
    Still moving forward and stymied by quirksome thing.

    Time recorded on SD card Excel file works great after 10:00 AM

    But, before that, leading zeros are deleted (I wish they would remain) AND either a 20 or 19 is inserted before the seconds value. It is 20 if the seconds are less than 30 and 19 if the seconds are greater than 30. I've tried and tried multiple ways of correcting this but to no avail. I could live with it if necessary but in the interest of learning which is my only goal anyway, I'd like to figure it out. This would be OK if I were a late riser LOL.

    At the bottom of my code I have included the Excel file to show what I mean.

    Thanks again for your time.
    Aaron
    CON
    {{
     Stores time, temperature and % of light on LDR in SD    readable in MS Excell
    }}
      _clkmode = xtal1 + pll16x      '80 MHz system clock
      _xinfreq = 5_000_000
    
      DO  = 0
      CLK = 1
      DI  = 2
      CS  = 3
      sclpin  =  20
      sdapin  =  21
      Bitrate = 100_000  'I2C @ 100k  
    
      'RTCbuf address registers
    Seconds = 0
    Minutes = 1
    Hours   = 2
    Day     = 3
    Date    = 4
    Month   = 5
    Year    = 6
               
    OBJ
     sdfat : "fsrw"
     pst   : "parallax serial terminal"
     fmt   : "StrFmt"
     rtc   : "jm_ds1307_demo"
     i2c   : "I2C PASM driver v1.3" 
      
    VAR   
      long  ldr_stk[60]
      long  tempo,temper,temper2,ldr_tim
      byte  RTCbuf[7]
      byte  index,line_num,iter  
    
    PUB  start
      pst.start(115200)
      I2C.start(SCLpin,SDApin,Bitrate)
      rtc.startx(sclpin, sdapin)
        'set up counter for LDR timer           see below for hook up
      ctra[30..26] := %01000     ' Set mode to "POS detector"
      ctra[5..0] := 17          ' Set APIN to 17 (P17)
      frqa := 1                ' Increment phsa by 1 for each clock tick  
      waitcnt(clkfreq+cnt)  
      main
    PUB  main  
      repeat
        temper:=I2C.read(I2C#TC74,00)     ' Read temperature byte
        temper2:=temper*9/5+32     'convert to farenheit     
        pst.dec(temper)
        pst.str(string("°C   "))
        pst.str(string(13,"iteration   "))
        pst.dec(iter)
        iter++  
        I2C.read_page(I2C#RTC,Seconds,@RTCbuf,7) 'read RTC
        
              'put time data in DAT buffer
        tempo:=@timebuf                'give tempo beginning RAM address   
        tempo:=fmt.hex(tempo,RTCbuf[hours],2)   'convert hour to ASCII and put in next 2 RAM addresses     
        tempo:=fmt.ch(tempo,"/")                'add a slash
        tempo:=fmt.hex(tempo,RTCbuf[minutes],2)  'convert minute to ASCII and put in next RAM addresses
        tempo:=fmt.ch(tempo,"/")
        tempo:=fmt.hex(tempo,RTCbuf[seconds],2)  'convert second to ASCII and put in next RAM addresses
    
             'this is a try to get rid of extra characters in seconds of Excell file
        tempo:=fmt.ch(tempo," ")     'pad buffer with spaces
        tempo:=fmt.ch(tempo," ")
    
        pst.str(string(13,"this is the data stored in Timebuf",13))
        pst.str(@timebuf)     'this should be date/time stored in DAT timebuf and SD card     
        
               'put temp data in DAT buffer
        temper:=@tempbuf        'give temper beginning RAM address
        temper:=fmt.fdec(temper,temper2,2,0)
        
                'put ldr data in DAT buffer
        ldr_tim:=LDR
       
        ldr_tim:=@ldrbuf
        ldr_tim:=fmt.fdec(ldr_tim,LDR,3,0)
        
               'display LDR %
        pst.str(string(13,"LDR percent =   "))
        pst.dec(LDR)
    
              'start a new file write or add to it
        if  line_num > 0
          second_wrt   ' "a" in popen method to append info to file
        else
          first_wrt    ' "w" in popen method for 1st write to file
              
        waitcnt(clkfreq*47+cnt) 'repeat loop  every ??? seconds
        
        if  ina[25]            'push button to quit SD writes    (Must be done to quit)
          sdfat.unmount        'loop continues but no write to SD
          
    PRI  second_wrt   'this adds more stuff to file 
      sdfat.popen(string("ldr5.csv"),"a")  '(w,a,r or d must be lower case)  !!!
    
          'write time to SD
      index:=0
      repeat  index from 0 to 9
        sdfat.pputc(byte[@timebuf][index])    '1 byte at a time
      sdfat.pputc(",")   'skip one column
      sdfat.pputc(",")
      
          'write temperature to SD
      sdfat.pputc(byte[@tempbuf][0])          
      sdfat.pputc(byte[@tempbuf][1])
      sdfat.pputs(string(" °F  "))
    
          'add in LDR percent ilumination to SD
      sdfat.pputc(",")
      sdfat.pputc(byte[@ldrbuf][0])  
      sdfat.pputc(byte[@ldrbuf][1])
      sdfat.pputc(byte[@ldrbuf][2])
      sdfat.pputs(string("%"))
    
            'add brite or dim warnings
      if  LDR  >96
        sdfat.pputc(",")
        sdfat.pputs(string("BRITE"))
      if  LDR  <60
        sdfat.pputc(",")
        sdfat.pputs(string("DIM"))
        
      sdfat.pputs(string(13,10))
      sdfat.pclose
      pst.str(string(13,"data 2 written",13))        'debug     
    
    PRI  first_wrt  |  mount        'this is the 1st write to SD card file
      mount := \sdfat.mount_explicit(DO,CLK,DI,CS)
      if  mount < 0
        pst.str(string(13,"failed to mount"))
        abort
      
      pst.str(string(13,"SD card was found and mounted fine",13))
      sdfat.popen(string("ldr5.csv"),"w")   '(w,a,r or d must be lower case)  !!!
      sdfat.pputs(string("Time,,Temp,Light"))
      sdfat.pputs(string(13,10))
      sdfat.pputs(string("hh/mm/ss"))
      sdfat.pputs(string(13,10))         
      index:=0
      repeat  index from 0 to 9
        sdfat.pputc(byte[@timebuf][index])    'put time 1 byte at a time including slashes
      sdfat.pputc(",")    'skip one column
      sdfat.pputc(",")
      sdfat.pputc(byte[@tempbuf][0])         'write temperature to SD
      sdfat.pputc(byte[@tempbuf][1])
      sdfat.pputs(string(" °F  "))
          'add in LDR percent ilumination to SD
      sdfat.pputc(",")
      sdfat.pputc(byte[@ldrbuf][0])
      sdfat.pputc(byte[@ldrbuf][1])
      sdfat.pputs(string("%"))
      sdfat.pputs(string(13,10))                                                            
      pst.str(string("data 1 written"))        'debug 
      pst.newline  
      sdfat.pclose  
      line_num++    'increment variable so further "a" writes go to 2nd method (add to file)
    
    PUB  LDR | time,pcnt    'time and pcnt are local variables      
         dira[17] := outa[17] := 1               ' Set pin to output-high
         waitcnt(clkfreq/100_000 + cnt)          ' Wait for circuit to charge       
         phsa~                                   ' Clear the phsa register
         dira[17]~                               ' Pin to input stops charging circuit
         waitcnt(clkfreq/60 + cnt) 
         time := (phsa - 624) #> 0 
         pcnt:=1+(9090-time)/89  #>0<#100   'pcnt is limited to 0 - 100                                                       
         result:=pcnt          'this is the value returned to calling method            
      
    DAT
      timebuf    byte "000000000", 0   'buffer to store ASCII-atized variables (1 byte for ea data place +1 + nul) ???
      tempbuf    byte "00",0
      ldrbuf     byte "000",0
    
    CON
    {{
    
       Excel file
    
    Time            Temp    Light   
    hh/mm/ss                                
    04/56/52                69 °F   9%      
    04/57/40                69 °F   94%     
    04/58/28                69 °F   94%     
    04/59/15                69 °F   94%     
    05/00/02                69 °F   93%     
    05/00/49                69 °F   94%     
    5/1/1936                71 °F   93%                    'here's the quirk
    5/2/2023                69 °F   100%    BRITE
    5/3/2010                71 °F   99%     BRITE
    
     
    }}
    
  • twm47099twm47099 Posts: 867
    edited 2014-05-18 08:20
    I'm not sure that I understand what you are doing, but if you are reading sd data into an excel file, it looks like the 'strange' cells are formatted as 'dates'. Excel can do this if the cell is general and it sees something it thinks is a date.
    Try to manually format all cells that might be written to, before having them written to.

    Otherwise, can you make a macro that will format the cells aftter they are written?

    hope this helps
    Tom
  • Duane DegnDuane Degn Posts: 10,588
    edited 2014-05-18 10:09
    I agree with Tom. It sounds like this is an Excel issue.

    You can have Excel treat the numbers as text if you surround it in double quotes.

    You'll need to use the ASCII value for double quotes (34) since otherwise the Prop Tool will think you're trying to enter a character or string. Just use 34 to place a double quote at the beginning of the field and 34 before adding the comma delimiter.

    If you change the name of the file to end with txt instead of csv, you can import it into Excel. While importing you can state what type of format you want the data to take. You might be able to use this import feature (or some other Excel feature) to solve the issue without having to resort to adding double quotes to the file.
  • AGCBAGCB Posts: 330
    edited 2014-05-18 10:22
    As I've thought about this for 10 minutes, I believe you's are right. I wasn't aware of that Excel property but it makes sense with the 19 being added to the more than 30s IE 1930-1960 and the 20 being added to the less than 30s IE2000-2029.

    It's too nice outside to be in the basement, I'm going fishing. I'll look at this early in the morning.

    Thanks again for your knowledge.

    Aaron
  • AGCBAGCB Posts: 330
    edited 2014-05-21 05:03
    The simple solution was to put colons between the hr:mn:sc instead of /s. Thanks
    Aaron
Sign In or Register to comment.