Modify CSV header using bash script

Question

I have a heap of CSV files being retrieved using bash scripts & wget & then creating & importing to mysql

the problem i have is the CSV files are formatted like this example

name,affiliate_feed_currency,price,msrp,special_price,affiliate_feed_freight,brand,short_description,description,Product_URL,Product_Image_URL,Product_ThumbNail_URL,Product_Category,Product_Season,Product_Gender,affiliate_feed_availability,Product_Sizes,Product_Colours,Tracking_Code,affiliate_link
"Custom Labeled Beer","AUD$","59.99","59.99","","9","Brewtopia Custom Labelled Beer","","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-who-has-everything'? For around the price of an imported beer we'll deliver to you our award winning Premium Lager professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/0_1195186629.jpg","http://www.brewtopia.com.au/images/products/thumbs/0_1195186629.jpg","Gifts","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Custom Labeled Beer</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Fathers Day Beer","AUD$","59.99","59.99","","9","Fathers Day Beer","Brewtopia Fathers Day Beer","Want to get on dad's good side for Fathers Day this year? Suck up to dad by buying him his OWN custom labelled case of beer. For around the price of an imported beerwe'll deliver to you our award winning Premium Lager professionally custom branded with dad's face and a message. You'll be in his good books for ages!","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/Dadsday/dads-day.jpg","","Gifts","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Fathers Day Beer</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Valentines Day Beer","AUD$","59.99","59.99","","9","Valentines Day Beer","Brewtopias Valentines Day Beer","Want to get your bloke to commit to you this Valentines Day? Try this... Design him his OWN beer! For around the price of an imported beer we'll deliver to you our award winning Premium Lager professionally custom branded with your man's face and a sexy message on the label. You'll have him popping the question in no time!","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/valentines/valmed.jpg","http://www.brewtopia.com.au/images/valentines/valthumb.jpg","Gifts","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Valentines Day Beer</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalized Beer","AUD$","59.99","59.99","","9","Custom Personalized Beer","Brewtopia Personalized Beer","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-who-has-everything'? For around the price of an imported beer we'll deliver to you our award winning Premium Lager professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/0_1195186629.jpg","http://www.brewtopia.com.au/images/products/thumbs/0_1195186629.jpg","Personalized Beer","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalized Beer</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalised Beer","AUD$","59.99","59.99","","9","Custom Personalised Beer","Brewtopia Personalised Beer","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-who-has-everything'? For around the price of an imported beer we'll deliver to you our award winning Premium Lager professionally custom personalised with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/0_1195186629.jpg","http://www.brewtopia.com.au/images/products/thumbs/0_1195186629.jpg","Personalised Beer","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalised Beer</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalized Wine","AUD$","12.99","12.99","","9","Custom Personalized wine","Brewtopia Personalized Wine","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-or-woman-who-has-everything'? We have a range to suit your budget - we'll deliver to you our Premium quality wine professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_wine.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_wine.jpg.jpg","Personalized Wine","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalized Wine</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalised Wine","AUD$","12.99","12.99","","9","Custom Personalised wine","Brewtopia Personalised Wine","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-who-has-everything'? We have a range to suit your budget - we'll deliver to you our Premium quality Wine professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_wine.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_wine.jpg.jpg","Personalised Wine","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalised Wine</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalised Water","AUD$","32.40","32.40","","9","Custom Personalised Water","Brewtopia Personalised Water","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to keep you hydrated? We have a range to suit your budget - we'll deliver to you our Pure Spring Water professionally personalized custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_water.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_water.jpg","Personalised Water","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalised Water</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalized Water","AUD$","32.40","32.40","","9","Custom Personalized Water","Brewtopia Personalized Water","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to keep you hydrated? We have a range to suit your budget - we'll deliver to you our Pure Spring Water professionally personalized custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_water.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_water.jpg","Personalized Water","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalized Water</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalized Champagne","AUD$","19.99","19.99","","9","Custom Personalized Champagne","Brewtopia Personalized Champagne","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-or-woman-who-has-everything'? We have a range to suit your budget - we'll deliver to you our Premium quality Sparkling Wine professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_champ.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_champ.jpg","Personalized Champagne","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalized Champagne</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"
"Personalised Champagne","AUD$","19.99","19.99","","9","Custom Personalised Champagne","Brewtopia Personalised Champagne","Looking for the most unique head turning way of branding you company event? Or just looking for a gift to impress the 'man-or-woman-who-has-everything'? We have a range to suit your budget - we'll deliver to you our Premium quality Sparkling Wine professionally custom branded with your company customer name message or logo","http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water","http://www.brewtopia.com.au/images/products/medium/affiliate buttons_champ.jpg","http://www.brewtopia.com.au/images/products/thumbs/affiliate buttons_champ.jpg","Personalized Champagne","","","","","","<img src='http://www.is1.clixgalore.com/Impression.asp?BID=34359&AfID=264058&AdID=4817' width='0' height='0' border='0'><A href='http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water'>Personalised Champagne</A>","http://www.clixGalore.com/PSale.aspx?BID=34359&AfID=264058&AdID=4817&AffDirectURL=http://www.brewtopia.com.au/design-a-label/custom-beer-wine-labels-bottled-water"

the actual names & amount of columns vary, so what I'm looking to do is change the header in all of the CSV files to be:

"name","affiliate_feed_currency","price","msrp","special_price","affiliate_feed_freight","brand,short_description","description","Product_URL","Product_Image_URL","Product_ThumbNail_URL","Product_Category","Product_Season","Product_Gender","affiliate_feed_availability","Product_Sizes","Product_Colours","Tracking_Code","affiliate_link"

I know this can be achieved using a simple sed statement, however I have no idea how to write it :(

Answer 1

This might work for you (GNU sed):

sed -r '1s/[^,]+/"&"/g' file

Surround anything which is not a comma with double-quotes.

Answer 2

for the input in your question, this line should do the job:

sed '1s/\b/"/g' file

you could add -i to do the change in-place.

test with your data:

kent$  echo 'column1,column2,column3,column4,column5
"value1","value2","value3","value4","value5"'|sed '1s/\b/"/g'
"column1","column2","column3","column4","column5"
"value1","value2","value3","value4","value5"

EDIT

add an awk solution, it works for header with spaces. for sed one-liner, pls check Zero's answer.

awk -v q="\"" 'NR==1{$0=q$0q;gsub(/,/,q"&"q)}7' file

and test:

kent$  echo 'column1,column2,column3,column4,col                umn5
"value1","value2","value3","value4","value5"'|awk -v q="\"" 'NR==1{$0=q$0q;gsub(/,/,q"&"q)}7'
"column1","column2","column3","column4","col                umn5"
"value1","value2","value3","value4","value5"

Answer 3

One way with awk (with not work with csv having embedded commas):

awk 'BEGIN{FS=OFS=","}NR==1{for(i=1;i<=NF;i++) $i=q $i q}1' q='"' mycsv > newcsv

Answer 4

Yes, it's a sed one-liner:

sed -i -r '1{s/(^|$)/"/g;s/,/","/g}' file.csv

This means:

• -i - edit files in place

• -r - use extended regular expressions

• 1{...} - perform the operations in {...} only on the first line of the file

• s/(^|$)/"/g - replace (s) either the beginning (^) or end ($) of the line with ", everywhere they occur (g), then

• s/,/","/g - replace (s) each instance of , with "," everywhere it occurs (g)