Thursday, 31 March 2016

BASH - Fixing a CSV by appending lines

Currently I have a CSV file that contains address information, the problem I have is that the address field has embedded new line characters so when I try and load it into MySQL it doesn't work.



Here is an example of what my csv file looks like:



ID|NAME|ADDRESS|PHONE
1|"JOHN DOE"|"1 SHORT ROAD LONDON UNITED KINGDOM"|"01234 567 890"
2|"JANE DOE"|"1 SHORT ROAD LONDON
UNITED KINGDOM"|"01234 567 890"
3|"BOB DOE"|"1
SHORT ROAD
LONDON
UNITED KINGDOM"|"01234 567 890"


And Here is what I want it to look like:



ID|NAME|ADDRESS|PHONE
1|"JOHN DOE"|"1 SHORT ROAD LONDON UNITED KINGDOM"|"01234 567 890"
2|"JANE DOE"|"1 SHORT ROAD LONDON UNITED KINGDOM"|"01234 567 890"
3|"BOB DOE"|"1 SHORT ROAD LONDON UNITED KINGDOM"|"01234 567 890"


My initial approach was to count the amount of '|' in the header line and have that as the target count.



typeset -i target
target=`head -1 broken.csv | awk -F\| '{print NF-1}'`


Then what I wanted to do was to iterate through the file line by line doing the same count on each line



while read -r line
do
count=`echo $line | awk -F\| '{print NF-1}'`


But I can't figure out the code for the next bit



What I want to do is



if count == target
echo $line >> fixed.csv
else
#append the next line and do the count again


I think I need a nested loop but I can't quite figure out the syntax so was hoping someone could give me some insight.



Any help much appreciated :)

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...