Create 'CREATE TABLE' statements with ease and types. With support for emitting copy.
Call csv2table with -h for help.
Requires python. If you wish to use this command with redshift, the botocore
library must be installed as well.
Installation:
```
curl https://raw.githubusercontent.com/f0rk/csv2table/master/csv2table > "$HOME/bin/csv2table"
chmod +x "$HOME/bin/csv2table"
```
Examples:
Imagine we have a file, colors.csv, with the following data:
Id,Color,Color Name,Description,Hex #,Inventory,Add Date
1,red,Red,Having the color of blood,#f00,0.25,2014-10-16
2,green,Green,Having the color of growing grass,#0f0,10.18,2014-08-25
3,blue,Blue,Having the color of the clear sky,#00f,4.67,2014-09-17
To emit a basic statement to create the table:
~$ csv2table --file /tmp/colors.csv
create table "colors" (
"Id" text,
"Color" text,
"Color Name" text,
"Description" text,
"Hex #" text,
"Inventory" text,
"Add Date" text
);
To emit a basic statement to create the table and import the data:
~$ csv2table --file /tmp/colors.csv --copy
create table "colors" (
"Id" text,
"Color" text,
"Color Name" text,
"Description" text,
"Hex #" text,
"Inventory" text,
"Add Date" text
);
copy "colors"("Id", "Color", "Color Name", "Description", "Hex #", "Inventory", "Add Date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
If the server cannot see the file, you will need to use --backslash, to use psql's \copy feature:
~$ csv2table --file /tmp/colors.csv --copy --backslash
create table "colors" (
"Id" text,
"Color" text,
"Color Name" text,
"Description" text,
"Hex #" text,
"Inventory" text,
"Add Date" text
);
\copy "colors"("Id", "Color", "Color Name", "Description", "Hex #", "Inventory", "Add Date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
Of course, these names are awful to work with in SQL, so let's fix that:
~$ csv2table --file /tmp/colors.csv --copy --backslash --lower --mogrify
create table "colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
\copy "colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
If you want the table to be in a particular schema, use the --schema argument:
~$ csv2table --file /tmp/colors.csv --copy --backslash --lower --mogrify --schema ryan
create table "ryan"."colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
\copy "ryan"."colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
You can also drop the table as well:
~$ csv2table --file /tmp/colors.csv --copy --backslash --lower --mogrify --schema ryan --drop
drop table if exists "ryan"."colors";
create table "ryan"."colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
\copy "ryan"."colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
However, I'd advise running that in a trasanction:
~$ csv2table --file /tmp/colors.csv --copy --backslash --lower --mogrify --schema ryan -1
begin;
drop table if exists "ryan"."colors";
create table "ryan"."colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
\copy "ryan"."colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from '/tmp/colors.csv' with csv header delimiter ',' quote '"';
commit;
Redshift is supported, too. You'll either need to AWS_ACCESS_KEY_ID and
AWS_SECRET_ACCESS_KEY or create an AWS_CREDENTIAL_FILE or create a config file,
somewhere, with the following information:
# csv2table redshift config
s3_account_id = XXXX
s3_private_key = XXXX
s3_bucket = ryan
You can then use it like the following, assuming you've specified everything in
your env:
~$ csv2table --file /tmp/colors.csv --copy --lower --mogrify --schema ryan --redshift-upload --redshift-bucket ryan
create table "ryan"."colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
copy "ryan"."colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from 's3://ryan/colors.csv' with credentials 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' csv ignoreheader 1 delimiter ',' quote '"';
With a config file:
~$ csv2table --file /tmp/colors.csv --copy --lower --mogrify --schema ryan --redshift ~/.aws/credentials --redshift-upload --redshift-bucket ryan
create table "ryan"."colors" (
"id" text,
"color" text,
"color_name" text,
"description" text,
"hex_" text,
"inventory" text,
"add_date" text
);
copy "ryan"."colors"("id", "color", "color_name", "description", "hex_", "inventory", "add_date") from 's3://ryan/colors.csv' with credentials 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' csv ignoreheader 1 delimiter ',' quote '"';