We help IT Professionals succeed at work.

Renaming a txt file with Julian Date

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?
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did my code help you?

Author

Commented:
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.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Using visual basic the day of the year is returned by

DatePart("y", Now())

Author

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In VBA

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

Author

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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"
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
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

Author

Commented:
Thank you man! This is exactly what i needed!

You are the man!

Author

Commented:
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!
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Author

Commented:
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?

Author

Commented:
okay... nvm i got it to work... i deleted one of the echos from the beginning! thanks!
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome.

~bp

Explore More ContentExplore courses, solutions, and other research materials related to this topic.