Filtering Data Imports From the Client Server into Excel

When importing data from our client server into Excel you can filter the quantity of data brought in. This will speed up processing time when setting up and refreshing data. It will also reduce your excel file size.

This function is controlled in the ‘Connection Properties’ window. Here is how you navigate there. Click on “properties…”  before you submit you data import request.

In the ‘Connection Properties’ window of the data connection you can define what data you bring into Excel.  Go to the ‘Definition’ tab and you can see a ‘Command text’ text box. This will already contain an SQL statement required to search for the data. We can add to it to refine our search.

SQL code example in `command text`:

“  SELECT * FROM `bulker_arrivals`.`annual_simple`  “

You then need to add a “where” statement to refine the search.

For example:  “  where `commodity group` = ‘agriculture’ and `exporter` =’Denmark’ and `year` in(2020,2021,2022)  “

The whole statement together will look like this:

“ SELECT * FROM `bulker_arrivals`.`annual_simple` where `commodity group` = ‘agriculture’ and `exporter` =’Denmark’ and `year` in(2020,2021,2022) “

 

Here is a list of the fields you can select with examples:

 

Importer  => “ where `importer` = ‘Denmark’ “ or “ where `importer` in(‘Denmark’,’Sweden’)
Importer_cont => “ where `importer_cont` = ‘Europe’ “
Importer_subcont => “ where `importer_subcont` = ‘EU27’ “
Importer MZ => “ where `importer_subcont` = ‘Black Sea’ “
Exporter => “ where `exporter` = ‘China’ “
Exporter_cont => “where `exporter_cont` = ‘Asia’ “
Exporter_subcont => “where `exporter_subcont` = ‘Far East’ “
Exporter MZ => “ where `exporter_MZ` = ‘Arabian Gulf’ “
Year  => “ where `year` = 2022” or “ where `year`>= 2015” or “ where year in(2020,2021,2022)”
Yearmonth => “ where `yearmonth` = 202201  ”
Commodity => “ where `commodity` = ‘Wheat’  “
Commodity Group => “ where `commodity Group` = ‘Agriculture’  “

 

When in Excel you can refine your selection by selecting the ‘Connection Properties’ tab again under the “PivotTable Analyze; Change Data Source” or “Data;Properties”