Best import approach? Delimiters in strings

[Andrew Taylor <andydtaylor@xxxxxxxxx>]

Hi,

A bulk import (script attached) is failing.

Error message:

psql:/home/andyt/projects/django-stringer/txc/stops_inport.txt:86: ERROR:  invalid input syntax for type double precision: "stop_lat"

I think the reason may be a delimiters in strings such as "Golders Green, Golders Green, stop GW"

490015496GW,"Golders Green, Golders Green, stop GW",,51.57207,-0.19549

What's a good way to handle this?

Thanks,

Andy

CREATE TABLE STOPS_DLR
(
  stop_id text,
  stop_name text,
  stop_desc text,
  stop_lat double precision,
  stop_lon double precision,
  zone_id text,
  stop_url text,
  mode text DEFAULT 'DLR'
)
WITH (
  OIDS=FALSE
);
ALTER TABLE STOPS_DLR
  OWNER TO django_dev;

CREATE TABLE STOPS_LondonBuses
(
  stop_id text,
  stop_name text,
  stop_desc text,
  stop_lat double precision,
  stop_lon double precision,
  zone_id text,
  stop_url text,
  mode text DEFAULT 'LondonBuses'
)
WITH (
  OIDS=FALSE
);
ALTER TABLE STOPS_LondonBuses
  OWNER TO django_dev;

CREATE TABLE STOPS_LondonRiverServices
(
  stop_id text,
  stop_name text,
  stop_desc text,
  stop_lat double precision,
  stop_lon double precision,
  zone_id text,
  stop_url text,
  mode text DEFAULT 'LondonRiverServices'
)
WITH (
  OIDS=FALSE
);
ALTER TABLE STOPS_LondonRiverServices
  OWNER TO django_dev;

CREATE TABLE STOPS_LondonUnderground
(
  stop_id text,
  stop_name text,
  stop_desc text,
  stop_lat double precision,
  stop_lon double precision,
  zone_id text,
  stop_url text,
  mode text DEFAULT 'LondonUnderground'
)
WITH (
  OIDS=FALSE
);
ALTER TABLE STOPS_LondonUnderground
  OWNER TO django_dev;

CREATE TABLE STOPS_Tramlink
(
  stop_id text,
  stop_name text,
  stop_desc text,
  stop_lat double precision,
  stop_lon double precision,
  zone_id text,
  stop_url text,
  mode text DEFAULT 'Tramlink'
)
WITH (
  OIDS=FALSE
);
ALTER TABLE STOPS_Tramlink
  OWNER TO django_dev;

copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_HY/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_CC/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonRiverServices from '/home/andyt/projects/django-stringer/txc/LondonRiverServices/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_LU/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_LD/GTFS/stops.txt' using delimiters ',';
copy STOPS_DLR from '/home/andyt/projects/django-stringer/txc/DLR/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_MN/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_ET/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_SK/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_SL/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_DC/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_IF/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_TE/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_EB/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_KE/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/LondonBuses/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_LC/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_CX/GTFS/stops.txt' using delimiters ',';
copy STOPS_Tramlink from '/home/andyt/projects/django-stringer/txc/Tramlink/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_CW/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonUnderground from '/home/andyt/projects/django-stringer/txc/LondonUnderground/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_ML/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_ME/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_SV/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_BE/GTFS/stops.txt' using delimiters ',';
copy STOPS_LondonBuses from '/home/andyt/projects/django-stringer/txc/OId_LG/GTFS/stops.txt' using delimiters ',';

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general