2

I have text file that looks as follows:

12.com,128.15.8.6,TEXT1,no1,['128.15.8.6']
23com,122.14.10.7,TEXT2,no2,['122.14.10.7']
45.com,91.33.10.4,TEXT3,no3,['91.33.10.4']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8', '5.112.1.10']

Because I want to insert the file content into a table using MySQL command saying separated by ',', the lat string between the brackets (which may contain strings separated by comma) is causing problems where MySQL wants to separate them but hte table does not have enough columns for them.

I want to replace the commas that are between the brackets [] with semicolon ;.

How can I do this in Linux in a simple way?

EDIT #1

The number of strings that are separated by , inside the square brackets is not defined. It can be 1, 2, 3, etc. I need to replace , whenever found inside brackets with ;.

0

sed might work as well:

sed 'h; s/.*[[]/[/; s/,/;/g; x; s/[[].*//; G; s/\n// ' file

Explanation:

sed '   h;          save the entire line to hold space 
        s/.*[[]/[/  remove anything till the opening `[`
        s/,/;/g     replace ALL commas with semicolons
        x           save modified bracketed text, get back original line 
        s/[[].*//   get rid of the bracketed text
        G           append the modified text
        s/\n//      remove the <newline> char introduced by `G`
 ' file
0

awk is quite nice here: use open bracket as the field separator and replace all commas in the 2nd field.

awk 'BEGIN {FS = OFS = "["} {gsub(/,/, ";", $2)} 1' file
0

Assuming there's no nested [...]:

sed -e :1 -e 's/\(\[[^]]*\),/\1;/g' -e t1 < file.in > file.out
0

If it is the last field and the number of columns is fixed, you can use bash:

while IFS=, read v1 v2 v3 v4 rest; do
    echo "$v1,$v2,$v3,$v4,${rest//,/;}"
done

Result:

12.com,128.15.8.6,TEXT1,no1,['128.15.8.6']
23com,122.14.10.7,TEXT2,no2,['122.14.10.7']
45.com,91.33.10.4,TEXT3,no3,['91.33.10.4']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10']

You can place the above statements in a file (together with #!/bin/bash at the top) and feed your file as stdin to that script, or you can name the file in the script:

while IFS=, read v1 v2 v3 v4 rest; do
    echo "$v1,$v2,$v3,$v4,${rest//,/;}"
done < yourfile

This uses IFS to split the line at the ,. the first four fields are assigned to v1..v4, while everything after that is assigned to the last variable, here called rest. The echo then outputs the variables separated by ,, while in the last variable it is replaced with ;.

  • You need to redirect the while loop stdin from your file, i.e. done < file – RudiC Aug 12 '18 at 9:54
  • @RalfFriedl I have undefined number of strings inside the brackets. That was an example only. It can be 3 strings or more or less. So I need a dynamic statement that replaces any , if found. Not only 3. – user9371654 Aug 12 '18 at 10:20
  • Can you please explain your script. What is rest? – user9371654 Aug 12 '18 at 10:21
  • Unfortunately, you answer did not solve the problem. For example, I have one last column with 3 or 4 or any number of strings that I need to replace their , with ;. For example, this is one output: ['4.5.7.9'; '1.2.9.3', '2.1.1.2'] where only the first comma replaced. I need all commas inside the square brackets to be replaced. – user9371654 Aug 12 '18 at 10:25
  • I added an explanation and fixed the patter to allow multiple replacements, // instead of /. – RalfFriedl Aug 12 '18 at 10:47
1

Since the strings in the brackets are always prefixed by a single quote you could simply substitute the pair like this:

$ sed "s/',/';/g" file
12.com,128.15.8.6,TEXT1,no1,['128.15.8.6']
23com,122.14.10.7,TEXT2,no2,['122.14.10.7']
45.com,91.33.10.4,TEXT3,no3,['91.33.10.4']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10'; '1.2.3.4']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10'; '1.2.3.4'; '2.3.4.5']

Alternative that deals with 3 or less

For substrings within the brackets up to 3 in length (['xxx', 'yyy', 'zzz']). You can use sed to do this:

$ sed 's/\([^\[]*\)\([^,]*\),\([^,]*\)/\1\2;\3/g' file
12.com,128.15.8.6,TEXT1,no1;['128.15.8.6']
23com,122.14.10.7,TEXT2,no2;['122.14.10.7']
45.com,91.33.10.4,TEXT3,no3;['91.33.10.4']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10']
67.com,88.22.88.8,TEXT4,no4,['88.22.88.8'; '5.112.1.10'; '1.2.3.4']

How it works

Within this solution is a simple search & replace s/.../.../g

  • s/\([^\[]*\) - matches everything up to the [ (zero or more) and saves it to \1
  • \([^,]*\) - matches everything up to the , and saves it to \2
  • , - matches a comma
  • \([^,]*\) - matches everything that's not a comma and saves it to \3
  • /\1\2;\3/g - reconstructs the bits so that it's \1\2;\3 now, does this greedily

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.