I have one csv file (pipe separated).I want to remove very first line of that CSV and at the same time,I have to move same file with same filename into another folder. After that,I have to insert that CSV into SQL table.I know,I have to use "Bulk Insert" for this.But,Main difficulty I am facing,to move that file with same filename(as I mentioned above).
After that,this process will continue on daily basis.So that,I've to take care that,File which I will gonna be insert into table,is must be fresh(latest file I've to pick up).So, can anyone help me out in these 2 scenarios?
Technology : I am using as SQL and xp_cmdshell.
Exec sp_configure 'xp_cmdshell',1
reconfigure
exec xp_cmdshell 'robocopy *.txt C:\Users\ngavhane\Desktop\abc C:\Users\ngavhane\Desktop\pqr /XF *att*.txt'
exec xp_cmdshell 'copy C:\Users\ngavhane\Desktop\abc C:\Users\ngavhane\Desktop\ProcessedData'
exec xp_cmdshell 'del C:\Users\ngavhane\Desktop\abc\*.txt'
exec xp_cmdshell 'more +1 C:\Users\ngavhane\Desktop\pqr\*.txt>C:\Users\ngavhane\Desktop\To_be_processed\*.txt'
bulk insert test_21012019
from 'C:\Users\ngavhane\Desktop\To_be_processed\'
with
(
fieldterminator='|',
rowterminator='\n'
);
Print 'File data has been inserted into table' ```
