European Central Bank historical rates import SQLite script

ecb_rates.sql

-- Run with:
-- $ sqlite3 ecb_rates.db
-- sqlite> .read ecb_rates.sql

drop table if exists eurofxref_hist;
drop table if exists ecb_rates;

create table ecb_rates (
  date text not null,
  curr text not null,
  rate numeric
);

-- Assumes the file has been downloaded to the same directory as the
-- script
.import eurofxref-hist.csv eurofxref_hist --csv

begin;
insert into ecb_rates (date, curr, rate) select Date, 'USD', case USD when 'N/A' then null else USD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'JPY', case JPY when 'N/A' then null else JPY end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'BGN', case BGN when 'N/A' then null else BGN end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'CYP', case CYP when 'N/A' then null else CYP end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'CZK', case CZK when 'N/A' then null else CZK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'DKK', case DKK when 'N/A' then null else DKK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'EEK', case EEK when 'N/A' then null else EEK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'GBP', case GBP when 'N/A' then null else GBP end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'HUF', case HUF when 'N/A' then null else HUF end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'LTL', case LTL when 'N/A' then null else LTL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'LVL', case LVL when 'N/A' then null else LVL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'MTL', case MTL when 'N/A' then null else MTL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'PLN', case PLN when 'N/A' then null else PLN end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'ROL', case ROL when 'N/A' then null else ROL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'RON', case RON when 'N/A' then null else RON end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'SEK', case SEK when 'N/A' then null else SEK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'SIT', case SIT when 'N/A' then null else SIT end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'SKK', case SKK when 'N/A' then null else SKK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'CHF', case CHF when 'N/A' then null else CHF end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'ISK', case ISK when 'N/A' then null else ISK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'NOK', case NOK when 'N/A' then null else NOK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'HRK', case HRK when 'N/A' then null else HRK end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'RUB', case RUB when 'N/A' then null else RUB end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'TRL', case TRL when 'N/A' then null else TRL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'TRY', case TRY when 'N/A' then null else TRY end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'AUD', case AUD when 'N/A' then null else AUD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'BRL', case BRL when 'N/A' then null else BRL end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'CAD', case CAD when 'N/A' then null else CAD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'CNY', case CNY when 'N/A' then null else CNY end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'HKD', case HKD when 'N/A' then null else HKD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'IDR', case IDR when 'N/A' then null else IDR end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'ILS', case ILS when 'N/A' then null else ILS end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'INR', case INR when 'N/A' then null else INR end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'KRW', case KRW when 'N/A' then null else KRW end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'MXN', case MXN when 'N/A' then null else MXN end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'MYR', case MYR when 'N/A' then null else MYR end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'NZD', case NZD when 'N/A' then null else NZD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'PHP', case PHP when 'N/A' then null else PHP end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'SGD', case SGD when 'N/A' then null else SGD end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'THB', case THB when 'N/A' then null else THB end from eurofxref_hist;
insert into ecb_rates (date, curr, rate) select Date, 'ZAR', case ZAR when 'N/A' then null else ZAR end from eurofxref_hist;
commit;