Shell Programming and Scripting

Hello,

I have a csv file with "^" as text delimiters and "|" as field delimiters. It's converted from a xls file. One record looks like this:

Code:

 ^Tablete Internet^|Archos|501838|^Tableta Internet ARCHOS 80 G9
...| ...
(more lines)
... "501|838"^|330.00|USD|sl|12|0|Link|^router wireless 150 Mbps^|^5 ron^|more|^other info^|

I have bolded the "|" which is delimiting the actual fields. I need to replace the "|" and newlines which can apear multiple times in the 4th field which is also delimited by the text delimiters "^".

The output must be a csv file with | as delimiters and without any new line in the 4th field so I can insert the fields into a mysql database.

My best result by now is:

Code:

awk -F '[\^\^]' '{gsub("\|",";",$2); $2; print}'

but that does not remove the new lines... and also changes some "|" needed in the last fields.

Any ideas?

I forgot to mention that i've also tried with sed and perl but with no luck.

why can't u just try to remove all the ^ symbols??

won't that looks like a file with just | being the delimiter? just a clarification!

Because I'll have too many fields then... the 4th field will be splitted in many...
I'll do that only after I have replaced the "|" from the 4th field...

Considering your 4th field to be

Code:

 ^Tableta Internet Archos ....^

I don't think this would cause any issues!!! Please correct me if I am wrong

My 4th field is:

Code:

|^Tableta Internet ARCHOS 80 G9
...| ...
(more lines)
... "501|838"^|

don't forget that this is only one record which splits on multiple lines...

And how do you make out the end/beginning of any record?

All records end with a new line. Each record has 12 fields separated by "|". If the field contains multiple words with spaces or new lines, then is enclosed with "^".
My problem is I'm not so experienced with regular expresions. I never neded to parse a field from a record wich splits multiple lines...