[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Renaming a txt file with Julian Date

Posted on 2012-08-15
16
Medium Priority
?
1,002 Views
Last Modified: 2012-09-04
I have a txt file that is constantly being produced via an "export to txt" command using sqlcmd from the command prompt. This txt file is generated everyday based on a SQL script to retrieve records from our Microsoft SQL Server 2008 database. We need to upload each txt file to an FTP site with a specific naming convention. I want to know how to change the name of the txt file to THAT specific naming convention automatically via one method everyday.

The convention is as follows:

CCCCC{2012}{Julian Date}01

where the CCCCC represents a static value (which is my company's name)

the 2012 is the current year...which also could be static ... until the year changes of course

the Julian date (three digit number)

and the 01 is also a static constant

Example:

APPLE+2012+JULIANDATE+01. The + just means its all concatenated together.

The txt file is exported with some arbitrary value lets say : 123456.txt  but we want itt ---> APPLE201236501.txt. for example.

If our batch is running on a job everyday to produce this txt file... how could we set up another automation to rename it once it's created to that naming convention?
0
Comment
Question by:SQLNoviceSIK
  • 7
  • 5
  • 4
16 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38298696
Here's code you can use to convert the current date to Julian date.

    Dim NormalDate As Date      'The serial date.
    Dim DateYear As String      'The year of the serial date.
    Dim JulianDay As String
    Dim JulianDate As String    'The converted Julian date value

    NormalDate = DateValue(Now())

    'Assign DateYear the year number
    DateYear = Format(NormalDate, "yy")

    'Find the day number for NormalDate
    JulianDay = Format(Str(NormalDate - DateValue("1/1/" & _
    Str(DateYear)) + 1), "000")

    'Combine the year and day to get the value for JulianDate.
    JulianDate = DateYear & JulianDay

    'Display the new date in the Julian date format.
    MsgBox "The equivalent Julian date is " & JulianDate

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38306400
Did my code help you?
0
 

Author Comment

by:SQLNoviceSIK
ID: 38307167
I haven't tried it yet but I was looking for more than just the Julian date as it was only a part of the naming convention I need as an automated method for renaming my text files.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 59

Expert Comment

by:Bill Prew
ID: 38308540
I think the poster may be using the term Julian date wrong, which is why I didn't respond yet.  Based on the question description I think they are looking for the day of the current year, not the julian date number.  So I think they want 1 to 366.

If that is the case, then this routine can be used in a simple BAT script to rename the file as needed.

http://www.dostips.com/DtCodeCmdLib.php#Function.dayOfYear

~bp
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38308569
Using visual basic the day of the year is returned by

DatePart("y", Now())
0
 

Author Comment

by:SQLNoviceSIK
ID: 38312351
Yes billprew it IS the dayofyear not Julian Date my mistake. Thank you for that tip but how would you rename a file based on that date?...in addition to the year and some static values?

blahblah2012-365-blahblah.txt?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38312366
In VBA

MyFileName = "blahblah2012-" & DatePart("y", Now()) & "-blahblah.txt"
0
 

Author Comment

by:SQLNoviceSIK
ID: 38337818
Does "MyFileName" refer to the path of the file? How and where do i input this code so that it knows which file.txt to change the name of to that name?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38337930
Create a macro while you do the 'export to txt' process. The macro will probably look something like this.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Martin Liss\My Documents\Book1.txt", FileFormat:= _
        xlTextMSDOS, CreateBackup:=False
End Sub

Open in new window



When you create it you can call it anything you like, and also assign a shortcut letter to it so that you easily run it when you want to create the txt file. And you would change the "C:\Documents and Settings\Martin Liss\My Documents\Book1.txt"so that it looks something like this
"C:\Documents and Settings\Martin Liss\" & "blahblah2012-" & DatePart("y", Now()) & "-blahblah..txt"
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 1980 total points
ID: 38352806
Here's an example of how to do it in a BAT file.  Right now it assumes the input file is always the same name, but easy to change if that's not the case.

@echo off

REM Define fixed parts of old and new file names
set File=123456
set Ext=txt
set New1=CCCCC
set New2=01

REM Get the year and day into jDate variable
call :GetDate jDate

REM Rename file to desired format
ren "%File%.%Ext%" "%New1%%jDate%%New2%.%Ext%"

REM Exit script
exit /b

:GetDate Return -- returns the day of the year, i.e. 1 for 1/1/2008, 266 for 12/31/2008
::              -- Return  [out] - variable name to store resulting day of the year
  setlocal
  set "DateStr=%date%"
  for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('"echo.|date"') do (
    for /f "tokens=1-3 delims=/.- " %%A in ("%DateStr:* =%") do (
      set %%a=%%A&set %%b=%%B&set %%c=%%C))
  set /a "yy=10000%yy% %%10000,mm=100%mm% %% 100,dd=100%dd% %% 100"
  set /a JD=dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4
  set /a "yy=10000%yy% %%10000,mm=1,dd=1"
  set /a JD-=-1+dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4
  set JD=00%JD%
  endlocal & set %~1=%yy%%JD:~-3%
  exit /b

Open in new window

~bp
0
 

Author Closing Comment

by:SQLNoviceSIK
ID: 38355837
Thank you man! This is exactly what i needed!

You are the man!
0
 

Author Comment

by:SQLNoviceSIK
ID: 38355950
One last thing... can i change this script to give me the Julian date minus a certain number of days?

For example.. if i want the day of year minus 10 days...I put a -10 value after the :

set /a JD=dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4
  set /a "yy=10000%yy% %%10000,mm=1,dd=1"

This works.... However... when I changed the date to January 1st on my computer to check and see if it would give me the 2011 day of year... it didn't rename the file properly...

Thanks for all your  help so far!
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 38356317
Give this a go, I added another parameter to the first function, which is the offset in days from the current date to generate the file stamp for.  Can be negative or positive, or omitted (for no offset).

And a big shout out to Peter Hartmann, founder of DosTips.com, a great site for some nifty DOS techniques and functions!

@echo off
setlocal

REM Define fixed parts of old and new file names
set File=123456
set Ext=txt
set New1=CCCCC
set New2=01

REM Get the year and day into jDate variable
call :GetDate jDate -10

REM Rename file to desired format
ECHO ren "%File%.%Ext%" "%New1%%jDate%%New2%.%Ext%"

REM Exit script
endlocal
exit /b

:GetDate Return -- returns the day of the year, i.e. 1 for 1/1/2008, 266 for 12/31/2008
::              -- Return  [out] - variable name to store resulting day of the year
::              -- Offset  [in]  - number of days to offset from today (+/-) before returning the day number
  setlocal
  set "DateStr=%date%"
  if "%~2" NEQ "" (set Offset=%~2) else (set Offset=0)
  echo [%Offset%]
  for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('"echo.|date"') do (
    for /f "tokens=1-3 delims=/.- " %%A in ("%DateStr:* =%") do (
      set %%a=%%A&set %%b=%%B&set %%c=%%C))
  set /a "yy=10000%yy% %%10000,mm=100%mm% %% 100,dd=100%dd% %% 100"
  set /a JD=(dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4)+%Offset%
  call :jdate2date %JD% yy my dd
  set /a "yy=10000%yy% %%10000,mm=1,dd=1"
  set /a JD-=-1+dd-32075+1461*(yy+4800+(mm-14)/12)/4+367*(mm-2-(mm-14)/12*12)/12-3*((yy+4900+(mm-14)/12)/100)/4
  set JD=00%JD%
  endlocal & set %~1=%yy%%JD:~-3%
  exit /b

:jdate2date JD YYYY MM DD -- converts julian days to gregorian date format
::                     -- JD   [in]  - julian days
::                     -- YYYY [out] - gregorian year, i.e. 2006
::                     -- MM   [out] - gregorian month, i.e. 12 for december
::                     -- DD   [out] - gregorian day, i.e. 31
SETLOCAL ENABLEDELAYEDEXPANSION
set /a L= %~1+68569,     N= 4*L/146097, L= L-(146097*N+3)/4, I= 4000*(L+1)/1461001
set /a L= L-1461*I/4+31, J= 80*L/2447,  K= L-2447*J/80,      L= J/11
set /a J= J+2-12*L,      I= 100*(N-49)+I+L
set /a YYYY= I,  MM=100+J,  DD=100+K
set MM=%MM:~-2%
set DD=%DD:~-2%
( ENDLOCAL & REM RETURN VALUES
    IF "%~2" NEQ "" (SET %~2=%YYYY%) ELSE echo.%YYYY%
    IF "%~3" NEQ "" (SET %~3=%MM%) ELSE echo.%MM%
    IF "%~4" NEQ "" (SET %~4=%DD%) ELSE echo.%DD%
)
EXIT /b

Open in new window

~bp
0
 

Author Comment

by:SQLNoviceSIK
ID: 38364592
It doesn't seem to work. If i just put this in a bat file unchanged and run it... my 123456.txt file will not change to anything...

i see where there is a -10 in the script... is that the part of the script where i put my delay... or do i have to change anything else in it?
0
 

Author Comment

by:SQLNoviceSIK
ID: 38364705
okay... nvm i got it to work... i deleted one of the echos from the beginning! thanks!
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 38365647
Welcome.

~bp
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

868 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