Page 1 of 1
Adding value from the header of the file to the body
Posted: 21 Sep 2015 09:22
by shaswat
Hi Team,
I have a requirement where I need to get the value from the header of a CSV file and add that to the body of the file. The catch in this is that, the header value is a date value but I want the date format to be changed when it is copied in the body.
Input File:
Code: Select all
"Version 1.0"
"As Of Date: 13-Aug-2015"
"value1","value2","value3"
"value11","value12","value13"
"value21","value22","value23"
Desired Output File:
Code: Select all
"value1","value2","08/13/2015","value3"
"value11","value12","08/13/2015","value13"
"value21","value22","08/13/2015","value23"
I am able to get rid of the header files and generate a new file only with the values, but I'm unable to get the date into my values. Please help me in achieving the desired output.
Thanks,
Shaswat
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 09:41
by Squashman
Is the date always on the second line?
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 10:17
by Aacini
Try this:
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Create the array of month numbers
set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
set /A m+=1
set "month[%%a]=!m:~1!"
)
rem Get the date from second line
< input.txt ( set /P "line=" & set /P "line=" )
for /F "tokens=4-6 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"
rem Process the file
for /F "skip=3 tokens=1-3 delims=," %%a in (input.txt) do echo %%a,%%b,"%myDate%",%%c
Antonio
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 11:10
by shaswat
Squashman wrote:Is the date always on the second line?
No, it varies from file to file. Some files having few more data in the header will push the date to third or fourth row.
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 11:48
by shaswat
Aacini wrote:Try this:
Antonio
Thanks Antonio,
I tried this code and can see the changes in the command prompt window while I run the script, but when I want the changes to be copied in a file it is not happening. I am attaching the code below, please correct me where I'm missing. I want the output to come in my out.csv file. But still this code didn't work and in the command prompt window it shows something like this:
Code: Select all
"value1","value2","08/13/2015","value3">>out.csv
"value11","value12","08/13/2015","value13">>out.csv
"value21","value22","08/13/2015","value23">>out.csv
One more thing is that, the date will always not be in second line, please let me know what changes I need to do if it will come up in third or fourth line?
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Create the array of month numbers
set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
set /A m+=1
set "month[%%a]=!m:~1!"
)
rem Get the date from second line
< input.csv ( set /P "line=" & set /P "line=" )
for /F "tokens=4-6 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"
rem Process the file
for /F "skip=3 tokens=1-3 delims=," %%a in (input.csv) do (
echo %%a,%%b,"%myDate%",%%c>>out.csv
)
Please correct me in the script so that I can get the desired output as a CSV file.
Thanks,
Shaswat
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 12:21
by Squashman
Antonio, you also need to drop the extra quote after the date.
Re: Adding value from the header of the file to the body
Posted: 21 Sep 2015 12:27
by Squashman
Shaswat,
You are going to have to learn to provide very detailed explanations of the tasks you need accomplished. Now that we know that the number of header lines is variable, we will need to know how we are going to identify the difference between a header row and actual data and is the date line always formatted identically for each file as well.
If your file just has two header rows, this will work for you.
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Create the array of month numbers
set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
set /A m+=1
set "month[%%a]=!m:~1!"
)
rem Get the date from second line
< input.csv ( set /P "line=" & set /P "line=" )
for /F "tokens=4-6 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"
rem Process the file
for /F "skip=3 tokens=1-3 delims=," %%a in (input.csv) do >>out.csv echo %%a,%%b,"%myDate%,%%c
This will also fix the redirection problem
Code: Select all
@echo off
setlocal EnableDelayedExpansion
rem Create the array of month numbers
set m=100
for %%a in (Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec) do (
set /A m+=1
set "month[%%a]=!m:~1!"
)
rem Get the date from second line
( set /P "line=" & set /P "line=" )< input.csv
for /F "tokens=4-6 delims=- " %%a in ("%line%") do set "myDate=!month[%%b]!/%%a/%%c"
rem Process the file
for /F "skip=3 tokens=1-3 delims=," %%a in (input.csv) do echo %%a,%%b,"%myDate%,%%c>>out.csv
Re: Adding value from the header of the file to the body
Posted: 22 Sep 2015 02:51
by shaswat
Squashman wrote:You are going to have to learn to provide very detailed explanations of the tasks you need accomplished. Now that we know that the number of header lines is variable, we will need to know how we are going to identify the difference between a header row and actual data and is the date line always formatted identically for each file as well.
This is the actual header file and will remain constant.
Code: Select all
"My file"
"Run ID: S-324-894-555"
"As Of Date: 13-Aug-2015"
"IsConsolidate: N"
So as of now the date will come in the third line, but in future it may happen that they add few more fields in the header and the format will change. So I was looking for a code (if that is possible) that can be modified with the change in the date position in the header file.
Now I have one doubt, as the date row is in the third row of the header so is there any case where the code will work after doing certain changes? Please suggest..!!
Re: Adding value from the header of the file to the body
Posted: 22 Sep 2015 07:06
by Squashman
Depending on what line the Date is in and how many lines after that are actually header rows, you will need to change two lines of code.
If the date is on the third line then three
SET /P commands need to be on this line.
Code: Select all
< input.csv ( set /P "line=" & set /P "line=" & set /P "line=" )
Now you also need to determine what line starts your actual data. So if your data starts on line 5 then the SKIP value needs to be set to 4 on this line.
Code: Select all
for /F "skip=4 tokens=1-3 delims=," %%a in (input.csv) do >>out.csv echo %%a,%%b,"%myDate%,%%c
I think those two examples should give you enough information to adjust the script according to the data. Hard to write anything more dynamic without really knowing what all the data actually looks like.
Re: Adding value from the header of the file to the body
Posted: 22 Sep 2015 08:53
by shaswat
Squashman wrote:I think those two examples should give you enough information to adjust the script according to the data. Hard to write anything more dynamic without really knowing what all the data actually looks like.
Thanks a lot Squashman. Your inputs really helped me a lot. As per changed or different input files I can now change the script that will work perfectly.