Re: Assistance with importing a csv into a PostgreSQL database

2009/8/6 Intengu Technologies <sindile(dot)bidla(at)gmail(dot)com>:
> Dear List,

Hey, a fellow South African :)

> Please assist me with how to import a csv file into PostgreSQL that
> has the following structure - http://pastebin.com/m56bb6cb2.
>
> The challenges are these:
> 1. import the whole csv into one table - problem is the number of
> fields will not be the same for the various rows, the csv has no field
> names
> 2. split this one table into the various tables - a total of 6 tables
> 3. update the field names with the correct names

I think it makes the most sense to split the CSV into separate files
for the different tables and deal with them separately instead of
trying to import the whole lot into one table and then splitting it up
afterwards.

One way to do that would be to import the data into a spreadsheet and
then sort by the first column. The cut and paste the data for the
different tables into different spreadsheets and export them to
separate CSV files.

Or you could write a script to separate it for you. e.g. the
following Python script should split into different files, assuming
the original file is called original.csv the new files will be called
table1.csv, table2.csv etc.

---[ cut here ]---
#!/usr/bin/env python

import csv

def get_csv_writer(filename):
return csv.writer(open(filename, "w"), quoting=csv.QUOTE_ALL)

infile = open("original.csv")
table = {"1": get_csv_writer("table1.csv"),
"2": get_csv_writer("table2.csv"),
"3": get_csv_writer("table3.csv"),
"4": get_csv_writer("table4.csv"),
"5": get_csv_writer("table5.csv"),
"6": get_csv_writer("table6.csv")}

reader = csv.reader(infile)
for line in reader:
table[line[0]].writerow(line[1:])
---[ cut here ]---

You could also just insert the data from the script instead of
outputting new CSV files.

P.S. I've done a quick test of the above script using your sample
data, but there's no error checking in the script, so you will have to
see if it outputs sensible data based on the real CSV file.

--
Michael Wood <esiotrot(at)gmail(dot)com>