Search code examples
csvspreadsheetbreakcut

split a spreadsheet column into 3

I have a spreadsheet that has 3 columns. Business Name/Contact Name/Email address. It's the Contact field where I need to change "Mr. John Doe" into "Mr" "John" "Doe".

I've learned as far as being able to find Mr or Mrs using:

cat merchent.csv | grep -E "Mr|Mrs"

The more I think on this process, I think it might be better to grab the whole field (with the file in CSV form) and then break up that field based on spaces. I understand the concept, and that it will involve some use of 'cut' but I don't know it well enough(yet) to understand how to accomplish this.

Solution

  • Use this script. 

    cat merchent.csv | cut -d "," -f2 | while read; do
IFS='. ' read -ra ADDR <<< "$REPLY"
for i in "${ADDR[@]}"; do
    # process "$i"
        echo -ne "$i "
done
echo " "
done

    Here I'm guessing your data is like 

    business_name1,contact_name1(in the format Mr. John Doe),email_address1
business_name2,contact_name2(in the format Mr. John Doe),email_address2