batch for loop, csv parsing and issue with proper output to the file

Question

let me describe my problem. I have a csv file exported from excel with large amount of data. The file has a title in the first row and column headings in the second. I need to extract only two columns (2nd and 3rd) from that file, put them to 1 column and send the output to another file.

Example:

Title
colA , colB , colC , colD ,...
abc  , def  , ghi  , jkl  ,...
abc  , def  , ghi  , jkl  ,...
abc  , def  , ghi  , jkl  ,...
abc  , def  , ghi  , jkl  ,...

The thing is, that the csv parser fails when it meets the row containing a string with - ( ) @ characters. (the loop treats them as delimiter I think, so it gives me an out of range error each time).

Here is what I already have.

@Echo off & setlocal EnableExtensions
setLocal EnableDelayedExpansion

REM creating and clearing files
copy /y NUL C:\list1.csv >NUL
copy /y NUL C:\list1_tmp.csv >NUL
copy /y NUL C:\exportedColumns.csv >NUL
copy /y NUL C:\Result.txt >NUL

set Result=C:\Result.txt
set Source=C:\sourcelist.csv
set list1=C:\list1.csv
set list1_tmp=C:\list1_tmp.csv
set expCol=C:\exportedColumns.csv

REM skip 1st two lines from source file and put to output file list1
for /f "skip=2 delims=*" %%a in (%Source%) do (echo %%a >>%list1%)

REM shorten each line to 500 chars and put it to new file
for /f "tokens=* delims=" %%a in ("%list1%") do (
set s=%%a
set s=%s:~0,500% 
echo.%s% >> "%list1_tmp%"
)
REM ^^^^^^^^^^^ this is not working. It puts only 1 space to the output file

rem Parsing the csv file
rem Process the file:
call :ProcessFile < %list1_tmp%
exit /B

:ProcessFile
set /P line=
:nextLine
    set line=:EOF
    set /P line=
    if "!line!" == ":EOF" goto :EOF
    set i=0
    for %%e in (%line%) do (
        set /A i+=1
        for %%i in (!i!) do (
        if %%i==1 echo %%~e >> %expCol%
        if %%i==2 echo %%~e >> %expCol%
    )
    if %%i==3 goto nextLine
    REM I don't want it to process all the columns
    )
goto nextLine

I'd like to ask you to look at this and help me to get 2 columns into one and put the output to 1 file.

I'd be extremely grateful.

For the echo that's being evaluated before you want, would it help to change it to >>"%list1_tmp%" echo.!s! to delay the expansion of %s%? (I just reordered the command redirection to keep from echoing a trailing whitespace into %list1_tmp%, but that's not the important part.) — rojo
– rojo, Commented Mar 20, 2013 at 17:58

Sean Landsman · Accepted Answer · 2013-03-21 14:14:14Z

2

How about this?

for /f "skip=2 tokens=2,3 delims=, " %i in (input.csv) do echo %i%j >> output.csv

Edit:

To replace / with newlines, you could try this:

@echo off

for /f "skip=2 tokens=2,3 delims=, " %%i in (test.csv) do call :replace %%i%%%j
goto :eof

:replace
set string=%*
For /f "tokens=1,* delims=/" %%a in ('echo %string%') Do (
echo.%%a
If not "%%b"=="" call :replace %%b)

For an input of:

title
colA , colB , colC , colD ,...
abc  , def  , g\hi  , jkl  ,...

the above would output:

defg
hi

edited Mar 21, 2013 at 14:14

answered Mar 20, 2013 at 17:59

Sean Landsman

7,1972 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

skazichris Over a year ago

the return was that the ij was unexpected.

skazichris Over a year ago

Ok, I've just changed %i to %%i and echo %i%j to %%i and it worked. Now I need to sort it and remove duplicates. I might use your help with the removal, although I'll digg this board first :)

skazichris Over a year ago

All right. It is now sorted and deduplicated. Any idea how to replace a slash to an enter sign? ^p?

Sean Landsman Over a year ago

@user2192002 see edit! but you may still need to do some tweaking for your particular data

skazichris Over a year ago

Sorry to bother you again. I have 1 file with a list of names in one column. I've managed to get rid of all quotemarks from each line. But I cannot get the proper code to change / ( ) characters to ^p (enter,new line) and put an output to new file. Could you please help me out?

|

Nate Hekman · Accepted Answer · 2013-03-20 18:06:58Z

0

One of the problems you've mentioned is in the line for %%e in (%line%) do ..., when %line% contains a special character such as (, which naturally will mess up the interpreter.

You may be able to avoid that by using string substitution in order to put quotes around each column. For example (I'm skipping some of your code to just focus on the part with the problem):

:ProcessFile
set /P line=
:nextLine
    for %%e in ("%line:,=" "%") do (
        echo %%~e
    )
goto nextLine

Notice this part: "%line:,=" "%". That is replacing all commas with " ", as well as adding " at the beginning and end of the line.

So if a particular line we're processing looks like this:

abc, def (foo), ghi

The for will be expanded to this:

for %%e in ("abc" "def (foo)" "ghi") do ...

Everything is nicely contained in quotes so the ( doesn't interfere. Of course if you have quotes in a particular column, that will interfere...

The next line, where I use %%e, I make it %%~e in order to strip the quotes.

answered Mar 20, 2013 at 18:06

Nate Hekman

6,6771 gold badge29 silver badges30 bronze badges

1 Comment

skazichris Over a year ago

" " was unexpected at this moment

rojo · Accepted Answer · 2013-03-20 18:59:03Z

It just so happens that I've been playing around with accessing CSV files as ADODB recordsets this morning. My code might be useful to you. As it is, the script loops through every .csv file in the current directory, displaying column = value for each row.

The JScript should be pretty easy to modify to combine the columns as you want. And since this is a batch / JScript hybrid, you can take your pick whether you want to create a Scripting.FileSystemObject object or just redirect the output of the cscript line to generate your new .csv file.

Here's the code for csv.bat. *shrug* This is not intended to be the end answer, but a suggested alternate path to try.

@if (@a==@b) @end /*

:: batch portion

@echo off
setlocal

:: force 32-bit environment for ODBC drivers
if exist "%windir%\syswow64\cmd.exe" (set "cmd=%windir%\syswow64\cmd.exe") else set "cmd=cmd.exe"

for /r %%I in (*.csv) do (
    echo Processing %%~nxI:
    echo;
    %cmd% /c cscript /nologo /e:jscript "%~f0" "%%~dpI" "%%~nxI"
    echo;
)

goto :EOF

:: JScript portion */
var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");

var dsn = "Driver={Microsoft Text Driver (*.txt; *.csv)};"
    + "Dbq=" + WSH.Arguments(0) + ";"
    + "Extensions=asc,csv,tab,txt;";

try { conn.Open(dsn); }
catch(e) {

    // If the Microsoft Text Driver didn't work,
    // try the MS Jet 4.0 provider instead.

    var dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    + WSH.Arguments(0)
    + ";Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";

    try { conn.Open(dsn); }
    catch(e) {

        // If that didn't work either, then give up.

        WSH.Echo("Unable to create ADODB connection.");
        WSH.Quit(1);
    }
}

rs.Open("SELECT * from " + WSH.Arguments(1), conn, 2, 4);

while (!rs.EOF) {
    for (var i=0; i<rs.Fields.Count; i++) {
        WSH.Echo(rs.Fields(i).Name + ' = ' + rs.Fields(i));
    }
    rs.MoveNext;
}

rs.Close();
conn.Close();

Collectives™ on Stack Overflow

batch for loop, csv parsing and issue with proper output to the file

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related