Script - Convert TXT File to CSV but format third column as date and remove specific character?

Question

I created a script that converts a txt file into a csv and places commas in between the columns, but now I am trying to have place forward slashes in the third column between the number strings of this column. This column should be formatted as a date. My other issue is in this same column, sometimes it contains strings of dashes than I want removed. I cannot figure out how to do this, I have written the script as how I think it should work.

@ECHO off
SETLOCAL EnableDelayedExpansion

for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
     set dow=%%i
     set month=%%j
     set day=%%k
     set year2=%DATE:~-2%
     set yearf=%%l

if "%%j"=="01" set mname=January
if "%%j"=="02" set mname=February
if "%%j"=="03" set mname=March
if "%%j"=="04" set mname=April
if "%%j"=="05" set mname=May
if "%%j"=="06" set mname=June
if "%%j"=="07" set mname=July
if "%%j"=="08" set mname=August
if "%%j"=="09" set mname=September
if "%%j"=="10" set mname=October
if "%%j"=="11" set mname=November
if "%%j"=="12" set mname=December
)
::Creates the folder structure based of the system date.
SET datestr=%month%%day%%year2%
SET "topdir=\\10.1.3.3\Information\Reports\Reports %yearf%\%month% %mname%\%day%"

::Outputs full File, this is the file that is used internally
SET "sourcedir=%topdir%"
SET "filename1=%sourcedir%\file%datestr%.txt"

FOR /f "usebackq tokens=1-3*" %%a IN ("%filename1%") DO (

 SET bdd=!%%c:~2!
 SET bmm=!%%c:~2,2!
 SET byy=!%%c:~-4!
 type %%c | findstr /v -

 ECHO(%%a,%%b,%bdd%/%bmm%/%byy% >> "%topdir%\file%datestr%.csv"
)

PAUSE

This is the part I addedd,

     SET bdd=!%%c:~2!
     SET bmm=!%%c:~2,2!
     SET byy=!%%c:~-4!
     type %%c | findstr /v -  ::trying to remove dashes from third column %%c

ECHO(%%a,%%b,%bdd%/%bmm%/%byy%

ECHO(%%a,%%b,%%c   ::this is what works

Example txt file

245454  5454564 01032016
216545  5454543  2042016
211145  8878787 --------

How I want it formatted

245454,5454564,01/03/2016
216545,5454543,2/04/2016
211145,8878787,

Without this code, the script works just fine, but I'm not even close to knowing how this should be done. Thank you.

Stephan · Accepted Answer · 2016-05-12T14:23:02.123

you can't do string manipulation with for variables. You have to use a temorary "normal" variable:

(FOR /f "usebackq tokens=1-3*" %%a IN ("%filename1%") DO (
  SET "d= %%c"
  set dat=!d:~-8,2!/!d:~-6,2!/!d:~-4!
  echo %%c|find "-" >nul && set "dat="
  ECHO(%%a,%%b,!dat!
)) > "%topdir%\file%datestr%.csv"

Output with your example text file:

245454,5454564,01/03/2016
216545,5454543, 2/04/2016
211145,8878787,

Some of the used tricks:
adding a leading space in SET "d= %%c" (or replace it with a 0 if you prefer 02\04\2016 instead of 2\04\2016
get the substrings relative to the end instead of the start of the string to take account to an eventually too long string (added space to a ddmmyyyy string)
echo %%c|find "-" >nul && set "dat=" : if there is a dash in %%c then set the resulting string to empty (or "----------" or to "no date" or whatever)
Use one redirection for the whole loop instead of every single line for performance.
finally, use delayed expansion with your output (!dat! instead of %dat%)

Excellent, works just as I needed. Thank you! Just a note, not sure if this is part of converting the file to csv, but it looks like it automaticlly leads with zero on a single digit month regardless if I insert it in "d= %%c". — Mark Pineda, May 11 '16 at 19:44
I appended my output to the answer. There is no "converting". A csv file is just a text file with another extension. If I leave out the space, ` 2/04/2016` is converted to `20/04/2016` - which clearly is wrong. — Stephan, May 12 '16 at 14:27

cyberponk · Answer 2 · 2016-05-12T21:25:41.727

Fixed code with comments:

@echo off
SETLOCAL ENABLEEXTENSIONS

:: Here I recommend using the GetDateIntl function that works with international
:: date settings also (check end of file). Your method is OK but won´t work
:: in all situations. You can use other ethods as well, like wmic.
call :GetDateIntl yearf month day 
echo/Using GetDateIntl you get: %yearf%-%month%-%day%
set "year2=%yearf:~-2%"

if "%month%"=="01" set mname=January
if "%month%"=="02" set mname=February
if "%month%"=="03" set mname=March
if "%month%"=="04" set mname=April
if "%month%"=="05" set mname=May
if "%month%"=="06" set mname=June
if "%month%"=="07" set mname=July
if "%month%"=="08" set mname=August
if "%month%"=="09" set mname=September
if "%month%"=="10" set mname=October
if "%month%"=="11" set mname=November
if "%month%"=="12" set mname=December

::Creates the folder structure based of the system date.
SET "datestr=%month%%day%%year2%"
SET "topdir=\\10.1.3.3\Information\Reports\Reports %yearf%\%month% %mname%\%day%"

::Outputs full File, this is the file that is used internally
SET "sourcedir=%topdir%"
SET "filename1=%sourcedir%\file%datestr%.txt"

:: Empty target file
echo/>"%topdir%\file%datestr%.csv"

:: You cannot use variables inside a FOR because each line is processed with the same
:: initial variable table, and changes only are applied after the FOR ends.
:: So if you create a variable, it will only be visible outside the FOR.
:: To fix this you have to use a function call, that runs every line with an 
:: updated variable table
FOR /f "usebackq tokens=1-3*" %%a IN ("%filename1%") DO call:PrintLine %%a %%b %%c

endlocal
goto:eof

:: This function is called for every line in the .txt file with the 3 columns 
:: as arguments
:PrintLine
  SET "_tmpdate=%3"

  :: CAREFUL: in your example, the date of the second line is missing the leading 0.
  :: If this really happenes in you .txt file then you need to check and correct this
  :: in your code. If all dates are ddmmyyyy format, this code is OK.
  SET "_tmpdate=%_tmpdate:~0,2%/%_tmpdate:~2,2%/%_tmpdate:~-4%" 
  :: Check if _tmpdate´s first char is "-". If it is, empty _tmpdate.
  IF "%_tmpdate:~0,1%"=="-" SET "_tmpdate="

  ECHO/%1,%2,%_tmpdate% >> "%topdir%\file%datestr%.csv"
goto:eof

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:GetDateIntl yy mm dd [/A]
:: Returns the current date on any machine with regional-independent settings
:: Arguments:
::   yy = variable name for the year output
::   mm = variable name for the month output
::   dd = variable name for the day output
::   /A = OPTIONAL, removes leading 0 on days/months smaller than 10 (example: 01 becomes 1)
:: Remarks:
::  Will return month in text format in regions with MMM month
::
SETLOCAL ENABLEEXTENSIONS
if "%date%A" LSS "A" (set toks=1-3) else (set toks=2-4)
for /f "tokens=2-4 delims=(-)" %%a in ('echo:^|date') do (
  for /f "tokens=%toks% delims=.-/ " %%i in ('date/t') do (
    set '%%a'=%%i
    set '%%b'=%%j
    set '%%c'=%%k
  )
)
if /I "%'yy'%"=="" set "'yy'=%'aa'%"
if /I "%'yy'%"=="" ( set "'yy'=%'jj'%" & set "'dd'=%'tt'%" )
if %'yy'% LSS 100 set 'yy'=20%'yy'%
endlocal&set %1=%'yy'%&set %4 %2=%'mm'%&set %4 %3=%'dd'%&goto :EOF
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Not familiar with GetDateIntl. Why would I use that over just "DATE"? Googled it a little bit, says GetDate is for NT4/2k/XP, this function would not work on windows 7? — arealhobo, May 12 '16 at 02:03
%Date% is region specific. This means if you use this script in a computer with date format different then yours, everything gets messed up. GetDateIntl works for systems with dates in any format. For example: If I run your original code in my PC it fails because my date format is DD/MM/YYYY. / GetDateIntl (my code) is not based on GetDate from google and also runs on win vista, 7, 8, 10, etc... If you are only going to run this on your PC, then you can use %DATE% without any problem. — cyberponk, May 12 '16 at 02:08
Sorry to disappoint you: on my german windows, `date` gives me `Geben Sie das neue Datum ein: (TT-MM-JJ)`, so your code fails because `%'yy'%` and `%'dd'%` are not defined. The longest list of methods to get a datestring independend of locale settings I found yet is [npocmaka](http://stackoverflow.com/a/20387874/2152082)'s answer to another question. — Stephan, May 12 '16 at 14:51
Good to know. Could you type echo %date% on a command prompt and tell me the result? — cyberponk, May 12 '16 at 18:53
Did you run the whole code or only the GetDateIntl function? I just tested it here and it works on all regions exept the ones with MMM (text) months — cyberponk, May 12 '16 at 19:11
I tried both and get `"100" kann syntaktisch an dieser Stelle nicht verarbeitet werden.` (in English it would be something like "100 was unexpected at this time") with `if %'yy'% LSS 100 `, because %'yy'% is not defined (it's `%'JJ'%` on my computer). — Stephan, May 12 '16 at 20:08
by the way - OP doesn't need any date processing, as he is just processing a textfile. That said, please see my link to [npocmaka's list of reliable methods](http://stackoverflow.com/a/20387874/2152082) to get a date string or [my personal favourite](http://stackoverflow.com/a/18024049/2152082). — Stephan, May 12 '16 at 20:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/111806/discussion-between-cyberponk-and-stephan). — cyberponk, May 12 '16 at 21:26

Script - Convert TXT File to CSV but format third column as date and remove specific character?

2 Answers2