The result of the export function contains lots of information and to filter for relevant data I wrote a small AWK script. (If not familiar with AWK use google. You can install and run it on WIN10)
Input for the script are the CSV files you get as result from the online database viewer
Steps to get result:
- make sure that (G)AWK is installed on your PC
- Select Export/Sync tags in Application configuration
- Select Output type: Use a Microsoft access database file and push the export button
- Convert the resulting mdb file via an online viewer to CSV format
- (Unzip and) Store these files in a directory
- Make sure that a file with below script is also in this directory
- Open the Windows Powershell and go go the directory
- Command at the Windows power shell : awk '-fconvert.awk' *.csv > result.txt
- Import the result.txt file in Google Sheets
convert.awk contains below script
/Export/ { y = $0 }
/Clusius/ {
if (FNR > 1) {
if (y != "" ) {
split ( FILENAME,x,"." )
print x[1]
print y
y = ""
}
print $0
}
}
The result of the export function contains lots of information and to filter for relevant data I wrote a small AWK script. (If not familiar with AWK use google. You can install and run it on WIN10)
Input for the script are the CSV files you get as result from the online database viewer
Steps to get result:
- make sure that (G)AWK is installed on your PC
- Select Export/Sync tags in Application configuration
- Select Output type: Use a Microsoft access database file and push the export button
- Convert the resulting mdb file via an online viewer to CSV format
- (Unzip and) Store these files in a directory
- Make sure that a file with below script is also in this directory
- Open the Windows Powershell and go go the directory
- Command at the Windows power shell : awk '-fconvert.awk' *.csv > result.txt
- Import the result.txt file in Google Sheets
convert.awk contains below script
# Read and filter the VTScada export tags via a GAWK script for relevant records
# Only the "Use a Microsoft Acces database file" works on my PC (No Excel installed)
# I convert the resulting access database via an online viewer to a bunch of CSV files
# e.g. (https://mdbviewer.herokuapp.com/
#
# 14-feb 2020
# Pieter de Vries
#
/Export/ { y = $0 } # All first records start with the word Export. Save this record in variable y
/Clusius/ { # Filter by the Area field (replace with your own)
if (FNR > 1) { # If not the first line
if (y != "" ) { # If y not empty
split ( FILENAME,x,"." ) # remove the csv extention
print x[1] # and print the name
print y # print the first line
y = "" # clear the first record variable
}
print $0 # print the relevant record
}
}