Solved

Batch files - naming output file extensions

Posted on 2014-03-10
11
454 Views
Last Modified: 2014-03-12
The following is the batch file I am using to create a "CSV" output from two SQL tables:

bcp "select * from livedb.dbo.tbl_RCA_DAILY_ATB_HEADERS" queryout "P:\ACCOUNTING\RCA_DAILY_HEADERS.csv" -T -SPTM-DR01 -w -t^|


bcp "select * from livedb.dbo.tbl_RCA_ATB_DAILY" queryout "P:\ACCOUNTING\RCA_ATB_DAILY_Temp.csv" -T -SPTM-DR01 -w -t^|

copy P:\ACCOUNTING\RCA_DAILY_HEADERS.csv + P:\ACCOUNTING\RCA_ATB_DAILY_Temp.csv  P:\ACCOUNTING\CHI\DailyATB\rca_p1_mt_daily_accts_"%date:~10,4%%date:~4,2%%date:~7,2%.csv

The code works well, except that the name of the output file results in a "txt" extension.  My client require a "pipe" delimited "csv" file.

How can I change the above code to make sure the output works like I need it to?

Thanks

glen
0
Comment
Question by:GPSPOW
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39918352
#1, rename the .txt to get rid of it with a
rename [filename.txt] to [filename.csv] if needed.

Try specifying the delimiter as such:

-t^|

| is a command shell item..it gets interpreted by the shell. ^| may escape it.
0
 

Author Comment

by:GPSPOW
ID: 39918362
How do I write it since the date is a variable?

Thanks

Glen
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39918662
CSV, pipe delimited, text file are all just text files.  All depends soley on the output of your bcp command.  Above should give us a CSV file.

Can you give us an example output file from your bcp line?
0
 
LVL 25

Expert Comment

by:SStory
ID: 39919233
Don't you need a -o before the output file name? Also it should already be a CSV file before you copy it.  I agree your current code should work.  Can you show us the output...a few lines from each bop and the final version? Change the data as needed to protect info before posting.  Are you saying the -t^| doesn't work?
0
 
LVL 38

Accepted Solution

by:
BillDL earned 500 total points
ID: 39919836
Was the double quote intentional or accidental in this portion of your COPY command line?:

rca_p1_mt_daily_accts_"%date:~10,4%
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:GPSPOW
ID: 39921030
I placed double quotes in the title of the new file:

copy P:\ACCOUNTING\RCA_DAILY_HEADERS.csv + P:\ACCOUNTING\RCA_ATB_DAILY_Temp.csv  "P:\ACCOUNTING\CHI\DailyATB\rca_p1_mt_daily_accts_"%date:~10,4%%date:~4,2%%date:~7,2%".csv"

The batch file works when I run it manually and I get afile with a "csv" extension.  However, when the batch file runs from the scheduled task the file has a "txt" extension.

I like the solution of renaming the file.  Since the date is variable, how do I write this?

Thanks

Glen
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 39921078
Is this scheduled, or run as a different user in any way btw?  the %date% bits could easily break as %date% could return different formatting.  if so consider using this way to get your date:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_1153-Using-dates-in-batch-files-scripts.html

You can always do rename *.txt *.csv

Steve
0
 
LVL 38

Expert Comment

by:BillDL
ID: 39921452
Thanks for clarifying about the double quote.  I had wondered if it had any bearing on the issue, because you forgot to add another at the end of the new file name and extension, and I see that while you were explaining this you placed an extra one in front of the .csv extension.  Obviously just typos here rather than in the actual batch file.
0
 
LVL 25

Expert Comment

by:SStory
ID: 39921839
>The batch file works when I run it manually and I get afile with a "csv" extension.  >However, when the batch file runs from the scheduled task the file has a "txt" extension.

That is beyond bizarre.  Is it for sure calling the same batch file from the scheduled task?

Rename P:\ACCOUNTING\CHI\DailyATB\rca_p1_mt_daily_accts_"%date:~10,4%%date:~4,2%%date:~7,2%.txt P:\ACCOUNTING\CHI\DailyATB\rca_p1_mt_daily_accts_"%date:~10,4%%date:~4,2%%date:~7,2%.csv

There is a space between two file paths.
I do not know why the batch would work differently though.  Double check the scheduled task to be sure you are calling the same script.
0
 

Author Closing Comment

by:GPSPOW
ID: 39921855
BillDL alerted me to m y syntax (grammar) error.  Once I added the double quotes around the path name\file name segment and the extension segment, I got the desired output.

As for the perplexing situation where the batch file did not run via the scheduler but when I did manually...the network administrator copied the batch file into a alternate server folder to allow the scheduler to run properly.  I was unaware that I was not editing the correct "bat" file.  Once I figured this out, the scheduler works as it should.

Again thanks to  all who gave me a possible solution.

Glen
0
 
LVL 38

Expert Comment

by:BillDL
ID: 39922938
Thank you Glen.  It's sometimes the simplest things that we overlook and scratch our heads over for ages.  We've all done it from time to time.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Run MS Access Module from Macro / Bat File 9 44
Calculating Business Hours 19 62
Copy Database Wizard Error 3 20
Slow SQL query 12 17
VALIDATING DATES One method of validating dates is to jam the date into the DATE command and see if it accepts it by examining the system's errorlevel value. A non-zero result indicates failure. A typical example might look something like the fol…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now