3

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.

user1620464
  • 39
  • 1
  • 2
  • 11
  • Edit sample input and output data into your question to clarify it. You'd find selecting a pasted copy and pressing the `{}` icon may be helpful. – Magoo Jul 24 '14 at 08:11
  • Hi I have tried the code which you have given, but instead of recording the data, it is recording the path of the file. 'FOR %%A IN ('C:\temp\fruit.csv') DO ( echo %%A is a fruit >>test.txt ) ' O/p: Mango is a fruit – user1620464 Jul 24 '14 at 09:32
  • Why use batch in the first place? This is a lot simpler in PowerShell, as `Import-Csv` is built in. – Bill_Stewart Apr 28 '22 at 15:22

2 Answers2

12

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

Community
  • 1
  • 1
Maas
  • 1,317
  • 9
  • 20
  • Hi Girish, thankyou for the quick reply. I have tried the code which you have given, but instead of recording the data, it is recording the path of the file. 'FOR %%A IN ('C:\temp\fruit.csv') DO ( echo %%A >>test.txt ) ' – user1620464 Jul 24 '14 at 08:11
  • 1
    What if the CSV contains this 1,2,"apples,bananas",4? it should print string `apples, bananas` for index 3, but it seems to ignore quotes. – bryc Oct 18 '21 at 00:19
1

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]
Tony Marques
  • 121
  • 4