Write a variable to SD >csv file
AGCB
Posts: 327
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
Thanks
Aaron
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 TC74Thanks
Aaron
Comments
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.
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
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".
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":
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.
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").
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"?
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
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 theres 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
You can add a comma to your "fileName" string by using the "ch" method of "StrFmt".
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.
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.
My code is liberally commented; hope it helps you out.
Aaron
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 dont know how to fix it. Ive 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 }}JLOCKE It's a DS1307
Aaron
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
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 }}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
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.
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
Aaron