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/comtradeToolsSetupJuly2008.zip
2. Install it (Can be in any directory). Default is C:\Program Files\UNSD\ComtradeTools\
3. Test Web Service by typing
http://comtrade.un.org/ws/getSdmxV1.aspx?px=H1&r=381&y=2003,2002&cc=TOTAL&p=0&comp=false
in web browser
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