3 Installing CSV data parser module
Contents
1. S Port options Events handling Select events handling modules Moodle name Version C Events notification events dll 3 2 0 2 C Internet sharing ishare dl 3 2 0 2 a Query Parse Filter Data export Events handling Download plugin now Fig 3 Examples of installed modules 4 Glossary Plug in module Main program the program shell that uses this module For example Advanced Serial Data Logger Parser the module that processes the data flow singling out data packets from it and variables from data packets These variables are used in data export modules after that Core see Main program 1999 2008 AGG Software CSV data parser module 5 5 1 User Manual Analyzing and parsing data To export the data received from the port you should configure the parser The parser allows you to single out data rows from the overall flow consisting of ASCII characters and parse these rows into variables that are exported into various destinations after that You should define the rules the parser will use to process the incoming data flow on the Data packet tab fig 1 Delimited data parser 3 2 1 build 7 Se General Data row begin IT Ip General Data row end 0D CIRE Timeout ms 5000 2 Characters Delimiter translation Quote character Ai C Detect column names row by word COLUMN1 Filter Export columns All as text Specified only C Decode2. The CSV data parser module PRINTED MANUAL 1999 2008 AGG Software CSV data parser module 1999 2008 AGG Software All rights reserved No parts of this work may be reproduced in any form or by any means graphic electronic or mechanical including photocopying recording taping or information storage and retrieval systems without the written permission of the publisher Products that are referred to in this document may be either trademarks and or registered trademarks of the respective owners The publisher and the author make no claim to these trademarks While every precaution has been taken in the preparation of this document the publisher and the author assume no responsibility for errors or omissions or for damages resulting from the use of information contained in this document or from the use of programs and source code that may accompany it In no event shall the publisher and the author be liable for any loss of profit or any other commercial damage caused or alleged to have been caused directly or indirectly by this document Printed 25 11 2008 Publisher AGG Software Production 1999 2008 AGG Software http www aggsoft com Contents l Table of Contents Part 1 Introduction 1 Part 2 System requirements 1 Part 3 Installing CSV data parser module 1 Part 4 Glossary 4 Part 5 User Manual 5 1 Analyzing and parsing data cccccccseeceeeeeneeeeeeeeeeeseeeesneeeseeeesne
3. same as x07 e escape ESC same as x1b Examples foo x20bar matchs foo bar note space in the middle tfoobar matchs foobar predefined by tab 1999 2008 AGG Software User Manual 12 Character classes You can specify a character class by enclosing a list of characters in which will match any one character from the list If the first character after the is the class matches any character not in the list Examples foob aeiou r finds strings foobar foober etc but not foobbr foobcr etc foob aeiou r find strings foobbr foobcr etc but not foobar foober etc Within a list the character is used to specify a range so that a z represents all characters between a and z inclusive If You want itself to be a member of a class put it at the start or end of the list or escape it with a backslash If You want T you may place it at the start of list or escape it with a backslash Examples az matchs a z and az matchs a z and a 2 matchs a z and a z matchs all twenty six small characters from a to z n xOD matchs any of 10 11 12 13 d t matchs any digit or t a matchs any char from TI a Metacharacters Metacharacters are special characters which are the essence of Regular Expressions There are different types of metacharacters described below Metacharacters line separato
4. If you ll select the All as text then the module will export all columns from your data row with the String data type The name of these exported variables will begin with the COLUMN word Otherwise if you ll select the Specified only you can define columns which you want to export and a data type of each column on the Data parsing tab fig 2 e Add atime stamp to each data row the parser will add the new DATE_TIME_STAMP variable to each data row that will be parsed into variables e Add a data source ID to each data row the parser will add the new DATA _SOURCE_ID variable to each data row that will be parsed into variables It will allow you to identify data during export if you collect data from several devices at the same time 1999 2008 AGG Software CSV data parser module Delimited data parser 3 2 1 build 7 Data parsing CE Parser params D Integer Characters Default value 0 translation Ordinal number 1 al Data parsing eatin eect Fig 2 Data parsing 1 Item name name of the parser variable This item will be used in data export modules 2 Data type a data type of a column 3 Default value the parser will use this value if can t extract the specified column and can t convert the extracted value to the specified data type 4 Ordinal number column number in your delimited data row Data parsing example You can find sever
5. escapes like n St 4x00 Data format C Add date time stamp to each data row parsed C Add a data source ID to each data row parsed Fig 1 Data formatting Use beginning ending signs to split data packets Usually an ASCII data flow contains a data row ending sign Usually the 0x0D Ox0A characters or their combination are used You can see what characters are the ending signs in your case Just enable displaying characters with the code lt 0x20h in the main window of the program and try to receive some data If your data block contains nonprinting characters with the code lt 0x20h you should specify them in the module as XX XX for example OD 0A e Data row begin optional parameter For example 02 e Data row end required parameter For example OD 0A Sometimes the beginning and the ending of a data packet vary For example the beginning of your data packet contains the value of time like 00 01 55 but this value is constantly changing Then you can create such a regular expression as d 2 d 2 d 2 in order to determine the beginning of a packet and select the RE checkbox next to the corresponding field 1999 2008 AGG Software User Manual 6 Timeout if you do not specify the beginning of a data packet it may happen that the module will infinitely wait for the ending of some data packet The timeout value is used to prevent it It defines the maximum interval the module can
6. 2 characters Bytes Binary value Blob Binary Large Object field bytes set Boolean Logical value True False 0 or 1 Float Real number value range 2 9 x 10 39 1 7 x 10 38 Smallint Signed small integer value range 32768 32767 Word Word unsigned small integer value range 0 65535 Integer Signed integer value 2147483648 2147483647 Date Date Time Time DateTime Date and time 5 2 Characters translation Character translation fig 4 is used when you want to remove or replace some characters in a data packet For example remove nonprinting ASCII characters Delimited data parser 3 2 1 build 7 Characters translation General Type Data packet iv Filter Character ASCIIOI NUL w Keep character Characters translation Remove all below ASCII 32 Remove all above ASCII 127 er Convert all characters None Uppercase Lowercase Filter Current translations Remove all below ASCII 32 Remove all above ASCII 127 Data parsing kJ Fig 4 Character translation 1999 2008 AGG Software 5 3 User Manual 10 Filter The filter is used to ignore some data packets that you do not want to export with the help of other modules Filter Filter rules data packets level Type Expression Action Text R Parse Kr Situation Fig 1 Filtering rules You should defi
7. Logger Notes for Microsoft Vista Since our software saves data to the registry and installs to the Program Files folder the following requirements must be met 1 You need Administrator rights to run and install our software 2 The shortcut icon of our software will be located on the desktop 3 Windows Vista will ask for your confirmation to continue the installation NOTE You can configure the user account only once in order not to see the above dialog box any more Search Google for the solution of this problem 3 Installing CSV data parser module 1 Close the main application for example Advanced Serial Data Logger if it is running 2 Copy the program to your hard drive 3 Run the module installation file with a double click on the file name in Windows Explorer 4 Follow the instructions of the installation software Usually it is enough just to click the Next button several times 1999 2008 AGG Software Installing CSV data parser module 2 5 Start the main application The name of the module will appear on the Modules tab of the Settings window if it is successfully installed If the module is compatible with the program its name and version will be displayed in the module list You can see examples of installed modules on fig 1 2 Some types of modules require additional configuration To do it just select a module from the list and click the Setup button next to the list The configura
8. al real life examples on our site where different data parsing methods are used It can help you understand how the parser works http Awww aggsoft com serial data logger tutorials htm Example Please take a look at the example below we ve received this data from a PBX 223 741 09127 5 21 20 00 05 28 O HERR RK KKK KKK KKK KKK KKK KKK KK KKK KK KKK 5713134 013 15 53 30 0 05 28 O KERR k e k e k e k de KKK KK KEKE ke de ke KKK ke ke ke ke ke ke Strings with data have different lengths in this case But all variables are located in a certain order separated with a semicolon delimiter In this example you can single out data by their ordinal number 1 7 using the separator 1999 2008 AGG Software User Manual 8 You can use the following tab to specify the data format for some data types fig 3 Delimited data parser 3 2 1 build 7 LJ Data format Text items format Characters translation C Trim spaces from left C Trim spaces from tight a o DRG Date and time format Fan Date items DD MM er Time items HH NN SS DateTime items DD MM ve HHTONNT GG Note Any non alpha numerical characters must be quoted Data parsing Example or characters you must enter as d or C Use current date for missing parts of date C Use current time for missing parts of time A Locale language ID 409 Data format Default value 0 409 English language Fig 3 Data format Text data fo
9. alternative tried and it successfully matches the target string This might not seem important but it is important when you are capturing matched text using parentheses Also remember that is interpreted as a literal within square brackets so if You write fee fie foe You re really only matching feio Examples foo bar foo matchs strings foobar or foofoo Metacharacters subexpressions The bracketing construct may also be used for define r e subexpressions Subexpressions are numbered based on the left to right order of their opening parenthesis First subexpression has number 1 Examples foobar 8 10 matchs strings which contain 8 9 or 10 instances of the foobar foob 0 9 a r matchs foobOr foob1r foobar foobaar foobaar etc Metacharacters backreferences Metacharacters 1 through 9 are interpreted as backreferences lt n gt matches previously matched subexpression lt n gt Examples 1 matchs aaaa and cc 1 also match abab and 123123 d 1 matchs 13 in double quotes or 4 in single quotes or 77 without quotes etc Modifiers Modifiers are for changing behaviour of parser 1999 2008 AGG Software 15 CSV data parser module There are many ways to set up modifiers Any of these modifiers may be embedded within the regular expression itself using the construct i Do case insensitive pat
10. e Reconnect after option is enabled No additional actions are required Invalid procedure call Bad arguments an attempt to call the module using invalid parameters Perhaps you are using the module incompatible with the version of the Advanced Serial Data Logger kernel Update the versions of both the kernel and the module 1999 2008 AGG Software 17 CSV data parser module Writing to the database is complete the message appears if your queue of SQL queries is successfully executed It requires no additional actions Writing to the database is complete with errors the message appears if the executing your queue of SQL queries was interrupted by an error It requires no additional actions Your SQL is empty Please specify some SQL text first the message appears if you do not enter the text for your SQL query Check if the options on the SQL queue tab are configured correctly Invalid temporary path the path to the temporary file specified by you does not exist Enter a new path in the Temporary folder field on the Errors handling tab s d will be replaced by additional information 1999 2008 AGG Software
11. ecurrences of a certain character Regular expressions look ugly for novices but really they are very simple handy and powerful tool Let s start our learning trip Simple matches Any single character matches itself unless it is a metacharacter with a special meaning described below A series of characters matches that series of characters in the target string so the pattern bluh would match bluh in the target string Quite simple eh You can cause characters that normally function as metacharacters or escape sequences to be interpreted literally by escaping them by preceding them with a backslash for instance metacharacter match beginning of string but match character match and so on Examples foobar matchs string foobar FooBarPtr matchs FooBarPtr Escape sequences Characters may be specified using a escape sequences syntax much like that used in C and Perl n matches a newline VI a tab etc More generally xnn where nn is a string of hexadecimal digits matches the character whose ASCII value is nn If You need wide Unicode character code You can use x nnnn where nnnn one or more hexadecimal digits xnn char with hex code nn x nnnn char with hex code nnnn one byte for plain text and two bytes for Unicode t tab HT TAB same as x09 n newline NL same as x0a r car return CR same as x0d f form feed FF same as x0c a alarm bell BEL
12. eseeeesneeeseeeesneeseeseesneseeeeseenes 5 2 Characters translation cccsseccseseeeseeeeeeeeeeeeneeeseneeeesscaesenseeeeeeaeseeseeeeeseesseseaeseneeeesasaeeeneneees 9 SKI Ee 10 4 Syntax of Regular Expressions cs eccesseessseeeeseeeeseeeeneeeeeseaeeeseeeesecaeseseeaeeseeesesseeeeneenes 11 Part 6 Troubles 16 1 Possible problems 0 sccccsseeeeceeeeseee eee eeseneeeeeenseeeeeeeesaneeeeeseseeeeseseeaneeeeesesaneesesesseeaeeeeessne 16 1999 2008 AGG Software 1 CSV data parser module 1 Introduction This module can parse a data flow which contains a tab or comma delimited text data You can define any delimiter or quote characters in the module Th module can easily and automatically parse all column in a CSV row to parser items These variables can be post processed and exported later as you want 2 System requirements The following requirements must be met for CSV data parser module to be installed Operating system Windows 95 Windows 95 OSR2 Windows 98 Windows Me Windows NT4 Windows 2000 Windows XP all editions both x86 and x64 Windows 2003 Server both x86 and x64 Windows Vista both x86 and x64 Free disk space Not less than 5 MB of free disk space is recommended Special access requirements You should log on as a user with Administrator rights in order to install this module The main application core must be installed for example Advanced Serial Data
13. fiers imsxr imsxr You may use it into r e for modifying modifiers by the fly If this construction inlined into subexpression then it effects only into this subexpression Examples i New York matchs New york and New York i New i York matchs New York but not New york i New York matchs New york and new york i New York matchs New York but not new york text A comment the text is ignored Note that parser closes the comment as soon as it sees a so 1999 2008 AGG Software User Manual 16 there is no way to put a literal in the comment Troubles Possible problems No data for publication exporting no data is passed for exporting Solution configure the parser make sure that one or more variables are declared in the parser Error on binding variable with name s s the error usually occurs if data does not correspond to the specified format For example the date and time format does not correspond to the data Unable to disconnect from the database s and Unable to connect to a database s it is impossible to connect disconnect to form the database You should check the parameters of the database connection The analysis of the additional information will help you locate the error Database access error s Stop operations with the database the message appears if an error occurs during an attempt to execute an SQL query if the
14. ne one or several filtering rules If some condition is true the program does what is specified in the Action field with the matching packet Action types e Ignore the current data packet will be ignored and will not be exported e Parse the current data packet will be parsed and exported There are several condition types that are specified in the Status field Rule status types e Disabled this rule is disabled and not used for filtering purposes e Contains this rules checks whether the string expression from the Expression field is present in the data packet e Does not contain this rules checks whether the string expression from the Expression field is absent in the data packet Expressions types Expressions in the Expression field can be of 2 types e Text the module will search the data packet for a string specified in the Expression field The search is case sensitive e Regular expression the module will use the regular expression specified in the Expression field in its search The search is case sensitive 1999 2008 AGG Software 11 CSV data parser module 5 4 Syntax of Regular Expressions Introduction Regular Expressions are a widely used method of specifying patterns of text to search for Special metacharacters allow You to specify for instance that a particular string You are looking for occurs at the beginning or end of a line or contains n r
15. obbr and foobr but not foobalkj9r fooba 2 r matchs the string foobaar 1999 2008 AGG Software User Manual 14 fooba 2 r matchs strings like foobaar foobaaar foobaaaar etc fooba 2 3 r matchs strings like foobaar or foobaaar but not foobaaaar A little explanation about greediness Greedy takes as many as possible non greedy takes as few as possible For example b and b applied to string abbbbc return bbbb b returns b b returns empty string b 2 3 returns bb b 2 3 returns bbb Metacharacters alternatives You can specify a series of alternatives for a pattern using to separate them so that fee fie foe will match any of fee fie or foe in the target string as would f eli o e The first alternative includes everything from the last pattern delimiter or the beginning of the pattern up to the first and the last alternative contains everything from the last to the next pattern delimiter For this reason it s common practice to include alternatives in parentheses to minimize confusion about where they start and end Alternatives are tried from left to right so the first alternative found for which the entire expression matches is the one that is chosen This means that alternatives are not necessarily greedy For example when matching foo foot against barefoot only the foo part will match as that is the first
16. r foobbr and so on but not foob1r foob r and so on Metacharacters iterators Any item of a regular expression may be followed by another type of metacharacters iterators Using this metacharacters You can specify number of occurences of previous character metacharacter or subexpression zero or more greedy similar to 0 one or more greedy similar to 1 Zero or one greedy similar to 0 1 n exactly n times greedy n at least n times greedy n m atleast n but not more than m times greedy zero or more non greedy similar to 0 one or more non greedy similar to 1 2 zero or one non greedy similar to 0 1 n exactly n times non greedy n at least n times non greedy n m at least n but not more than m times non greedy So digits in curly brackets of the form n m specify the minimum number of times to match the item n and the maximum m The form n is equivalent to n n and matches exactly n times The form n matches n or more times There is no limit to the size of n or m but large numbers will chew up more memory and slow down r e execution If a curly bracket occurs in any other context it is treated as a regular character Examples foob r matchs strings like foobar foobalkjdflkj9r and foobr foob r matchs strings like foobar foobalkjdflkj9r but not foobr foob r matchs strings like foobar fo
17. rmat this group of options allows you to remove leading and or trailing spaces in variables of the String type Date and time format it often happens that the date or time format the external device sends does not coincide with the format in which this data is stored in the database To convert the date into the necessary format you should specify the format the date and time is received in The same format is used to specify the date and time as the one that is used in the name of the log file in the main program for example Advanced Serial Data Logger So read about it in the help file of the main program Note the and or characters are enclosed in quotation marks in the template see fig 3 If some part is missing in the received date and time for example year you can take the missing part from the current date and time Use the current date for missing parts Use the current time for missing parts Sometimes devices send dates with month names for example Jan 10 2005 and this name can be in a language in this example it is in English different from the language of your operating system The language ID the date is sent in is specified in the Date language ID field in this case 1999 2008 AGG Software 9 CSV data parser module Data types String String value Character array with length from 1 to 65535 characters Memo String value Character array with length from 1 to 23
18. rs s start of line end of line A start of text Z end of text any character in line Examples foobar matchs string foobar only if it s at the beginning of line foobar matchs string foobar only if it s at the end of line foobar matchs string foobar only if it s the only string in line foob r matchs strings like foobar foobbr foobir and so on The metacharacter by default is only guaranteed to match at the beginning of the input string text the metacharacter only at the end Embedded line separators will not be matched by AN or You may however wish to treat a string as a multi line buffer such that the will match after any line separator within the string and will match before any line separator The metacharacter by default matches any character 1999 2008 AGG Software 13 CSV data parser module Note that an empty line pattern doesnot match the empty string within the sequence xOD x0A but matchs the empty string within the sequence xOA x0D Metacharacters predefined classes w an alphanumeric character including _ WW a nonalphanumeric d a numeric character D a non numeric s any space same as t n r f S a non space You may use Ww d and s within custom character classes Examples foob dr matchs strings like foob1r foob6r and so on but not foobar foobbr and so on foob w s r matchs strings like foobar foob
19. second variant of reacting to errors is selected The message implies a Yes or No answer The analysis of the additional information will help you locate the error Unable to verify your SQL script s the message appears when an attempt to analyze your SQL query fails Check if the syntax of your SQL query is correct Tested successfully the message appears if your database connection is successfully tested It requires no additional actions Database isn t used the message appears if the module is temporarily disabled the Temporarily disabled check box is selected or the database name field is empty Check the connection parameters Database isn t selected the message appears if the database type is not selected Check the connection parameters Database s s contains the database name The message appears if the database connection is successful Usually you see it when you call the module for the first time It requires no additional actions Invalid data block length columns d length d an internal application error It means that the data sent by the parser is in an invalid format Perhaps you are using the module incompatible with the version of the Advanced Serial Data Logger kernel Update the versions of both the kernel and the module The time of connection is not due yet d d the message appears during an attempt to connect to the database after the connection to it has been lost and th
20. tern matching using installed in you system locale settings m Treat string as multiple lines That is change and from matching at only the very start or end of the string to the start or end of any line anywhere within the string s Treat string as single line That is change to match any character whatsoever even a line separators which it normally would not match g Non standard modifier Switching it Off You ll switch all following operators into non greedy mode by default this modifier is On So if modifier g is Off then works as as and so on x Extend your pattern s legibility by permitting whitespace and comments see explanation below The modifier x itself needs a little more explanation It tells the parser to ignore whitespace that is neither backslashed nor within a character class You can use this to break up your regular expression into slightly more readable parts The character is also treated as a metacharacter introducing a comment for example abc comment 1 You can use spaces to format r e parser ignores it efg comment 2 This also means that if you want real whitespace or characters in the pattern outside a character class where they are unaffected by x that you ll either have to escape them or encode them using octal or hex escapes Taken together these features go a long way towards making regular expressions text more readable How to change modi
21. tion of the module is described below You can see some types of modules on the Log file tab To configure such a module you should select it from the File type list and click the Advanced button Port options Query Parse Filter Data query module ASCII data query and parser default dll wl Parser module As data query and parser default dll x C Parsing and exporting for data sent Parsing and exporting for data received Query Parse Filte i Select data filter modules Module name Version C Expressions expressions dll 3 2 0 2 C Aggregation aggregator dll 3 2 0 1 C Deadband deadband dll 3 2 0 1 Data export Events handling Fig 1 Examples of installed modules 1999 2008 AGG Software CSV data parser module S Port options Data export Select data export modules z J Module name Version C OPC server buildin dll 3 2 0 14 DDE Server ddesry dll 3 2 0 1 C Local database localdb dll 3 2 0 3 C ODBC database odbcexport dil 3 2 0 3 Query Parse Filter C Direct Excel connection olexcel dll 3 2 0 8 e J Send keystrokes wndem dll 3 2 0 2 ES C Excel export excelexport dll 3 0 0 19 Data export Download plugin now Hel Redirect data to another configuration Events handling Configuration Fig 2 Examples of installed modules 1999 2008 AGG Software Installing CSV data parser module
22. wait for a packet to end The Load amp Save button allows you to load some presets for some types of the data flow for example a CSV file or a plain text file Additional parameters on this tab The following parameters are used in both methods e Delimiter specify a delimiter which is used in your delimited data Usually comma semicolon tab You may define tab character as 09 e Quote character specify one or more quote characters which are using in your CSV data flow The format of this field lt Opening quote 1 gt lt Closing quote 1 gt lt Format delimiter gt lt Opening quote 2 gt lt Closing quote 2 gt lt Format delimiter gt lt Opening quote N gt lt Closing quote N gt Where Opening quote and Closing quote are any characters If the Closing quote isn t defined then Opening quote Closing quote Format delimiter For example the following string Il defines two pairs of quotes and Detect column names row by word if you ll specify any value in this field then the module will ignore all data rows which will contain this word e Decode escapes like n t x00 some fields in your data flow may contain non printable characters which are encoded like t ASCII TAB character n ASCII line feed character and etc This option allows you to decode these encoded characters automatically Export columns this options allow you to specify parsing options individually for each column in the data row
Download Pdf Manuals
Related Search