read csv file through windows batch file and create txt file

Question

I have an excel file with 5 values each in a line. now, i want to read the .csv file through batch file and create text file with the contents in the file. Example, if i have Apple, Mango in csv file (in one column), then the batch file should read this .csv file and should create a text file like " Apple is a fruit" and in next line " Mango is a fruit". Can anyone share the code please.

Answer 1

there are a lot of similar questions answered in SOF. Few of them to refer are :

Reading from a csv file and extracting certain data columns based on first column value

Help in writing a batch script to parse CSV file and output a text file

How do you loop in a Windows batch file?

Simplest answer is to loop thru your file using:

FOR %A IN (list) DO command [ parameters ]

Example:

Sample CSV:

0,1,2,4
1,1,2,3
2,2,4,6
3,3,6,9

bat file content:

for /f "usebackq tokens=1-4 delims=," %%a in ("sample1.csv") do (
      echo %%a %%b %%c %%d )

here tokens=1-4 meaning that we wish to read 4 column data for each line

Answer 2

I had thought to convert file using powershell in the FOR command but it's just much easier and simpler to get command line to parse the file itself using parameters.

@echo off rem     findstr "@SPC@" sample3.csv&& echo ERROR: source has our space encoding& goto :EOF
 for /F "tokens=*" %%a in (sample3.csv) do (
   set x_line=%%a
   set x_line=!x_line: =@SPC@!
   set x_line=!x_line:^&=@AND@!
   set x_line=!x_line:^|=@POR@!
   call :parcsv %%a& rem dangerous unless you parsed properly
 )
 goto :EOF

:parcsv
 set x_cola=%~1
 set x_colb=%~2
 set x_colc=%~3
 set x_cold=%~4
 for %%v in ( x_cola x_colb x_colc x_cold 
 ) do set %%v=!%%v:@POR@=^|!& set %%v=!%%v:@AND@=^&! set %%v=!%%v:@SPC@= !
 echo "!x_cola!"  "!x_colb!" "!x_colc!"   "!x_cold!"
 goto :EOF

This would properly handle a file with a line: 1,2,"apples,bananas",4. or used to convert comma delimited file into tab delimited etc.

Or actually on second thought it now breaks: 1,2,"orange bananas",5 and a half,6

So I needed to update FOR /F line from the original: for /F "tokens=* delims=," %%a in (sample.csv) do call :parcsv %%a So it uses an alternate character or code rather than space.

On and on 3rd review... it's really really bad because if you have "&" in a line you can break out of the bat file and hack yourself... so we would need to encode "&" and "|" and perhaps more!!! so let's go up and update those things too...

So perhaps powershell hybrid oneliner isn't so bad:

for /F "tokens=1-4 delims=  " %%a in ('powershell "import-csv sample.csv|convertto-csv -d `t"') do echo a:[%%a] b:[%%b] c:[%%c] d:[%%d]