Re: [Fwd: [PORTS] M$ SQL server DTS package equivalent in Postgres]

2007/1/23, Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au>:
>
> G'day,
>
> Is there an equivalent in Postgres to the DTS Packages available in M$
> SQL server.
>
> I use these in SQL server to pre-load data from CSV files prior to
> enabling replication from my primary application. Any pointers on where
> best to go for this would be appreciated. I'm reading about something
> called EMS, is that the way to go?
>
> Sample of one of the files I use:
>
> DEALER_ID^DATE_CHANGED^TIME_CHANGED^BILLING_CODE_ID^DES^BILLING_CODE_TYPE^LABOUR_RATE^LABOUR_SALES_GROUP^CUSTOMER_NO^PARTS_SALES_GRO
> f UP^COMEBACK^WORKSHOP^FRANCHISE^LOCATION^DELETEFLAG
> F65^23-Jan-2007^10:13^AA^ADVERSITING ADMIN^I^45^40^2196^18^^0^BLANK^0^
> F65^23-Jan-2007^10:13^AN^ADV NEW^I^45^40^1636^18^^0^BLANK^0^
> F65^23-Jan-2007^10:13^AP^ADV PARTS^I^45^40^1919^18^^0^BLANK^0^
> F65^23-Jan-2007^10:13^AS^ADV SERV^I^45^40^2057^18^^0^BLANK^0^
> F65^23-Jan-2007^10:13^AU^ADV USED^I^45^40^1775^18^^0^BLANK^0^N
>
>

Paul,
what you're looking for exists in a number of variations.
You can use a good text editor with the postgres' COPY command for simple
bulk .csv loading, but in the long run, you might want to consider a
full-fledged ETL<http://en.wikipedia.org/wiki/Extract%252C_transform%252C_load>tool
like the Pentaho Data Integration suite. It allows you to use a wide
variety of data sources (anything with a jdbc driver, .xls, .csv, .xml for
starters), chain data transformations, store them and execute them on a
regular basis or on-demand from a java app etc. A number of such tools are
available, but I suggested Pentaho Data Integration as I have experience
with it and because it is under an open source licence. If anyone can
suggest a better tool for the job, please say so: I for one would like to
have my horizons broadened. :)

Cheers,
t.n.a.