ComtradeTools is a command line program that can be used to obtain data via UN Web Services
and to convert it from SDMX Cross-Sectional into CSV format (user can select which attributes
to be converted) and import it into SQL Server Database.

Revision History
• 24-March-2010: Added proxy in the config file. For those that use the proxy server
• 16-Feb-2010: Added AuthorizationCode in the config file. Used to access UN Comtrade web services outside the IP address range.
• 11-07-2008: Added compression parameter. If set to false, compression is not used during data exchange. Note: Error may occur during large data exchange (>300MB), if compression set to true. In this case, please set compression to false.
• 15-08-2005: Added resume parameter. The program will be resumed automatically for n times.



Step by Step Instructions
Before continuing, make sure that you have access to UN Comtrade Web Services by clicking this link: http://comtrade.un.org/ws/CheckRights.aspx

1. Get ComtradeTools from
http://comtrade.un.org/ws/att/comtradeToolsSetupMar2010.zip
http://comtrade.un.org/ws/att/comtradeToolsSetupFeb2010.zip 

2. Install it (Can be in any directory). Default is C:\Program Files\UNSD\ComtradeTools\

3. Test Web Service by typing/copy-paste the following url in web browser:

a) http://comtrade.un.org/ws/getSdmxV1.aspx?px=H1&r=381&y=2003,2002&cc=TOTAL&p=0&comp=false
--> for user authentication using IP address ranges
or
b) http://comtrade.un.org/ws/getSdmxV1.aspx?px=H1&r=381&y=2003,2002&cc=TOTAL&p=0&comp=false&code=yourauthorizationcode
 --> for user authentication using authorization code (get the code from http://comtrade.un.org/db/u/uAccountInfo.aspx)

4. Open the folder C:\Program Files\UNSD\ComtradeTools\

5. Try to use ComtradeTools to download data in CSV: Indonesia 2003 (HS1996)
- open command line windows
- go to ComtradeTools directory
- run this command:
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndConvertToCSV
- the result is H12003360_CSV.txt
- try to run the same command by changing the output directory (to c:\temp\sdmx)
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndConvertToCSV /outputDirectory:c:\temp\sdmx\

6. Change and modify the column position in CSV file:

* Add REPORTED_CURRENCY and CONVERSION_FACTOR
* Remove REPORTER and year
- open ComtradeTools.exe.config (in C:\Program Files\UNSD\ComtradeTools\)
- change the entry AttributeMapping to
<add key="AttributeMapping" value="REPORTED_CURRENCY=cur; CONVERSION_FACTOR=cf; CL=classification;
TF=flow; CC-XX=commodity; PRT=partner; netweight=netweight; qty=quantity; QU=QuantityUnit;value=TradeValue" />
- run the command again
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndConvertToCSV /outputDirectory:c:\temp\sdmx\
* This shows that any sdmx attributes can shown in csv
* RE-ADD REPORTER and YEAR
<add key="AttributeMapping" value="RPT=reporter; time=year; REPORTED_CURRENCY=cur; CONVERSION_FACTOR=cf; CL=classification; TF=flow; CC-XX=commodity; PRT=partner; netweight=netweight; qty=quantity; QU=QuantityUnit;value=TradeValue" />

7. Use project to download CSV file (for all H1-Indonesia)

- open ComtradeTools.exe.config (in C:\Program Files\UNSD\ComtradeTools\)
- add entry:
<add key="IndonesiaH1" value="LastSynch=;SynchOption=TIMESTAMP;Parameters=/r:360 /px:H1" />
- run the command line
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndConvertToCSV
/outputDirectory:c:\temp\sdmx\ /projectID:IndonesiaH1 /synchOption:Timestamp
* The command line will download the data availability and then loop through it, the name is da_H1ally360_IndonesiaH1_COMTRADE.xml
* At the end, the timestamp will be updated

8. Use Import to SQL feature

- Install the latest SQL Client Component (http://www.microsoft.com/sql/downloads/2000/sp3.asp file: sql2ksp3.exe)
* if the latest client is not installed the error message is:
"Unhandled Exception: System.InvalidCastException: QueryInterface for interface D TS.CustomTask failed."
- Create destination table (in any database):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempLoad]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempLoad]
GO
CREATE TABLE [dbo].[tempLoad] (
[pfCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rgCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rtCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ptCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cmdCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cmdID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qtCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeQuantity] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetWeight] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currency] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[conversion_factor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO - Setup the connection string (in ComtradeTools.exe.config)
<add key="SQLDestConnectionString" value="UserName=sdmx;Password=sdmx;Server=(local);Database=Northwind;Table=tempLoad" />
* Note there is a special keyword CC (classification), RR, (reporter), YY (year) so that the destination table can be customized based on reporter and/or year and/or classification

- Set up the mapping from CSV to table (note: CASE-SENSITIVE)
<add key="SQLColumnMapping" value="reporter=rtCode;classification=pfCode;flow=rgCode;commodity=cmdCode;year=yr;
partner=ptCode;TradeValue=TradeValue;netweight=NetWeight;quantity=TradeQuantity;
cur=currency;cf=conversion_factor" />
--add any constant values
<add key="SQLConstValueMapping" value="source=UN" />

--try to import one dataset
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndImportToSQLDB
ComtradeTools /r:360 /y:2003 /px:H1 /action:DownloadAndImportToSQLDB /removeTemp:false /DTSDebug:true
--> create DTS Package without running it

*Check tempLoad table in database

9. Use projec to import datasets into SQL

-Reset the IndonesiaH1 LastSynch
<add key="IndonesiaH1" value="LastSynch=;SynchOption=TIMESTAMP;Parameters=/r:360 /px:H1" />

-reRun the command with action
ComtradeTools /action:DownloadAndImportToSQLDB /outputDirectory:c:\temp\sdmx\ /projectID:IndonesiaH1
/synchOption:Timestamp

10. Use the resume parameter for task no.9

-Reset the IndonesiaH1 LastSynch
<add key="IndonesiaH1" value="LastSynch=;SynchOption=TIMESTAMP;Parameters=/r:360 /px:H1" />

-reRun the command with action
ComtradeTools /action:DownloadAndImportToSQLDB /outputDirectory:c:\temp\sdmx\
/projectID:IndonesiaH1 /synchOption:Timestamp /resume:3
*ComtradeTools will resume 3 times if it encounters runtime error

11. Use Authorization Code for web services authentication

- open ComtradeTools.exe.config (in C:\Program Files\UNSD\ComtradeTools\)
- add your authorization code to the entry AuthorizationCode to
<add key="AuthorizationCode" value="GetTheCodeFromUNComtrade|Database|MyAccount|AccountInfo" />