Difference between revisions of "Rate import from CSV"

From Kolmisoft Wiki
Jump to navigationJump to search
 
(71 intermediate revisions by 12 users not shown)
Line 12: Line 12:
First line can be header.  
First line can be header.  


'''Value Separator''' and '''Decimal Separator''' MUST be set correctly in '''SETTINGS - Setup - Settings - Functionality''':
''Value Separator'' and ''Decimal Separator'' MUST be set correctly in '''SETTINGS -> Setup -> Settings -> Functionality''':


*  CSV Column Separator
*  CSV Column Separator
*  CSV Decimal Separator
*  CSV Decimal Separator
 
<br><br>
[[File:Settings-functionality.png]]
<br><br>
File and CSV separator settings should match. If they do not, you will get errors when trying to import file.
File and CSV separator settings should match. If they do not, you will get errors when trying to import file.


* [[Good CSV rate file examples]]
* [[Good CSV rate file examples]]
* [[Bad CSV rate file examples]]
* [[Bad CSV rate file examples]]
 
<br><br>
=== Where can I get an example file with available prefixes? ===
=== Where can I get an example file with available prefixes? ===


Go to '''SETTINGS - Billing - Tariffs''' and click on [[Image:icon_excel.png]] '''Destinations CSV'''.
Go to '''SETTINGS -> Billing -> Tariffs''' and click on [[Image:icon_excel.png]] '''Destinations CSV'''.
 
 
<br><br>
<br><br>
= Description =
= Description =


It is very tedious work to add several thousands rates by hand so you can import them from CSV file.
It is very tedious work to add several thousand rates by hand, so you can import them from a CSV file.


There are 7 steps for importing rates from CSV file:
There are seven steps for importing rates from a CSV file:


# File upload
# File upload
Line 43: Line 42:
# Creating new rates
# Creating new rates


We will cover each of them one by one.
We will cover each of these one by one.
 
'''IMPORTANT NOTE: When importing - prefixes should be in E.164 format!!! E.g. no international prefixes!'''
 
 


'''IMPORTANT NOTE: When importing, prefixes should be in E.164 format!!! For example, no international prefixes!'''
<br><br>
== 1. File upload ==
== 1. File upload ==


Select [[Image:excel.png]] '''Import from CSV''' for the Tariff you want to import rates.
Select [[Image:excel.png]] '''Import from CSV''' for the Tariff you want to import rates.


Then press on '''Browse''' and select CSV file with rates. Press '''Upload file''':
Then click on '''Browse''' and select CSV file with rates. Click '''Upload file''':
 
<br><br>
[[Image:rateimportfromcsv1.png]]
[[Image:rateimportfromcsv1.png]]
<br><br>
== 2.1 Column assignment - wrong separators detected ==
Sometimes the system is able to detect that CSV separators are wrong and suggests you to change them:
<br><br>
[[File:Tariff_import_wrong_separators.png]]
<br><br>
== 2.2 Column assignment ==


The file is uploaded and the first five lines of the file are available to check:
<br><br>
[[Image:rateimportfromcsv2.png]]
<br><br>
This helps us in the next step, to assign CSV file's columns to MOR's variables. We have the following window:
<br><br>
[[Image:rateimportfromcsv3_2.png]]
<br><br>
Here we must assign columns from the CSV file. '''Prefix''' and '''Rate''' are mandatory fields. All other fields can be empty. If during import system will find corresponding headers in CSV file, columns will be assigned automatically. You just need to check is it correct.


'''[[Rate Effective From | Effective from]]''' date can be imported in various formats of date. If there is no Effective From date in CSV file you are able to set that date manually, otherwise Default value (it is now) will be taken.


== 2. Column assignment ==
In addition, you can also use of one of the '''quick''' buttons to set the time to '''Now''' (this will give you a full current time, e.g. 2015-09-29 19:06:21) or '''Start of today''' (e.g. 2015-09-29 00:00:00).


File is uploaded and 5 first line of this file is available to check:
'''Connection Fee''', '''Increment''' and '''Min. Time''' have their defaults. If you wish, you can enter values manually  for them. Manually entered values have the highest priority. If you omit them, then MOR inserts values from the CSV file. If there is no assigned column for these fields, then default values will be added to database (DB).


[[Image:rateimportfromcsv2.png]]
You can also import '''Ghost Percent''' with the rates. '''Ghost Percent''' only affects remaining time value, which is said by IVR if device has [[Device_settings#Tell_Options | Tell time ]] option enabled. It works the same way as the '''Ghost minute percent''' in the [[Calling_Card_Groups | Calling Cards Groups]]: it is a value to trick the user into thinking he has more minutes when he really has less. This affects only tell time values, actual call is billed according to real rates and ghost percent does not affect billing in any way.


This helps us in next step, to assign CSV file‟s columns to MOR‟s variables. We have following window:
'''Action''' - If this column have keyword DELETE, corresponding rate (prefix) is deleted if already exists in the tariff. If such rate does not exist in the system it will be ignored (not imported). All other values except DELETE in Action column are ignored.
'''Date Format''' - Choose Date Format from the file. Below are supported date formats:
<br>
[[File:Dateformat.png]]


[[Image:rateimportfromcsv3.png]]
This way of selecting CSV rows and assigning them to MOR values lets us import various CSV file formats.
<br><br>
===Additional options===
<br><br>
[[File:Importfromcsv.png]]
<br><br>
'''Rate Day Type'''<br>
<!---This option is available starting from [[MOR_9_documentation | MOR 9]]--->
It is possible to select to which Day Type rates apply before importing them.


Here we must to assign columns from CSV file. '''Prefix''' and '''Rate''' are mandatory fields. All other fields can be empty.
So in two steps it is possible to import separate Rates for Work Days and Free Days, or (in a single step) to import rates for All Days at once.
'''
Connection Fee''', '''Increment''' and '''Min. Time''' have their defaults, if you want, you can enter manually values for them. Manually entered values have highest priority. If you omit them, then MOR insert values from CSV file, if there‟re no assigned column for these fields, then default values will be added to database (DB).


There are group of values under name '''USA specifics'''. These are only for USA/Canada. Users from these countries need to use these fields with their CSV files.
'''On/Off Peak Tariff import'''<br>
<!---This option is available starting from [[MOR_10_documentation | MOR 10]]--->
If you need to import tariffs for on/off peak periods, all you need to do is upload another tariff on top of the one you have with a different
time period.
* You are now able to specify peak and off-peak periods (hours, minutes, seconds) when importing tariffs for work days and weekdays.
* Important notice - time periods cannot cover each other.
* If for example you upload a tariff from 8am to 12pm. The Tariff that is uploaded on top can only be from 12pm to 8am.


In the following example we selected fields which match our CSV file. Also entered some manual values for '''Increment''' and '''Min.Time'''. We will count time by 30s increments and minimum time for the call will be 60s with these settings:
'''Update Destination Names from file'''<br>


[[Image:rateimportfromcsv4.png]]
When this option is selected destination name is updated from csv file during rate import.


Such way of selecting CSV rows and assigning them to MOR values lets us import various CSV file formats.
'''Delete Rates for Prefixes which are not present in imported file'''


This option allows you to delete existing rates, which will not be updated. (The prefixes that belong to these rates are not a part of the file being imported).


=== Rate Day Type ===
This option will delete Prefix and Rate from the tariff.


''This option is available starting from [[MOR_9_documentation | MOR 9]]''
'''Warn about Rates greater than'''<br>
This option is is used in Tariff analysis to warn about Rates which are greater than a certain value. This check is disabled by entering 0. '''Note''': The setting does not affect Rate import in any way since it is only used for informational purposes.


It is possible to select to which Day Type rates apply before importing them.
'''Ignore Effective from time when updating Rates'''<br>
 
When this option is selected only the date part of Effective from value is matched when updating Rates. The option is useful when the CSV file with rates contains only date as Effective from. '''Note''': when there are several rates for the same date and this options is enabled, all the rates for this date will be updated.
So in 2 steps it is possible to import separate rates for Work Days and Free Days or in 1 step - for All Days at once.
<br><br>
 
[[Image:rate_day_type.png]]


== 3. Column confirmation ==
== 3. Column confirmation ==


Next step is to review our selection and confirm if everything is correct. We can go Back or forward to the next step:
The next step is to review our selection and confirm that everything is correct. We can go Back or forward to the next step:
 
<br><br>
[[Image:rateimportfromcsv5.png]]
[[Image:rateimportfromcsv5.png]]
 
<br><br>
 
'''NOTE''' Steps where is nothing to do  (value = 0) will be skipped.
<br><br>


== 4. Analysis ==
== 4. Analysis ==


It will take some time to accomplish this step. Now MOR is checking a lot of things from DB and CSV files: comparing rates, searching for duplicates, fixing small errors and so on. We get something like this after analysis is completed:
It will take some time to accomplish this step. MOR checks many things from DB and CSV files: it compares rates, searches for duplicates, fixes small errors and so on. After the analysis is completed, we get something like this :
<br><br>
[[Image:Rate_import_analize.png]]
<br><br>
The most interesting line is '''737 bad destination(s)'''. We click on the List to check them. In the new window, we can see which destinations are bad and we can decide what to do with them. Is it possible that there are no directions for them, MOR doesn't know to which country to assign this destination, or maybe these destinations have duplicates in CSV file? You have to fix this if you want to import the whole file. Here you can stop and fix your CSV file, or you can ignore these errors and continue.<br>
Line '''Zero Rates in CSV file''' displays number of rates wich has zero value. These rates will not be ignored, and will be imported.<br>
Line '''High Rates''' displays a number of rates which are greater than a value set in '''Warn about Rates greater than''' setting. These Rates will still be imported.<br>
We continue the demo by clicking ''Proceed''.


[[Image:rateimportfromcsv6.png]]
The following steps are separated to avoid excessive demands on processing power if a rate file is huge. This keeps the browser from crashing and allows it to successfully import 160.000+ destinations from one CSV file (this has been tested).
<br><br>


The most interesting line is '''3 bad destination(s)'''. We press on the List to check them. In the new window we can see which destinations are bad and we can decide what to do with them. Possible that there are no directions for them, MOR cannot know to which country to assign this destination or maybe these destinations have duplicates in CSV file? You have to fix this if you want to import whole file. Here you can stop and fix your CSV file or you can ignore these errors and continue.
== 5.1. Creating Destinations ==


We will continue due to demo purposes pressing on '''Proceed'''.
MOR creates new destinations here. These Destinations are necessary for the Rates, which will be added in step 7. This window is just informative, no decisions here:


Following steps are separated to divide big processing power if rate file is huge. It allows browser not to crash and successfully import 160.000+ destinations from one CSV file. (Tested).
MOR creates only Destinations for prefixes which are not yet in MOR's DB and determines whether it is possible to find out the Direction (Country) for these new prefixes (Destinations).


== 5. Creating destinations ==
This may be done in two ways:


MOR creates new destinations here. These destinations are necessary for the rates which will be added in step 7. This window is just informative, no decisions here:
1. The system checks the available prefixes for the best match and selects found Direction as a basis for the new prefix (Destination).


MOR creates only Destinations for prefixes which are not in MORs DB yet _AND_ if it is possible to find out the Direction (Country) fro these new prefixes (Destinations).
For example: if you try to import the rate for prefix 370630 and this prefix is unknown to MOR, MOR checks the longest matching prefix - 3706 in this case - and finds out that it is Lithuania Mobile. So it creates a new Destination with Prefix 370630 in which the Direction will be Lithuania.
 
It is possible in 2 ways:
 
1. System checks available prefixes for best match and selects found Direction as basis for new prefix (Destination).
 
Example: if you try to import rate for prefix 370630 and this prefix is unknown for MOR, MOR checks longest matching prefix - 3706 for this case and founds out that it is Lithuania MOB, so it will create new Destination with Prefix 370630 which Direction will be Lithuania and subcode MOB (mobile network).
 
2. If Country code is available in imported file, MOR assigns such Direction to newly created Destination.
 
Example: if again you try to import 370630 and it is unknown to MOR but in same CSV line for Country Code field you have LTU, MOR will create such Destination with Direction = Lithuania.


2. If Country code is available in the imported file, MOR assigns this Direction to the newly created Destination.


For example: if again you try to import 370630 and it is unknown to MOR, but you have LTU in same CSV line for Country Code field, MOR will create such a Destination with Direction = Lithuania.
<br><br>
[[Image:rateimportfromcsv7.png]]
[[Image:rateimportfromcsv7.png]]
<br><br>
'''NOTE''' Because Destination cannot be empty (space), during the Destination creation, MOR automatically sets Destination name as Direction, Destination name will be Direction.
<br><br>
== 5.2. Updating Destinations ==


Step appears when '''Update Destination Names from file''' is check in second step.
<br><br>
== 6. Updating rates ==
== 6. Updating rates ==


Here MOR updates rates with the values from CSV files. These rates already exist in DB. Just press '''Proceed''':
To update existing rates your CSV file should contain prefixes that already exist in Tariff. Also, it should contain the exact ''Effective from'' date. If your CSV file does not contain Effective from date then you can set it manually in Step 2, just make sure that date is the same as in Tariff. Only then rates will be updated. If there are no existing rates with the exact prefix and effective from then new rates will be created.<br>


Here MOR updates rates with the values from CSV files. These rates already exist in DB. Just click ''Proceed'':
<br><br>
[[Image:rateimportfromcsv8.png]]
[[Image:rateimportfromcsv8.png]]
 
<br><br>
 


== 7. Creating new rates ==
== 7. Creating new rates ==


Finally MOR creates all rates and we are all done!
Here, MOR creates rates that have not existed previously. Click ''Proceed'' afterwards.
 
<br><br>
[[Image:rateimportfromcsv9.png]]
[[Image:rateimportfromcsv9.png]]
<br><br>
== 8. Deleting rates ==


This is the final step. MOR deletes the rates which were not a part of the file being imported.
You're all done!


== NOTES ==
== NOTES ==


Rate import is _VERY_ CPU intensive task.
Rate import is VERY CPU-intensive.


If there are a lot of rates please be patient and let MOR finish its job. Sometimes it gets some time to count everything and come back to life.  
If there are a lot of rates, please be patient and let MOR finish its job. It may take some time to count everything and come back to life.  


At the moments it could seem that browser is not responding – that is not true – it just works very hard importing your very own rates from CSV file.  
At some point it may seem that browser is not responding. That is not the case – it is just working very hard, importing your very own rates from the CSV file.  


If you get timeout error try to import file in smaller pieces.
If you get a timeout error, try to import the file in smaller pieces.


= Destination to create from CSV =
= Destination to create from CSV =


When importing rates from CSV file now you can check which Destinations will be created automatically.
When importing rates from the CSV file, you can check which Destinations will be created automatically.


You can view them in separate window when you press List in step 4 in field Destinations to create.
You can view them in a separate window when you click List in step 4 in the field "Destinations to create".


Such destinations do not exist in MOR database and MOR will create them when importing rate file.
Such destinations do not exist in MOR's database but MOR will create them when importing the rate file.


More info: [[Integrity Check]]
More info: [[Integrity Check]]
 
<br><br>
= Example Videos =
= Example Videos =


<flashow>http://wiki.kolmisoft.com/videos/rates_import_from_csv.swf|width=600|height=455</flashow>


https://wiki.kolmisoft.com/videos/rates_import_from_csv.gif




Similar video with different tariff:
Similar video with different tariff:


<flashow>http://wiki.kolmisoft.com/videos/tariff_import_from_csv.swf|width=800|height=618</flashow>


https://wiki.kolmisoft.com/videos/tariff_import_from_csv.gif
<br><br>
Another example:
<html>
<iframe width="640" height="360" src="https://www.youtube.com/embed/XzqORQHpgpo" frameborder="0"></iframe>
</html>
Another example with Provider tariff:


<html>
<iframe width="640" height="360" src="https://www.youtube.com/embed/OVlrAJ5_a44" frameborder="0"></iframe>
</html>


= See also =
= See also =


* [[Importing Tariffs from CSV with wrong Regional Settings]]
* [[Good CSV rate file examples]]
* [[Good CSV rate file examples]]
* [[Bad CSV rate file examples]]
* [[Bad CSV rate file examples]]
* [[How to create a csv file]]

Latest revision as of 08:05, 9 December 2022

File format

File should be CSV (comma separated values).

Necessary fields in CSV line:

  • Prefix
  • Price

No other fields are necessary.

First line can be header.

Value Separator and Decimal Separator MUST be set correctly in SETTINGS -> Setup -> Settings -> Functionality:

  • CSV Column Separator
  • CSV Decimal Separator



Settings-functionality.png

File and CSV separator settings should match. If they do not, you will get errors when trying to import file.



Where can I get an example file with available prefixes?

Go to SETTINGS -> Billing -> Tariffs and click on Icon excel.png Destinations CSV.

Description

It is very tedious work to add several thousand rates by hand, so you can import them from a CSV file.

There are seven steps for importing rates from a CSV file:

  1. File upload
  2. Column assignment
  3. Column confirmation
  4. Analysis
  5. Creating destinations
  6. Updating rates
  7. Creating new rates

We will cover each of these one by one.

IMPORTANT NOTE: When importing, prefixes should be in E.164 format!!! For example, no international prefixes!

1. File upload

Select Excel.png Import from CSV for the Tariff you want to import rates.

Then click on Browse and select CSV file with rates. Click Upload file:

Rateimportfromcsv1.png

2.1 Column assignment - wrong separators detected

Sometimes the system is able to detect that CSV separators are wrong and suggests you to change them:

Tariff import wrong separators.png

2.2 Column assignment

The file is uploaded and the first five lines of the file are available to check:

Rateimportfromcsv2.png

This helps us in the next step, to assign CSV file's columns to MOR's variables. We have the following window:

Rateimportfromcsv3 2.png

Here we must assign columns from the CSV file. Prefix and Rate are mandatory fields. All other fields can be empty. If during import system will find corresponding headers in CSV file, columns will be assigned automatically. You just need to check is it correct.

Effective from date can be imported in various formats of date. If there is no Effective From date in CSV file you are able to set that date manually, otherwise Default value (it is now) will be taken.

In addition, you can also use of one of the quick buttons to set the time to Now (this will give you a full current time, e.g. 2015-09-29 19:06:21) or Start of today (e.g. 2015-09-29 00:00:00).

Connection Fee, Increment and Min. Time have their defaults. If you wish, you can enter values manually for them. Manually entered values have the highest priority. If you omit them, then MOR inserts values from the CSV file. If there is no assigned column for these fields, then default values will be added to database (DB).

You can also import Ghost Percent with the rates. Ghost Percent only affects remaining time value, which is said by IVR if device has Tell time option enabled. It works the same way as the Ghost minute percent in the Calling Cards Groups: it is a value to trick the user into thinking he has more minutes when he really has less. This affects only tell time values, actual call is billed according to real rates and ghost percent does not affect billing in any way.

Action - If this column have keyword DELETE, corresponding rate (prefix) is deleted if already exists in the tariff. If such rate does not exist in the system it will be ignored (not imported). All other values except DELETE in Action column are ignored. Date Format - Choose Date Format from the file. Below are supported date formats:
Dateformat.png

This way of selecting CSV rows and assigning them to MOR values lets us import various CSV file formats.

Additional options



Importfromcsv.png

Rate Day Type
It is possible to select to which Day Type rates apply before importing them.

So in two steps it is possible to import separate Rates for Work Days and Free Days, or (in a single step) to import rates for All Days at once.

On/Off Peak Tariff import
If you need to import tariffs for on/off peak periods, all you need to do is upload another tariff on top of the one you have with a different time period.

  • You are now able to specify peak and off-peak periods (hours, minutes, seconds) when importing tariffs for work days and weekdays.
  • Important notice - time periods cannot cover each other.
  • If for example you upload a tariff from 8am to 12pm. The Tariff that is uploaded on top can only be from 12pm to 8am.

Update Destination Names from file

When this option is selected destination name is updated from csv file during rate import.

Delete Rates for Prefixes which are not present in imported file

This option allows you to delete existing rates, which will not be updated. (The prefixes that belong to these rates are not a part of the file being imported).

This option will delete Prefix and Rate from the tariff.

Warn about Rates greater than
This option is is used in Tariff analysis to warn about Rates which are greater than a certain value. This check is disabled by entering 0. Note: The setting does not affect Rate import in any way since it is only used for informational purposes.

Ignore Effective from time when updating Rates
When this option is selected only the date part of Effective from value is matched when updating Rates. The option is useful when the CSV file with rates contains only date as Effective from. Note: when there are several rates for the same date and this options is enabled, all the rates for this date will be updated.

3. Column confirmation

The next step is to review our selection and confirm that everything is correct. We can go Back or forward to the next step:

Rateimportfromcsv5.png

NOTE Steps where is nothing to do (value = 0) will be skipped.

4. Analysis

It will take some time to accomplish this step. MOR checks many things from DB and CSV files: it compares rates, searches for duplicates, fixes small errors and so on. After the analysis is completed, we get something like this :

Rate import analize.png

The most interesting line is 737 bad destination(s). We click on the List to check them. In the new window, we can see which destinations are bad and we can decide what to do with them. Is it possible that there are no directions for them, MOR doesn't know to which country to assign this destination, or maybe these destinations have duplicates in CSV file? You have to fix this if you want to import the whole file. Here you can stop and fix your CSV file, or you can ignore these errors and continue.
Line Zero Rates in CSV file displays number of rates wich has zero value. These rates will not be ignored, and will be imported.
Line High Rates displays a number of rates which are greater than a value set in Warn about Rates greater than setting. These Rates will still be imported.
We continue the demo by clicking Proceed.

The following steps are separated to avoid excessive demands on processing power if a rate file is huge. This keeps the browser from crashing and allows it to successfully import 160.000+ destinations from one CSV file (this has been tested).

5.1. Creating Destinations

MOR creates new destinations here. These Destinations are necessary for the Rates, which will be added in step 7. This window is just informative, no decisions here:

MOR creates only Destinations for prefixes which are not yet in MOR's DB and determines whether it is possible to find out the Direction (Country) for these new prefixes (Destinations).

This may be done in two ways:

1. The system checks the available prefixes for the best match and selects found Direction as a basis for the new prefix (Destination).

For example: if you try to import the rate for prefix 370630 and this prefix is unknown to MOR, MOR checks the longest matching prefix - 3706 in this case - and finds out that it is Lithuania Mobile. So it creates a new Destination with Prefix 370630 in which the Direction will be Lithuania.

2. If Country code is available in the imported file, MOR assigns this Direction to the newly created Destination.

For example: if again you try to import 370630 and it is unknown to MOR, but you have LTU in same CSV line for Country Code field, MOR will create such a Destination with Direction = Lithuania.

Rateimportfromcsv7.png

NOTE Because Destination cannot be empty (space), during the Destination creation, MOR automatically sets Destination name as Direction, Destination name will be Direction.

5.2. Updating Destinations

Step appears when Update Destination Names from file is check in second step.

6. Updating rates

To update existing rates your CSV file should contain prefixes that already exist in Tariff. Also, it should contain the exact Effective from date. If your CSV file does not contain Effective from date then you can set it manually in Step 2, just make sure that date is the same as in Tariff. Only then rates will be updated. If there are no existing rates with the exact prefix and effective from then new rates will be created.

Here MOR updates rates with the values from CSV files. These rates already exist in DB. Just click Proceed:

Rateimportfromcsv8.png

7. Creating new rates

Here, MOR creates rates that have not existed previously. Click Proceed afterwards.

Rateimportfromcsv9.png

8. Deleting rates

This is the final step. MOR deletes the rates which were not a part of the file being imported. You're all done!

NOTES

Rate import is VERY CPU-intensive.

If there are a lot of rates, please be patient and let MOR finish its job. It may take some time to count everything and come back to life.

At some point it may seem that browser is not responding. That is not the case – it is just working very hard, importing your very own rates from the CSV file.

If you get a timeout error, try to import the file in smaller pieces.

Destination to create from CSV

When importing rates from the CSV file, you can check which Destinations will be created automatically.

You can view them in a separate window when you click List in step 4 in the field "Destinations to create".

Such destinations do not exist in MOR's database but MOR will create them when importing the rate file.

More info: Integrity Check

Example Videos

rates_import_from_csv.gif


Similar video with different tariff:


tariff_import_from_csv.gif

Another example:

Another example with Provider tariff:

See also