Switch to desktop version  
Change the format of the eWON Txt/CSV reports - Printable Version

+- Ewon Technical Forum (https://techforum.ewon.biz)
+-- Forum: Development (https://techforum.ewon.biz/forum-50.html)
+--- Forum: BASIC Script (https://techforum.ewon.biz/forum-52.html)
+--- Thread: Change the format of the eWON Txt/CSV reports (/thread-597.html)



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