linux shell script delimiter-CodePudding

How to change delimiter from current comma (,) to semicolon (;) inside .txt file using linux command?

Here is my ME_1384_DataWarehouse_*.txt file:

Data Warehouse,ME_1384,Budget for HW/SVC,13/05/2022,10,9999,13/05/2022,27,08,27,08
Data Warehouse,ME_1384,Budget for HW/SVC,09/05/2022,10,9999,09/05/2022,45,58,45,58
Data Warehouse,ME_1384,Budget for HW/SVC,25/05/2022,10,9999,25/05/2022,7,54,7,54
Data Warehouse,ME_1384,Budget for HW/SVC,25/05/2022,10,9999,25/05/2022,7,54,7,54

It is very important that value of last two columns is number with 2 decimal places, so value of last 2 columns in first row for example is:"27,08"

That could be the main problem why delimiter couldn't be change in proper way.

I tried with:

sed 's/,/;/g' ME_1384_DataWarehouse_*.txt

and every comma sign has been changed, including mentioned value of the last 2 columns.

Is there anyone who can help me out with this issue?

CodePudding user response：

Your question is somewhat unclear, but if you are trying to say "don't change the last comma, or the third-to-last one", a solution to that might be

perl -pi~ -e 's/,(?![^,] (?:,[^,] ,[^,] )?$)/;/g' ME_1384_DataWarehouse_*.txt

The -p option says to print every line; the -i~ says to modify the file, but save the original with a tilde added to its file name as a backup; and the regex uses a negative lookahead (?!...) to protect the two fields you want to exempt from the replacement. Lookaheads are a modern regex feature which isn't supported by older tools like sed.

Once you are satisfied with the solution, you can remove the ~ after -i to disable the generation of backups.

CodePudding user response：

Do you mean to change 27,08,27,08 to 27,08;27,08? I think you can try this

sed -i 's/,$[0-9]\{2\},[0-9]\{2\}$$/;\1/' ME_1384_DataWarehouse_*.txt

CodePudding user response：

With sed you can replace the nth occurance of a certain lookup string. Example:

$ sed 's/,/;/4` file

will replace the 4th comma into a semicolon.

So, if you know you have 11 fields (10 commas), you can do

$ sed 's/,/;/g;s/;/,/10;s/;/,/8' file

Example:

$ seq 1 11 | paste -sd, | sed 's/,/;/g;s/;/,/10;s/;/,/8'
1;2;3;4;5;6;7;8,9;10,11