Reading a delimited file

[pp8771]

I have a very unusual batch requirement.
I have file with a header and number of rows which are considered as data in the subsequent rows.
Number of header columns can vary and can be determined from the number of delimters. if there are three delimiters in a row - i will have four columns in the file.
The data rows(subsequent rows after the header) are not constant it can vary 2 to end of file

Sample file

Code: Select all

COMPID|COMPNAME|ADDRESS|YEAROFESTABLISTMENT
100|XYC|AWER RD|12072018
120|BNM|PQTY RD|12082018

Required Output will be header rows followed by subsequent rows.In the end of each row i will mention also the folder name(F1) and file name (u1.dat).
There are multiple files in each such folders.

Code: Select all

COMPID,COMPNAME,ADDRESS,YEAROFESTABLISTMENT##"100","XYC","AWER RD","12072018"##F1.u1
COMPID,COMPNAME,ADDRESS,YEAROFESTABLISTMENT##"120","BNM","PQTY RD","12082018"##F1.u1

Please how we can we transalate the requirement using batch to an above output.

[Squashman]

You really haven't specified enough details. Really need to know what the folder structure is and are you expecting each file to have its own output file or are you combining all the files into one.

Regardless of that, here is the base code to do one file. Hopefully you can figure out the rest.

Code: Select all

    @echo off
    setlocal enabledelayedexpansion
    
    set "filename=u1.dat"
    set "folder=F1"
    
    REM Get header row
    set /p "header="<"%filename%"
    set "header=%header:|=,%"
    
    REM get base file name witout exention
    FOR %%G IN ("%filename%") do set "basename=%%~nG"
    
    REM Read file
    FOR /F "skip=1 usebackq delims=" %%G IN ("%filename%") DO (
    	set "line=%%~G"
    	set line="!line:|=","!"
    	echo %header%##!line!##%folder%.%basename%
    )
pause

[pp8771]

Thanks for the response.
trrying to answer your question:
I am not concatenating the all the file contents into one file but will write to different file by appending file name folder name and line no.
Let say I have parent folder (A1). Under the parent folder A1 it has different subfolder A12,A13, A14 and A15. I want to read this folder name and file name and content of each filename under each folders. Could be some folders do not contain any file then I should not process that folders.

It is in continuation of the requirement.
I am using this code and listing the folders and files inside the folders by a looping the folders but encountering few issues while reading files and printing few name.
Sample input file is provide below.

Also i need to print the line numbers along with the rows.

Code: Select all

@echo off
 setlocal enabledelayedexpansion
 set WORKING_DIRECTORY=%cd%
pushD %WORKING_DIRECTORY%
for /f "usebackq tokens=*" %%a in (`dir /b/s/a:d MigrationPoc`) do (
 echo:%%~nxa
  set "vfolder=%%~nxa"
 echo  folder name %vfolder%  ; 
 for /f "usebackq tokens=*" %%a in (`dir /a-d /b %%a` ) do  (
  echo:%%~na
  set vfilename=%%~na
  set /p "header="<"%vfilename%"
  set "header=%header:|=,%"
 FOR /F "skip=1 usebackq delims=" %%G IN ("%vfilename%") DO (
    	set "line=%%~G"
    	set line="!line:|=","!"
    	REM# also i need to print the line number starting from 2
    	echo %header%##!line!##%vfolder%.%vfilename%
 )
 )
 )
 pause
popD

output:

Code: Select all

F1----->Folder name appearing properly
 folder name   ; ----> Folder name is not appearing
u1 ----> File name
The system cannot find the path specified.  --->Output is not coming but the file is present in the path
sample output:
COMPID,COMPNAME,ADDRESS,YEAROFESTABLISTMENT##"100","XYC","AWER RD","12072018"##F1.u1##<line no> 2
COMPID,COMPNAME,ADDRESS,YEAROFESTABLISTMENT##"120","BNM","PQTY RD","12082018"##F1.u1##<line no> 3
The system cannot find the file .
b1
The system cannot find the path specified.
The system cannot find the file .
c1
The system cannot find the path specified.
The system cannot find the file .
d1
The system cannot find the path specified.
The system cannot find the file .
Sch_B
 folder name   ;
File Not Found ---->If there is not file  inside a folder then file not found should not be print.It should be skipped.
Sch_C
 folder name   ;
File Not Found
Sch_D
 folder name   ;
a1
The system cannot find the path specified.
The system cannot find the file .
b1
The system cannot find the path specified.
The system cannot find the file .
c1
The system cannot find the path specified.
The system cannot find the file .
d1
The system cannot find the path specified.
The system cannot find the file .

Sample input file u1:

Code: Select all

COMPID|COMPNAME|ADDRESS|YEAROFESTABLISTMENT
100|XYC|AWER RD|12072018
120|BNM|PQTY RD|12082018

[Squashman]

Well you certainly can't read the file if you are stripping the extension from the file name before you try to use that variable to read the file.

Also note my use of delayed expansion variables. There is a reason I am using exclamation points inside a code block.

[pp8771]

In that case i will store two things in two variables. One after stripping and one with extension.Thanks for the advise.
I have rewritten it.

Code: Select all

@echo off
 setlocal enabledelayedexpansion
 set WORKING_DIRECTORY=%cd%
pushD %WORKING_DIRECTORY%
REM echo %WORKING_DIRECTORY%
for /f "usebackq tokens=*" %%a in (`dir /b/s/a:d MigrationPoc`) do (
 echo:%%~nxa
  set "vfolder=%%~nxa"
 for /f "usebackq tokens=*" %%a in (`dir /a-d /b %%a` ) do  (
  echo %%a
  echo:%%~na
  set vfilenamewithext=%%a
  set vfilename=%%~na
  set /p "header="<"!vfilenamewithext!"
  set "header=%header:|=,%"
 FOR /F "skip=1 usebackq delims=" %%G IN ("!vfilenamewithext!") DO (
    	set "line=%%~G"
    	set line="!line:|=","!"
    	echo %header%##!line!##!vfolder!.!vfilename!
 )
 )
 )
 pause
popD

But issue with reading the file still persists and I cannot able to print the line number along with file contents.

My output is:

Code: Select all

F1
u1.sql
u11
The system cannot find the file specified.
The system cannot find the file u1.sql.
b1.sql
b1
The system cannot find the file specified.
The system cannot find the file b1.sql.
c1.sql
c1
The system cannot find the file specified.
The system cannot find the file c1.sql.
d1.sql
d1
The system cannot find the file specified.
The system cannot find the file d1.sql.
Sch_B
File Not Found
Sch_C
File Not Found
Sch_D
a1.sql
a1
The system cannot find the file specified.
The system cannot find the file a1.sql.
b1.sql
b1
The system cannot find the file specified.
The system cannot find the file b1.sql.
c1.sql
c1
The system cannot find the file specified.
The system cannot find the file c1.sql.
d1.sql
d1
The system cannot find the file specified.
The system cannot find the file d1.sql.

[pp8771]

really struggling to read file and print in the above format. Any kind of assistance from experts is really appreciated.
Thanks for the earlier assistance .

Code: Select all

@echo off
 setlocal enabledelayedexpansion
 set WORKING_DIRECTORY=%cd%
pushD %WORKING_DIRECTORY%
REM echo %WORKING_DIRECTORY%
for /f "usebackq tokens=*" %%a in (`dir /b/s/a:d MigrationPoc`) do (
 echo:%%~nxa
  set "vfolder=%%~nxa"
  set vemptyfolder=
  dir /b "%%a" | findstr "^" >nul || set "vemptyfolder=%%~nxa"
  if !vemptyfolder! NEQ !vfolder! (
 for /f "usebackq tokens=*" %%a in (`dir /a-d /b %%a` ) do  (
  echo %%a
  echo:%%~na
  set vfilenamewithext=%%a
  set vfilename=%%~na
  set /p "header="<"MigrationPoc\!vfolder!\!vfilenamewithext!"
  set "header=%header:|=,%"
  
 FOR /F "skip=1 usebackq delims=" %%G IN ("MigrationPoc\!vfolder!\!vfilenamewithext!") DO (
    	set "line=%%~G"
    	set line="!line:|=","!"
    	echo %header%##!line!##!vfolder!.!vfilename!
		)
 )
 )
 )
 pause
popD

Header is not printing.

Code: Select all

Sch_A
a1.sql
a1
##"100","XYC","AWER RD","12072018"##Sch_A.a1
##"120","BNM","PQTY RD","12082018"##Sch_A.a1
##"100","XYC","AWER RD","12072018"##Sch_A.a1
##"120","BNM","PQTY RD","12082018"##Sch_A.a1
b1.sql
b1
##"100","XYC","AWER RD","12072018"##Sch_A.b1
##"120","BNM","PQTY RD","12082018"##Sch_A.b1
c1.sql
c1
d1.sql
d1
Sch_B
Sch_C
Sch_D
a1.sql
a1
b1.sql
b1
c1.sql
c1
d1.sql
d1
Press any key to continue . . .