Skip to content

Writing a WINSCP script to automate a generated csv file using sqlcmd

January 15, 2013

Ok, I have been meaning to write a blog on this, as I have a little time now to do it, I have over the last day or so generated a script with a few obstacles along thew way which is a WinSCP script for uploading a generated csv file onto SFTP. The great feature is that you can simply use sqlcmd to generate the csv file inside a .bat file.

I will explain the first part which is used to generate the .csv file

Inside a .bat file, you can write the following:-

for /f “tokens=1-4 delims=/-/ %%a in (‘date /t’) do set XDate=%%a%%b%%c

SQLCMD -S SERVERNAME -E -Q “set nocount on; set ansi_warnings off; select * from [database].[dbo].[session] where StartTime >= DateADD(Day, 0, DATEDIFF(Day, 0, GetDate() – 1)) AND StartTime <= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))” /o %Xdate%.tmp /s “,” -w -w 800
type %Xdate%.tmp | findstr /V \-\,\- > %Xdate%.csv
del %Xdate%.tmp
winscp.exe /script=c:\script.txt
exit

The first line sets todays date which can be notated inside the batch file

The second line uses an inbuilt MS SQL 2008 command function called SQLCMD which runs queries on the command line level, here it runs a select query on the table session, and gets all records from the day before yesterday midnight to yesterday midnight (all yesterday’s records) with column width 800pts in each column, and places it inside a .tmp file.

The third line finds any column header notations – which SQLMD generates,  and clears it out or filters it and saves it out as a .csv file. (Comma delimited with , )

The fourth line deletes the .tmp file

And the fifth line exits

This script helps on automating the csv generation process without manually having to write the query and generate the .csv fie or using the wizards with package files (which I don’t like).

Hope that helps! More about this in a later post!

 

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: