Loading csv files into tables

Learn how to load a cvs file into a table.

Procedure

  1. Create a table. 
    create table TABLE NAME (date Date, Location VARCHAR(100), MinTemp REAL, MaxTemp REAL, Rainfall REAL, Evaporation REAL,Sunshine REAL, WindGustDir VARCHAR(20), WindGustSpeed INTEGER, WindDir9am VARCHAR(10), WindDir3pm VARCHAR(10), WindSpeed9am INTEGER, WindSpeed3pm INTEGER, Humidity9am INTEGER, Humidity3pm INTEGER, Pressure9am REAL, Pressure3pm REAL, cloud9am VARCHAR(10), cloud3pm VARCHAR(10), Temp9am REAL, Temp3pm REAL, RainToday varchar(30), RISK_MM REAL, RainTomorrow varchar(30));
    Example: 
    create table weather (date Date, Location VARCHAR(100), MinTemp REAL, MaxTemp REAL, Rainfall REAL, Evaporation REAL,Sunshine REAL, WindGustDir VARCHAR(20), WindGustSpeed INTEGER, WindDir9am VARCHAR(10), WindDir3pm VARCHAR(10), WindSpeed9am INTEGER, WindSpeed3pm INTEGER, Humidity9am INTEGER, Humidity3pm INTEGER, Pressure9am REAL, Pressure3pm REAL, cloud9am VARCHAR(10), cloud3pm VARCHAR(10), Temp9am REAL, Temp3pm REAL, RainToday varchar(30), RISK_MM REAL, RainTomorrow varchar(30));
  2. Load the cvs file into the table: 
    nzload -df TABLE NAME
    nzload -df weatherAUS.csv -t weather -db weather -nullValue NA -boolStyle YES_NO -skipRows 1 -delim , -dateStyle MDY -dateDelim '/'
  3. For tables, which do not have an identity column (like the weather table), create an identity column and set the value with rowid: 
    alter table weather add column id bigint;
update weather set id=rowid;