TES CSV Converter

Web based, multi-user, Telephone Management System with budget management and barring control

Moderators: Leon van Heerden, Luanda_Junzi, Belinda Frick, Lee Hendricks

TES CSV Converter

Postby Leon van Heerden » Mon Sep 04, 2017 6:19 pm

The TES CSV Converter checks a CSV file and converts it the TES format. The resulting file will then contain only relevant data for the TES system.

If the CSV files contains a record that doesn't match the INI file's RANGE sections, the file is rejected and moved to the Rejected folder. A log entry with the line number that failed and the reason, is logged away. This prevents invalid data from being uploaded.
The program will not run multiple copies at the same time in the same folders. This ensure that the processing of data doesn't overlap.

If there are records causing the processing to fail and it is data that you do not want to process, these entries should be removed from the file before it is processed by this utility.

TODO -- Supply an example script for pre-processing --

The Script below shows how to run the program
process-tes-data.sh
Code: Select all
#!/bin/bash
BASEFOLDER=/opt/datatex/tescsvconverter
CONVERTER=tescsvconverter

if [ ! -d $BASEFOLDER ] ; then
  echo "TES CSV Convertoer base folder doesn't exist [$BASEFOLDER]"
  exit 1
fi

if [ ! -e $BASEFOLDER/$CONVERTER ]; then
  echo "TES CSV Converter not found [$BASEFOLDER/$CONVERTER]"
  exit 1
fi

if [ ! -x  $BASEFOLDER/$CONVERTER ]; then
   echo "TES CSV Converter not executable [$BASEFOLDER/$CONVERTER]"
   exit 1
fi

cd $BASEFOLDER
./$CONVERTER


This is an example to run the program from Cron every hour at 1 minute past the hour
Code: Select all
1 * * * * /opt/datatex/tescsvconverter/process-tes-data.sh



Default location:
/opt/datatex/tescsvconverter
Files:
csvconverter
csvconverter.ini
Folders:
csvin/ { Where the CSV file will be read in from to process }
csvout/ { Where the successfully processed files will be dropped }
csvbackup/ { BAckup copies of the processed files are left here }
csvreject/ { all files that were not processed at all are dropped here. }

INI File
When you run this program it will check for an INI file with the same name as the program in the same folder.
If no INI file is found, a default file will be created with blank default values:

Code: Select all
[EXT RANGE 1]
START EXT=
END EXT=
COMPANYID=
BRANCHID=
EXCEPTIONS=

[PINCODE RANGE 1]
START PIN=
END PIN=

[CSV PATHS]
INPATH=csvin/
OUTPATH=csvout/
BACKUPPATH=csvbackup/
REJECTPATH=csvreject/

[CSV FILENAME]
FILES OLDER THAN SECONDS=10
SEQ=0
GUID=F9B933304A774BAD82C4BB7F6B1A3743


[EXT RANGE 1]
The START EXT and END EXT form a range of values that are used to determine the extension of a call. Call records will be validated against the extension ranges and generate valid call data from the CSV files. The extensions will be checked against the Source and Destination fields.
If the extension falls in the range, the CompanyID and BranchID are allocated to the call record.
CompanyID and BranchID must be valid TES IDs or the data will be rejected by TES.
To retrieve the IDs,
[list=][*]Login to your TES account,
[*]Go to Company Settings for the company that these extension belong to.
[*]The Company ID is the ID number displayed at the top section called "Company" , to the left of the WebHost field.
[*]The Branch ID is displayed int he section Called "Branch" next to each branch. [/list]
Only valid IDs will be loaded into TES.
EXCEPTIONS= allows you to enter a comma separated list of exceptions to the range.
e.g.
EXCEPTIONS=EXT,ext, fax,0215924033,202
All these values will now be treated as if they are also extensions and will allow the validator to place them in the extension field.


[PINCODE RANGE 1]
This section is related to the [EXT RANGE 1] where the number is the same.
The START and END PIN values form a range of values that are valid as pin codes for the related extensions on the EXT RANGE section. Any values that fall outside the configured range are, treated as Accounting codes and not PIN Codes.

[CSV PATHS]
These are the default values used. You can change the INI location to a different location if you need to point somewhere else.
INPATH=csvin/
All CSV files found in this folder will be processed if they are older that then configured time.

OUTPATH=csvout/
Where the successfully processed files will be dropped. The file names will be the GUID-SEQUENCE_NUMBER.csv

BACKUPPATH=csvbackup/
Backup copies of the processed files are left here. A date folder will be created every day when this utility runs and all files dropped in this folder.

REJECTPATH=csvreject/
All files that were not processed at all are dropped here.

[CSV FILENAME]
SEQ=0
Do not change the sequence number as files will then be overwritten and call records may be lost. The files that are uploaded and processed will have these sequence numbers and the last processed sequence number is stored here.

GUID=
This is a unique identifier for this server. This value should never be changed since it is the value linked to the data coming from this server. Duplicate this values from other sites may cause inaccessible data. TES will only allow valid company branch data to be loaded and if your values are not valid on the TES side, the data is rejected on the TES server.


Log messages:
Code: Select all
** ERROR - There is no In Path [CSV PATHS] in the ini file. No processing will be doen

There is no INPATH= in the INI file or there is no INI file at all.
Check that you have an INPATH= configured and that it exists.


Code: Select all
** ERROR This job is already busy running

This message is shown when you try and run a second copy of the program
or
if the program crashed and the previous run didn't finish properly.
Check your log files first and then check the following.
{base software folder}/tmp, will contain a file "tescsvconverterrunning.txt" if you remove this file the sofware will run again. Check that your software is not still running and stop it first before removing the file. Ensure that you don't remove this file while the software is still running, since it will cause problems with files being processed.

Code: Select all
** ERROR File Rejected: leon.csv, Source: 825693450, Destination: 600, Line: 1


This message is shown when a file is rejected. Neither the source or the destination appear in the RANGE EXT sections. If you do not want these entries to process, they should be removed from the file before they are processed by the Tes CSV Converter.
In this case the "600" is a device that we can add to the RANGE EXT section to process through to TES.
"Line 1" shows which entry int he file is the problem line.
----
Leon van Heerden
http://www.datatex.co.za
Leon van Heerden
 
Posts: 332
Joined: Fri Oct 29, 2010 11:21 am
Location: Cape Town

Return to TNG

Who is online

Users browsing this forum: No registered users and 1 guest