0

I have a question related to bash operating on comma-separated value files (.csv) saved with spaces as the selected separator.

As an example I'll put small .csv file here:

   A  B  C  D  
1  a  b  c  d   
2  e  f  g  h  
3  i  j  k  l  
4  m  n  o  p

And here is my question: Is that possible in bash to read specific value for example from cell C4?

Tried to find any topic with similar problem but cannot it.

Thanks in advance!

Charles Duffy
  • 280,126
  • 43
  • 390
  • 441
nemox007
  • 29
  • 4
  • CSV files are *not* an Excel-specific format. When you say "excel", you make folks think you're talking about .xls or .xlsx, which is a very different matter (for those, you'd want to use Python, which has some very good parsers available). That said -- for bash, the easiest solutions are slightly inaccurate, and won't work correctly if you have values in your cells with commas in them. Do you know if that's potentially the case here? – Charles Duffy Jul 20 '15 at 12:21
  • Actually I put .csv file with the spaces since in my excel settings I have option to show this files as normal excel files. I assume without this setting all the vaules should be separate by commas. – nemox007 Jul 20 '15 at 12:28
  • And your actual files will be saved that way, so it's not just for demonstration purposes? That works, then. (Though it means that if any of your data contains spaces, we'd need to know it to give correct answers). – Charles Duffy Jul 20 '15 at 12:29
  • Details on how you're generating a file need to be in the question; I've edited them in myself here, but you should do that in the future. – Charles Duffy Jul 20 '15 at 12:30
  • Yes, all of my files are saved in that way. Thanks, for you observations & suggestions – nemox007 Jul 20 '15 at 12:31
  • ...and none of your data contains spaces inside the fields? Then the answer currently on offer should work. – Charles Duffy Jul 20 '15 at 12:33
  • Are the column and row headers in your actual data? – glenn jackman Jul 20 '15 at 13:32

2 Answers2

2

Can do this very easily in awk :

example.sh

#!/bin/bash

awk '
{
  if(NR==5){ print $4; }
}
' < "$1"

output

$ ./example.sh input.txt 
o

details

  • NR filters the line number
  • $4 refers to the fourth field ( under C column )
amdixon
  • 3,814
  • 8
  • 25
  • 34
  • Please use `... <"$1"` or `<"$1" ...`rather than `cat $1 | ...` -- more efficient (since it gives `awk` a direct handle on a file, rather than a handle on a pipeline to a `cat` process reading from the file), and also less buggy (not quoting the filename means it's subject to string-splitting and globbing). – Charles Duffy Jul 20 '15 at 12:24
  • corrected to fix the redundant io as suggested. on the issue of csv, normally csv is comma delimeted, although the upcoming RFC specification seems to include the use of more general delimiters ( [csv-wikipedia](https://en.wikipedia.org/wiki/Comma-separated_values) ) – amdixon Jul 20 '15 at 12:28
  • Indeed, I'm aware of that -- the Python CSV module has support for alternate delimiters, but when the OP indicated that it was an "Excel file", I made an assumption (then reasonable) that they used Excel's default variant of CSV format. – Charles Duffy Jul 20 '15 at 12:32
  • true - excel has different csv types ( mac, dos ) - no idea what the difference. here i have assumed basically by looking at the input that the file is tab separated – amdixon Jul 20 '15 at 12:35
0

The tricky part is converting "C4" into column 3, row 4. Here's one way with bash:

#!/bin/bash

cell=$1
file=$2

colnum() {
    local -u col=$1
    local val=0 i
    for ((i=0; i<${#col}; i++)); do
        # ascii value of a char, ref: http://stackoverflow.com/q/890262/7552
        printf -v ascii "%d" "'${col:i:1}"
        val=$(( val*26 + ascii - 64 ))
    done
    echo "$val"
}

if ! [[ $cell =~ ^([A-Za-z]+)([0-9]+)$ ]]; then
    echo "error: invalid cell '$cell'"
    exit 1
fi

col=$(colnum "${BASH_REMATCH[1]}")
row=${BASH_REMATCH[2]}
lineno=0

while read -ra fields; do
    if (( ++lineno == row )); then
        echo "${fields[col-1]}"
    fi
done < "$file"
glenn jackman
  • 238,783
  • 38
  • 220
  • 352