2

I was wondering if there was software for Linux or Windows (preferably Win), that would allow you to take a line from a CSV or spreadsheet file and replace it inside of a seperate text file?

Example:

The CSV/Excel file of movies:

Line 1: title, description, year, rating
Line 2: title2, description2, year2, rating2
Line 3: title3, description3, year3, rating3

Each line would correspond to a text file: line 1 would replace the information in textfile1
line 2 in textfile2... etc.

I’m finding it hard to google, as I’m not sure of the correct term for this stuff. Searching for "bulk text editor" and the like brings up results that seem to only allow search and replace from inside a menu, not from an external CSV or spreadsheet.

Further example from comments for clarity (sorry, this is a new concept to me):

I have a CSV file and each line has info for a certain video. They are separated by commas but each line is formatted the same:

The Simpsons, The adventures of an average family in Springfield., 1989, TV-PG

So as you can see, each line will have the format of:

Title, Description, Year, Rating

Now, I have a TXT or HTML file and each one corresponds to a video. Let’s say I have 200 videos, so I create 200 identically formatted HTML files. These HTML files or TXT files would be named 1.txt, 2.txt, 3.txt, etc. They would all contain the dummy texts of Title, Description, Year, Rating which are replaced by the CSV data, 1 CSV line per text file.

| improve this question | |
  • Do you require a GUI or would a programming solution be okay (and if so any language preference - that would be very easy to do in Python) – Nick Dickinson-Wilde Jul 15 '14 at 16:23
2

If I've understood you correctly, then this should be pretty easy in Bash (sorry, GNU/Linux only). Create a script like this in your favourite text editor:

#!/bin/bash

FILENAME=""

cat a.csv | while read line
do
    FILENAME=$((FILENAME+1))
    echo $line > $FILENAME.txt
done

Where a.csv is the CSV file you're processing. This will create separated files (1.txt, 2.txt, etc.) spilt on the CSV line (ie. one file per line of your CSV).

Save it as csv.sh (or whatever) in the directory of your CSV and run:

chmod u+x ./csv.sh

and

./csv.sh

And you should have a nice lot of files with the data separated.

Edit:

Right, think I understand now - if the .html (or .txt) files are identical you may as well let Bash create the whole content too. Here's an example with the four columns used as part of a rudimentary Bash / HTML templating system to output the CSV data in HTML:

#!/bin/bash

FILENAME="0"
cat a.csv | while IFS=',' read -r f1 f2 f3 f4; do
    FILENAME=$((FILENAME+1))
    cat >${FILENAME}.html <<EOT
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>$f1</title>
    </head>
    <body>
        <header>
            <h1>$f1</h1>
            <h2>Rating: $f4</h2>
            <details>
                <summary>Year: $f3</summary>
                <p>$f2</p>
            </details>
        </header>
    </body>
</html>
EOT
done

Note: This uses (as per your example) a comma as a delimiter (field separator).

| improve this answer | |
  • Interesting. It looks like what I need. Could you confirm this: I want to look inside those text files (which already exist as 1.txt, 2.txt) and replace instances of "title1, description2" etc with the real data in the csv file. – Confusedinbulk. Jul 15 '14 at 11:24
  • Think I'm a little confused - do you want to replace (ie. overwrite) the 1.txt, etc. contents? The above script will overwrite any data already in 1.txt, etc. Could you add an example expected output of what you'd like? – Elliot Reed Jul 15 '14 at 11:26
  • Sure. As my example, I'm using videos. I have a csv file and each line has info for a certain video. They are separated by commas but each line is formatted the same: The Simpsons, The adventures of an average family in Springfield., 1989, TV-PG ... So as you can see, each line will have the format of Title, Description, Year, Rating. Now, I have a txt or html file and each one corresponds to a video. Let's say I have 200 videos, so I create 200 identically formatted html files all containing the dummy texts of Title, Description, Year, Rating which are replaced by the csv data. – Confusedinbulk. Jul 15 '14 at 11:48
  • See the edit - this will generate your HTML files with the details from the CSV file. This would be a bit simpler than opening up each (identical) existing file and running a search and replace. – Elliot Reed Jul 15 '14 at 12:28
  • I've accepted your answer. Thank you for a timely response and for including an exact example script that demonstrates my example precisely. – Confusedinbulk. Jul 15 '14 at 12:32
3

I'm not entirely sure if I understand your intention correctly, but at least on Linux that should be easily done using core command line utilities like sed or awk. sed is a stream editor, which usually works line-based, and understands regular expressions, while awk is a "structured text processor".

But you don't even need those, as the Bash shell can deal fine with it (see e.g. bash split string into array on our sister site). Taking your example, and further assuming the only "commata" found in the input file are the separators, a simple shell script could accomplish what you describe:

idx=0                                      # initialize a counter
while read line; do                        # process the file line by line
    idx=$(($idx+1))                        # increase counter
    IFS=', ' read -a array <<< "$line"     # split line into array
    # at this point, array[0] … array[3] should hold title … rating2, so process your values:
    echo "Title: …" > "file${idx}.txt"     # adjust this to your needs
done < input.csv                           # end of loop, specifying where to read from

Of course, this is just a raw example which will need a lot of "fine tuning". I've tried to keep it simple to follow; questions on details are a better fit for our sister site Super User.

I'm not entirely sure, but it might well be possible Windows Powershell could be used in a similar manner (as you wrote you'd prefer a Windows solution).

| improve this answer | |
  • I will research this further. Thank you. If you could be so kind to view my updated question and tell me if this solution makes sense, I'd appreciate it. – Confusedinbulk. Jul 15 '14 at 12:13
  • 1
    Yes. That's what I assumed you've meant. Elliot put everything together nicely in his answer (looks like he borrowed some pieces from mine, but that's fine – I've borrowed them elsewhere, too ;) It might be you need to change his initial FILENAME="" to FILENAME=0, though, if you want to use it as a number with $((FILENAME+1)) (will place a more detailed comment there). – Izzy♦ Jul 15 '14 at 12:58
  • 1
    @Izzy oh yes, borrowed your IFS separator :) If you're ever in England I'll buy you a beer! – Elliot Reed Jul 15 '14 at 13:34
  • That's what I was referring to (the separator, not the beer :) I might count on the latter, though, some day if I know where about. But that doesn't fit here, might be better in chat :) – Izzy♦ Jul 15 '14 at 13:41
  • Thank you both so much for your time. This fixes a problem I've been wrestling with for some time. – Confusedinbulk. Jul 15 '14 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.