Copy command using filter

saurav_1

Hi,

 

I have a requirement where i need to restrict the data in copy command. suppose i hve 1000 records in my csv file that i will be loadinh using copy command, so i want that with this copy command i can load the data like top 500 from bottom.

 

That is from 500 to 1000.

 

Now if my record got increased by 50 that is total 1050 record the 550 record to be populated.

 

Is their any solution for this????

abhi_dwh

If requirement is only to skip top 500 records everytime , You can use SKIP with copy command , it will  skip top 500 records always means 1 -500 .

 

 

copy abc from /temp/abc.txt delimiter ';' SKIP 500  ;

--> above command will skip top 500 records .

 

 

copy abc from /temp/abc.txt delimiter ';' SKIP bytes 500  ;

--> This command will skip 500 bytes from starting of file.

 

Thanks :

Abhi

 

Adrian_Oprea_1

 You can do this using pipes in linux and the stdin copy option.

 

- just pipe the output of the tail or any linux command that can limit the numbers of rows, form head or tail to your copy command.

 - you can put more logic in linux scripting then in the copy command.

 

 See example:

[dbadmin@~]$ cat /tmp/file.csv
1,1,2
2,1,2
3,1,2
4,1,2
[dbadmin@~]$ tail -2 /tmp/file.csv
3,1,2
4,1,2
[dbadmin@~]$ tail -2 /tmp/file.csv |vsql -d db -U dbadmin -c "COPY test FROM LOCAL STDIN DELIMITER ',' ;"
Password:
Rows Loaded
-------------
2
(1 row)

[dbadmin@~]$ vsql -d db -U dbadmin -c "select * from test;"
Password:
 col1 | col2 | col3
------+------+------
 3 | 1 | 2
 4 | 1 | 2
(2 rows)

 

saurav_1

Hi, My requirement is i have to keep last 500 record from current date. So, every time this record will change.I have to keep only top 600 record for each day.So using skip will skip top 600 record itself but i need that only top 600 record to keep using copy command.

saurav_1

Hi, Thanks for the help, this looks like now i can solve my problem. But if we use this command the every time the result of copy command is getting stored in some cache or some other place and when next time i am running the same copy command it is taking the earlier data also. Like first time when i am running the command and is populating 10 records then i deleted these records and re ran the copy cammand and this time it is populating 20 records, 10 from current record and 10 from last record. Can u help me over this as i am new to linux

Adrian_Oprea_1

 not sure i understand your requirment !