Обсуждение: Conversion of columns during CSV Import

Hello,

is there any possibility to convert special columns during an CSV import
via COPY?
For example:

HELLO;WORLD;9999;011001

9999 should be converted to a character field
011001 to the date 10th January 2001

For the moment the only idea we have is, to import the CSV into a TEMP
table and
perform the conversion by a select from the temp to the target table.

Thanks for any idea.

Regards,

Patrick

On 29/06/2012 08:54, Patrick Schneider wrote:
> Hello,
>
> is there any possibility to convert special columns during an CSV import
> via COPY?
> For example:
>
> HELLO;WORLD;9999;011001
>
> 9999 should be converted to a character field
> 011001 to the date 10th January 2001
>
> For the moment the only idea we have is, to import the CSV into a TEMP
> table and
> perform the conversion by a select from the temp to the target table.

I don't think there is; AFAIK the only option is the one you've outlined.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 06/29/2012 12:54 AM, Patrick Schneider wrote:
> Hello,
>
> is there any possibility to convert special columns during an CSV
> import via COPY?
> For example:
>
> HELLO;WORLD;9999;011001
>
> 9999 should be converted to a character field
> 011001 to the date 10th January 2001
>
> For the moment the only idea we have is, to import the CSV into a TEMP
> table and
> perform the conversion by a select from the temp to the target table.
Technically everything coming in from the CSV is text and it will be
converted to the data-type in your PostgreSQL table during the import
unless such conversion is not possible in which case it will throw an
error. 9999 will import as text to a text/character field. You could
also import it to an int or numeric provided all data in that column
will convert to the specified type.

Similarly, "011001" will be interpreted per ISO 8601 as 10 January 2001.
But your sample data is ambiguous (month 01 and year 01). If the data
ordering is YMD you are fine. MDY will be problematic and may require a
preprocessing step either inside or outside PostgreSQL.

Given the above data:
create table foo (a text, b text, c text, d date);

An input table (with some data to show where the month and the year
really are:
HELLO;WORLD;9999;011001
Goodbye;World;0000;120629

Copy the data:
\copy foo from foo.txt csv delimiter ';'

Profit:select * from foo;
     a    |   b   |  c   |     d
---------+-------+------+------------
  HELLO   | WORLD | 9999 | 2001-10-01
  Goodbye | World | 0000 | 2012-06-29

Cheers,
Steve

On 29 Jun 2012, at 9:54, Patrick Schneider wrote:

> Hello,
>
> is there any possibility to convert special columns during an CSV import via COPY?
> For example:
>
> HELLO;WORLD;9999;011001
>
> 9999 should be converted to a character field
> 011001 to the date 10th January 2001
>
> For the moment the only idea we have is, to import the CSV into a TEMP table and
> perform the conversion by a select from the temp to the target table.
>
> Thanks for any idea.


I haven't worked with them yet, but as I understand it FDW's (foreign data wrappers) present data from foreign sources
(suchas a CSV file) as a table inside the database. Once you have that, it should be fairly easy to craft an INSERT ...
SELECTthat converts the data on the fly. 

Or use an ETL tool - Pentaho is popular.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Em 29/06/2012 14:10, Raymond O'Donnell escreveu:
> On 29/06/2012 08:54, Patrick Schneider wrote:
>> Hello,
>>
>> is there any possibility to convert special columns during an CSV import
>> via COPY?
>> For example:
>>
>> HELLO;WORLD;9999;011001
>>
>> 9999 should be converted to a character field
>> 011001 to the date 10th January 2001
>>
>> For the moment the only idea we have is, to import the CSV into a TEMP
>> table and
>> perform the conversion by a select from the temp to the target table.
> I don't think there is; AFAIK the only option is the one you've outlined.
>
> Ray.
>
>
On PostgreSQL 9.1, you can use File FDW to create a foreign table (that
is your CSV file).

Then you can use any kind of conversion allowed in the SELECT statement

There is an interesting article here:
http://www.postgresonline.com/journal/archives/250-File-FDW-Family-Part-1-file_fdw.html


Very easy to use.

Regards,


Edson

On 2012-06-29, Patrick Schneider <patrick.schneider@debeka.de> wrote:
> Hello,
>
> is there any possibility to convert special columns during an CSV import
> via COPY?
> For example:
>
> HELLO;WORLD;9999;011001
>
> 9999 should be converted to a character field

do you mean like '9999' (easy) or like '香' or like '✏' (harder)

> 011001 to the date 10th January 2001

I don't think there's any setting you can use to get postgres to
automatically translate 011001 to 10th January 2001 during a csv import

'20010110' would be doable

> For the moment the only idea we have is, to import the CSV into a TEMP
> table and
> perform the conversion by a select from the temp to the target table.

that's probably the best way.

--
⚂⚃ 100% natural