Shell Programming and Scripting

Hi All,

Seeking for your assistance on how to combine two fields if there's a comma between them.

What i did was, extract the records from database.

file1.csv(extracted from db)

Code:

82|Supplies Station, Inc.|112012|KARISSA APPAREL, INC. - 112012|NON TRADE F/A AND SUPPLIES|7486|ORAFAS|ORAFAS|2016-03-15|STANDARD|Invoice|60 Days|2016-05-14|CONSO DATED|80400.00|Not Yet Due|0.0010056572830600|Never Validated

As you can see there's a comma on Field 2 and 3(| delimeted), if you will open it in csv the field 2 and will be consist of 2 fields same field 3 because of the csv comma delimeted.

CSV output

Code:

82|<b>Supplies Station</b>|<b>Inc.</b>|112012|<b>KARISSA APPAREL</b>| <b>INC. - 112012</b>|NON TRADE F/A AND SUPPLIES|7486|ORAFAS|ORAFAS|2016-03-15|STANDARD|Invoice|60 Days|2016-05-14|CONSO DATED|80400.00|Not Yet Due|0.0010056572830600|Never Validated

My Expected output in the csv file:

Code:

82    Supplies Station,Inc.   112012    KARISSA APPAREL, INC. - 112012     NON TRADE F/A AND SUPPLIES   7486   ORAFAS   ORAFAS   2016-03-15   STANDARD   Invoice   60 Days   2016-05-14   CONSO DATED   80400.00   Not Yet Due   0.0010056572830600   Never Validated

i used awk but still wrong

Code:

awk '{for (i=1;i<=NF;i++) $i="\""$i"\""}1' FS="|" OFS="|" file1.csv

Please advise,

Thanks,

Moderator's Comments:

When presenting input and output that contains multiple adjacent spaces and input and output lines that do not otherwise fit on a single line; using CODE tags is crucial for people reading your posts to understand what you are trying to do. You have been placed in read-only mode for a few days for your repeated refusal to use CODE tags when they are required. Read the e-mail that you received explaining why you have you received your 5th infraction for not using CODE tags. Look at the tutorial included in that e-mail. And, please start using CODE tags as required by forum rules instead of expecting moderators and administrators to format your posts for you. Continued refusal to use CODE tags properly may result in you being permanently barred from posting in the UNIX & Linux Forums.

I am totally confused by the description of your problem. You start off with a file of information extracted from a database into a character separated values file with vertical-bar as the separation character. (In which case commas should be absolutely unimportant.)

Then you say you open it in csv??? What is csv? Is it the name of a utility that you have written that assumes that every comma (and sometimes also a space following a comma) should be changed to a vertical-bar? It sounds like your entire problem is that you are using a utility that destroyed the commas in your input file when you didn't want the commas to be changed at all. Why are you using a utility that destroys your input data?

Then you say that the output that you want should randomly replace vertical-bar characters in your input file with three, four, or five space characters and sometimes remove a single space character following a comma. But the description of your problem didn't say anything about changing vertical-bar characters to random numbers of adjacent space characters.

Then you show us an awk script that surrounds each input field with double-quote characters (not affecting commas and not changing vertical-bar field separation characters).

Please give us a MUCH clearer explanation of what you are trying to do (and use CODE tags when displaying sample input, sample output, AND code segments so we have a chance of telling the difference between a single space character and sequences of multiple adjacent space tab characters).

Actually, I can't see the problem here. ".csv" implies (but doesn't insist) fields to be separated by commas (or semicolons). Spreadsheet programs would show three fields (by default) with your above data. But - when reading (any) text file, you usually can select the field separator; setting it to | will yield exactly what you seem to be after: Supplies Station, Inc. and KARISSA APPAREL, INC. - 112012 in fields 2 and 3, resp.