CODEWITHSUNDEEP
pythoncsvbatch-file
James Roseman
James Roseman

Reputation: 1624

Translation from Python to Batch for a CSV program

I really need help with using a batch file to do the following:

I need to read in a text file line by line and, for each entry of a given CSV file that starts with an element found in the line of text file, write it into a new CSV file.

Or, in other words:

Read the following

example.csv
Page, Pageviews, Bounce Rate, /category/apples, 1029, 67%, /category/brussel-sprout, 3409, 92%, /category/orange, 1233, 87%

Then read the following text file line by line:

example.txt
/category/apples
/category/orange

And create this file:

new.csv
Page, Pageviews, Bounce Rate, /category/apples, 1029, 67%, /category/orange, 1233, 87%

So far I've worked on this project through a batch file that takes each individual address of a list, checks to see if it has a certain header, and then writes the extension in a list. Unfortunately this isn't enough, as I also need to then regenerate the tables from the original CSV -- but only the ones with a header. I was attempting to do this via Python but it was proving too cumbersome (and system boundaries made it not feasible to write Python, anyway). If you'd like to see code you can click on my questions through my profile, but they don't concern this piece. What I have left to do is this, and I ask for clarity and ideas as my strengths don't lie in BAT but C++ (and no, that's not an option).

Thank you for the help. This community has (with some exception) proved excellent grounds for learning valuable lessons and pushing me to succeed.

This is where I was headed with Python:

import csv

lines = []
with open('output.txt','r') as f:
    for line in f.readlines():
        lines.append(line[:-1])

with open('corrected.csv','w') as correct:
    writer = csv.writer(correct, dialect = 'excel')
    with open('input.csv', 'r') as mycsv:
        reader = csv.reader(mycsv)
        for row in reader:
            if row[0] not in lines:
                writer.writerow(row)

Upvotes: 0

Views: 318

Answers (2)

Aacini
Aacini

Reputation: 67236

I just write the Batch file you need. However, I don't know the .csv file format, so I assumed several details. These are my assumptions:

  • The .csv file contains just ASCII text with no CR-LF separators.
  • Each .csv element is separated by comma AND DO NOT CONTAIN SPACES (this limitation may be solved).
  • The size of the input .csv file is below 8 KB (required to read its contents in a Batch variable).
  • The .csv data is comprised of groups of 3 elements: a header with 3 elements, and each category with 3 elements.
  • Both the categories in the .csv file and the lines in the .txt file are placed in ASCII ascending order.
  • The categories in the .txt file ARE included in the .csv.

This is the Batch file:

@echo off
setlocal EnableDelayedExpansion
rem Read .csv data and split/store its elements in csv array.
set /P csv_data=< example.csv
set i=0
for %%e in (!csv_data!) do (
    set /A i+=1
    set csv[!i!]=%%e
)
rem Create the new.csv file with its header.
echo %csv[1]%, %csv[2]%, %csv[3]%> new.csv
rem Process each line of the text file.
set i=1
for /F %%l in (example.txt) do call :SeekAndWriteCsv %%l
goto :eof

:SeekAndWriteCsv
rem Seek the matching csv category.
set /A i+=3
if not !csv[%i%]! == %1 goto SeekAndWriteCsv
rem Write the matching category to the new.csv file.
set /A j=i+1, k=i+2
echo , !csv[%i%]!, !csv[%j%]!, !csv[%k%]!>> new.csv

That is it. Please, try it and let me know any problem you get.

EDIT

I just realized of an error: the new.csv categories are placed in separated lines. To fix that, make these changes:

Change these lines

rem Create the new.csv file with its header.
echo %csv[1]%, %csv[2]%, %csv[3]%> new.csv

by these ones

rem Create the new.csv file with its header.
echo/> enter.txt
set /P dummy=%csv[1]%, %csv[2]%, %csv[3]%< enter.txt > new.csv

Change this line

echo , !csv[%i%]!, !csv[%j%]!, !csv[%k%]!>> new.csv

by this one

set /P dummy=, !csv[%i%]!, !csv[%j%]!, !csv[%k%]!< enter.txt >> new.csv

Also, to avoid errors with some special characters, insert quotes in this line

    set csv[!i!]=%%e

this way

    set "csv[!i!]=%%e"

Upvotes: 3

jdigital
jdigital

Reputation: 12286

Windows (DOS) batch language is ill suited for this kind of task. While it may be possible, the resulting script is certain to be cumbersome, convoluted, and confusing.

Python is a fine solution, and it's not clear in the posting why you don't want to use it. If your concern is installing Python, consider py2exe which allows you to create a standalone python executable.

Upvotes: 1

Related Questions