Change the format of the eWON Txt/CSV reports - simon - 03-05-2018
Hi,
Some people want to send Text/CSV Export Block Descriptor from their eWON but there is a little thing in the format that eWON uses which is not compatible with the software used to process them.
Here is a script example that allows you to change the semi-column into a column :
Code: PRINT "File processing..."
OPEN "exp:$dtHT$ftT$st_m1$et_s0" FOR TEXT INPUT AS 1
OPEN "file:/usr/fileToSend.txt" FOR BINARY OUTPUT AS 2
ReadNext:
IF EOF 1 THEN GOTO ReadDone
Line$ = GET 1
IF Line$ = "" THEN GOTO ReadDone
PUT 2, @ReplaceStr$(Line$ , ";", ",")
GOTO ReadNext
ReadDone:
CLOSE 1
CLOSE 2
PRINT "File processed"
SENDMAIL "test@hms.se", "", "Report with comma", "&[$dtUF$uf/usr/fileToSend.txt$fnReport.txt]"
Function ReplaceStr$($InputString$, $SearchString$, $ReplaceByString$)
$Loop:
$posstr% = INSTR 1, $InputString$, $SearchString$
IF $posstr% = 0 THEN GOTO $endloop
$SearchStringEndPos% = $posstr% + LEN $SearchString$
$LenInputString% = LEN $InputString$
IF $SearchStringEndPos% > $LenInputString% THEN //SearchString is at the end of the InputString
$ReplaceStr$ = $InputString$(1 To $posstr%-1) + $ReplaceByString$
ELSE
$ReplaceStr$ = $InputString$(1 To $posstr%-1) + $ReplaceByString$ + $InputString$($SearchStringEndPos% To $LenInputString%)
ENDIF
$InputString$ = $ReplaceStr$
GOTO $Loop
$endloop:
EndFn
Here is another example to change the date format used :
Code: OPEN "exp:$dtHT$ftT$st_d1$et_s0" FOR TEXT INPUT AS 1
OPEN "file:/usr/fileToSend.txt" FOR BINARY OUTPUT AS 2
FirstLine% = 1
ReadNext:
IF EOF 1 THEN GOTO ReadDone
Line$ = GET 1
IF Line$ = "" THEN GOTO ReadDone
IF FirstLine% = 1 THEN
FirstLine% = 0
PUT 2, Line$
GOTO ReadNext
ENDIF
PosStart% = INSTR 1, Line$, ";"
PosStart% = PosStart% + 2 //Remove Quotes as well
PosEnd% = INSTR PosStart%, Line$, " "
PosEnd% = PosEnd% - 1 //Remove Quotes as well
Date$ = Line$(PosStart% TO PosEnd%)
NewDate$ = Date$(7 TO 10) + "/" + Date$(4 TO 5) + "/" + Date$(1 To 2)
PUT 2, Line$(1 TO PosStart% - 1) + NewDate$ + Line$(PosEnd% +1 TO LEN(Line$))
GOTO ReadNext
ReadDone:
CLOSE 1
CLOSE 2
RE: Change the format of the eWON Txt/CSV reports - Neels - 13-05-2020
(03-05-2018, 04:28 PM)simon Wrote: Hi,
Some people want to send Text/CSV Export Block Descriptor from their eWON but there is a little thing in the format that eWON uses which is not compatible with the software used to process them.
Here is a script example that allows you to change the semi-column into a column :
Code: PRINT "File processing..."
OPEN "exp:$dtHT$ftT$st_m1$et_s0" FOR TEXT INPUT AS 1
OPEN "file:/usr/fileToSend.txt" FOR BINARY OUTPUT AS 2
ReadNext:
IF EOF 1 THEN GOTO ReadDone
Line$ = GET 1
IF Line$ = "" THEN GOTO ReadDone
PUT 2, @ReplaceStr$(Line$ , ";", ",")
GOTO ReadNext
ReadDone:
CLOSE 1
CLOSE 2
PRINT "File processed"
SENDMAIL "test@hms.se", "", "Report with comma", "&[$dtUF$uf/usr/fileToSend.txt$fnReport.txt]"
Function ReplaceStr$($InputString$, $SearchString$, $ReplaceByString$)
$Loop:
$posstr% = INSTR 1, $InputString$, $SearchString$
IF $posstr% = 0 THEN GOTO $endloop
$SearchStringEndPos% = $posstr% + LEN $SearchString$
$LenInputString% = LEN $InputString$
IF $SearchStringEndPos% > $LenInputString% THEN //SearchString is at the end of the InputString
$ReplaceStr$ = $InputString$(1 To $posstr%-1) + $ReplaceByString$
ELSE
$ReplaceStr$ = $InputString$(1 To $posstr%-1) + $ReplaceByString$ + $InputString$($SearchStringEndPos% To $LenInputString%)
ENDIF
$InputString$ = $ReplaceStr$
GOTO $Loop
$endloop:
EndFn
Here is another example to change the date format used :
Code: OPEN "exp:$dtHT$ftT$st_d1$et_s0" FOR TEXT INPUT AS 1
OPEN "file:/usr/fileToSend.txt" FOR BINARY OUTPUT AS 2
FirstLine% = 1
ReadNext:
IF EOF 1 THEN GOTO ReadDone
Line$ = GET 1
IF Line$ = "" THEN GOTO ReadDone
IF FirstLine% = 1 THEN
FirstLine% = 0
PosCR% = INSTR 1, Line$, CHR$(13)
PUT 2, Line$(1 TO PosCR% -1)
Line$ = Line$ (PosCR% TO LEN(Line$))
ENDIF
PosStart% = INSTR 1, Line$, ";"
PosStart% = PosStart% + 2 //Remove Quotes as well
PosEnd% = INSTR PosStart%, Line$, " "
PosEnd% = PosEnd% - 1 //Remove Quotes as well
Date$ = Line$(PosStart% TO PosEnd%)
NewDate$ = Date$(7 TO 10) + "/" + Date$(4 TO 5) + "/" + Date$(1 To 2)
PUT 2, Line$(1 TO PosStart% - 1) + NewDate$ + Line$(PosEnd% +1 TO LEN(Line$))
GOTO ReadNext
ReadDone:
CLOSE 1
CLOSE 2
Hi Simon
I am sorry but , with very few knowledge of this could you tell me where to write this scripting to be able to convert the text CSV to excell and have a graph of my Tag ?
Thanks
RE: Change the format of the eWON Txt/CSV reports - simon - 18-05-2020
Hi,
The Ewon Flexy does not support XLS format but CSV (that you can open in Excel). I guess you are talking about CSV files.
Here, if you want the script to send you a CSV file that you then use to create your graph in Excel, you just have to change the file extension to .csv in the files path :
&[$dtUF$uf/usr/fileToSend.csv$fnReport.csv
and
OPEN "file:/usr/fileToSend.csv" FOR BINARY OUTPUT AS 2
|