Thread: Best import approach? Delimiters in strings

On 02/16/2013 09:02 AM, Andrew Taylor wrote:
> 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?

What is the command string you are using to import the data?

>
> Thanks,
>
>
> Andy
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

The first value seems more suspect, should be "490015496GW". Mixing
numeric and string values is best enclosed in quotes.


"Golders Green, Golders Green, stop GW" is normal if you want the
entire string between the quotes to arrive in a single column (with
the comma).




On Sat, 2013-02-16 at 17:02 +0000, Andrew Taylor wrote:
> 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

Unfortunately my starting point is data in the format of that example line.
I hasn't spotted the lack of quotes on the first value. Given this format,
is my best bet to write a script transforming it pre-import to postgres?
I.e. are there no arguments I could pass to the import process to handle
this directly? Thanks
On 16 Feb 2013 17:02, "Andrew Taylor" <andydtaylor@gmail.com> wrote:

> 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
>

On 02/16/2013 09:52 AM, Andrew Taylor wrote:
> Unfortunately my starting point is data in the format of that example
> line. I hasn't spotted the lack of quotes on the first value. Given this
> format, is my best bet to write a script transforming it pre-import to
> postgres? I.e. are there no arguments I could pass to the import process
> to handle this directly? Thanks

Yes, but we need to see the actual import code i.e COPY table_name FROM
file_name

>
>

--
Adrian Klaver
adrian.klaver@gmail.com

On 02/16/2013 09:52 AM, Andrew Taylor wrote:
> Unfortunately my starting point is data in the format of that example
> line. I hasn't spotted the lack of quotes on the first value. Given this
> format, is my best bet to write a script transforming it pre-import to
> postgres? I.e. are there no arguments I could pass to the import process
> to handle this directly? Thanks
>

Ignore last post, just found attached file:)


--
Adrian Klaver
adrian.klaver@gmail.com

On 02/16/2013 09:52 AM, Andrew Taylor wrote:
> Unfortunately my starting point is data in the format of that example
> line. I hasn't spotted the lack of quotes on the first value. Given this
> format, is my best bet to write a script transforming it pre-import to
> postgres? I.e. are there no arguments I could pass to the import process
> to handle this directly? Thanks
>

Right now you are processing as text so:

http://www.postgresql.org/docs/9.2/interactive/sql-copy.html

Text Format

...Backslash characters (\) can be used in the COPY data to quote data
characters that might otherwise be taken as row or column delimiters. In
particular, the following characters must be preceded by a backslash if
they appear as part of a column value: backslash itself, newline,
carriage return, and the current delimiter character...

Option 1

If you want to continue to do that you need to escape the delimiter
character or create the *.txt file with a different delimiter, I usually
use '|'.

Option 2
Use the CSV format. By default the delimiter character is a comma and
the quote character is ". So

copy STOPS_LondonBuses from
'/home/andyt/projects/django-stringer/txc/OId_HY/GTFS/stops.txt' WITH
FORMAT csv;


--
Adrian Klaver
adrian.klaver@gmail.com