Re: Assistance with importing a csv into a PostgreSQL database - Mailing list pgsql-novice

Fellow South African indeed ;)

I run windows and have installed Python - forgive me I am not a
programmer and my data is on an external HD.
I then modified your script to be like this
import csv
>
> def get_csv_writer(filename):
>     return csv.writer(open(filename, "w"), quoting=csv.QUOTE_ALL)
>
> infile = open("F:\data\myfile.csv")
> table = {"1": get_csv_writer("F:\data\myfiletable1.csv"),
>          "2": get_csv_writer("F:\data\myfiletable2.csv"),
>          "3": get_csv_writer("F:\data\myfiletable3.csv"),
>          "4": get_csv_writer("F:\data\myfiletable4.csv"),
>          "5": get_csv_writer("F:\data\myfiletable5.csv"),
>          "6": get_csv_writer("F:\data\myfiletable6.csv")}
>
> reader = csv.reader(infile)
> for line in reader:
>     table[line[0]].writerow(line[1:])

I then saved this as importcsv.py

Opened it in python IDE, but I am getting syntax errors.


On 06/08/2009, Michael Wood <esiotrot@gmail.com> wrote:
> 2009/8/6 Intengu Technologies <sindile.bidla@gmail.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@gmail.com>
>


--
Sindile Bidla