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