How to load ASCI files to PostgreSQL automatically in a interval each day?

Hi,

I want to load a specific file  each day on a table on PostgreSQL, the file name is different each day like I get around 5 files each day the file name would be like i.e

filename20111220122612.ASCI
filename20111220123013.ASCI
filename20111220124015.ASCI

it's like NAME YEAR MONTH DAY HOURS MINUTE SECOND.ASCI

I am looking to a solution where this can be done automatically and every time it load the files it moves it to another location or dump the file name in a table so I know which files are loaded and which are not.

regards

LVL 1
hi4pplAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

earth man2Commented:
use at command to execute script like

cd c:\import_directory_path
for /f %%a in (’dir /b filename*.ASCI) do (
   psql -c "COPY INTO tablename from %%a" yourdatabase_name
   mv %%a c:\holding_directory_path
)

what operating system are you using ?
0
hi4pplAuthor Commented:
Hi,

thanks for the answer, my option is open but I am mostly want to go to to linux not windows and probably using red hat or oralce linux enterprise ... the script you put up their is for windows I guess not shell?
0
earth man2Commented:
the Linux script is similar

#!/bin/bash
cd /yourpath2thedata/csvimport
for f in *.csv
do
  echo "Processing File - $f"
  psql -c "copy into table_x from '$f'" your_dbname
  mv $f ../archive
done
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hi4pplAuthor Commented:
Hi

thanks for the replay I will try this and replay back if I it does what I need but also I will be creating one more table on the DB where it insert the file names of the loaded data is that possible?

regards
0
earth man2Commented:
create table table_y (  filename text, load_tstamp timestamp );

#!/bin/bash
cd /yourpath2thedata/csvimport
for f in *.csv
do
  echo "Processing File - $f"
  psql -c "copy into table_x from '$f'" your_dbname
  psql -c "insert into table_y values ( '$f', now() )" your_dbname
  mv $f ../archive
done
0
hi4pplAuthor Commented:
Hi thanks

I try this one but I get following problem,

- I have installed PG 9.1 and when I try to connect from terminal it says command is not found that is why I have to manual export the PG path which I did the following command
(export PATH=/opt/PostgreSQL/9.1/bin/:$PATH)
but this will only remain for the current session of the path if I close the terminal it will vanish and I have to export it again...

- second thing is that the db that I have is also require credential and username for the database when I supplied it like bellow :

 psql -U username -W password -c "copy into test from '$f'" dbname

thanks

regards



0
hi4pplAuthor Commented:
and also it gives syntax error near copy into
0
earth man2Commented:
http://www.postgresql.org/docs/9.1/static/sql-copy.html

psql -U username -W password -c "copy table_x from '$f'" your_dbname
0
hi4pplAuthor Commented:
hi

thanks for the replay when I put the password in the file I get the following error:


[root@localhost Desktop]# ./dataloader.sh
Processing File - james.csv
psql.bin: warning: extra command-line argument "copy  test from 'james.csv'" ignored
psql.bin: warning: extra command-line argument "JAMES" ignored
Password for user postgres:

but when I remove the password from the file and leave only the username section I get the following error


Processing File - james5.csv
Password for user postgres:
ERROR:  could not open file "james5.csv" for reading: No such file or directory
Password for user postgres:
INSERT 0 1
Processing File - james6.csv
Password for user postgres:
ERROR:  could not open file "james.csv" for reading: No such file or directory
Password for user postgres:
INSERT 0 1

thanks for the help,
regards
0
earth man2Commented:
fully qualify the psql executable

#!/bin/bash
PGBINDIR=/opt/PostgreSQL/9.1/bin
cd /yourpath2thedata/csvimport
if [ ! -f ../archive ]
  mkdir ../archive
fi
for f in *.csv
do
  echo "Processing File - $f"
  $PGBINDIR/psql -U username -W password -c "copy table_x from '$f'" your_dbname
  $PGBINDIR/psql -c "insert into table_y values ( '$f', now() )" your_dbname
  mv $f ../archive
done

0
hi4pplAuthor Commented:
Hi

thanks

but I still get the following error
./script.sh: line 6: syntax error near unexpected token `fi'
./script.sh: line 6: `fi'

and also do I have to have csvimport ? as my data is  in /data
and FYI I am using redhat if this script works for that I am not sure just asking.
regards
0
earth man2Commented:
sorry I've had to reboot into linux and debug that script -- missing semi colon

$ cat yyy.sh

#!/bin/bash
PGBINDIR=/usr/bin
cd .
if [ ! -d ../archive ]; then
  mkdir ../archive
fi
for f in *.csv
do
  echo "Processing File - $f"
  $PGBINDIR/psql -c "\copy table_x from '$f' with csv" test1
  $PGBINDIR/psql -c "insert into table_y values ( '$f', now() )" test1
  mv $f ../archive
done

$ cat yyy.csv

1,'Hello World!','22-May-1920'
2,'My Way','24-May-1921'

$ psql test1

psql (8.4.5)

test1=> \d table_x
      Table "public.table_x"
   Column   |  Type   | Modifiers
------------+---------+-----------
 id         | integer |
 col1       | text    |
 date_stamp | date    |

test1=> \d table_y
               Table "public.table_y"
  Column   |            Type             | Modifiers
-----------+-----------------------------+-----------
 file_name | text                        |
 loaded    | timestamp without time zone |

test1=> \q
[cchapman@shed x]$ ./yyy.sh
Processing File - yyy.csv
INSERT 0 1
[cchapman@shed x]$ psql test1
psql (8.4.5)
Type "help" for help.

test1=> select * from table_y;
 file_name |           loaded          
-----------+----------------------------
 yyy.csv   | 2012-01-12 21:48:11.150297
(1 row)

test1=> select * from table_x;
 id |      col1      | date_stamp
----+----------------+------------
  1 | 'Hello World!' | 1920-05-22
  2 | 'My Way'       | 1921-05-24
(2 rows)

test1=> \q

$ ls ../archive
yyy.csv
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hi4pplAuthor Commented:
Hi

thanks for quick replay but I still get following error:

ERROR:  could not open file "archive.csv" for reading: No such file or directory

it insert the data loaded but it cannot open CSV file:

>ls -ltr
-rwxrwxr-x 1 root root 63 Jan 10 21:11 archive.csv

and I run the script from root user I even chmod 775 and chown it root but still it does not work.

regards
0
hi4pplAuthor Commented:
Hi,

thanks it's fixed... one thing more if the files have header how do I exclude those headers?

i.e.

firs name| last name| school
james| bond| CA school
zack|daniel|LA school

etc

and also if I append only first name and last name how can I do that?

thanks
0
earth man2Commented:
try

$PGBINDIR/psql -c "\copy table_x from '$f' with csv, header TRUE, delimiter as '|'" yourdb
0
hi4pplAuthor Commented:
Hi,

I get syntax error near csv,

plus when I add password -W password it gives error like

psql.bin: warning: extra command-line argument "table name"  ignored

regards
0
earth man2Commented:
 $PGBINDIR/psql -c "\copy table_z from '$f' with delimiter as '|' csv header" yourdbname
0
hi4pplAuthor Commented:
Hi,

thanks for replay, but there is problem here... the file format like bellow

1-first issue

first name|last name|class
Zack|jackson|third
james|lee|first class

and plus did you find any soulition for the password to include it in file becasue when I include the password with -W I get following error

2-second issuse
psql.bin: warning: extra command-line argument "table name"  ignored

regards

0
earth man2Commented:
You'll need to give the exact syntax else it's impossible to work out what's going wrong.

I've run this script in Postgresql 8.4 OK.  Do you have punctuation characters in the password that is interfering with the statement parser ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.