2

Source file is having a special character in starting of each row. File is double space delimited.

Sample data file:

âNAME  ABC
âAGE  21
âADDRESS  XYZ street ABC city
âCONTACT  13244235
âDOJ  20181212

I want to remove â as its first and special character in each line and convert file to ; (semicolon) delimited file.

Below code I have written which is working fine in UAT but its not working correctly in PROD:

awk '{ print substr($0,1) }' FILE1.txt | sed 's/ /;/' > FILE2.txt

UAT output (desirable output which is expected):

NAME;ABC
AGE;21
ADDRESS;XYZ street ABC city
CONTACT;13244235
DOJ;20181212

PROD output:

âNAME;ABC
âAGE;21
âADDRESS;XYZ street ABC city
âCONTACT;13244235
âDOJ;20181212

Same code is working fine in UAT i.e removing first character and converting file to ; semicolon delimited, But in PROD its not removing 1st special character but converting file to semicolon delimited.

Output of locale:

locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Can anyone help me out on this ..?

Improve this question
15
  • @alecxs please don't put answers in comments. That circumvents the quality control tools of the site (voting) and can make others feel less inclined to answer if a solution has already been given. – terdon♦ Aug 12 '20 at 9:23
  • @alecxs it was an imperfect solution (you had posted sed 's/^â//' 's/ /;/' FILE1.txt > FILE2.txt which might fix the problem with the â (probably not though since I suspect that's an encoding issue and not an actual â), but would only change the first space on each line to a ;. But that's precisely why you shouldn't post things like that as comments. It was a "wrong" solution since it wouldn't actually work as intended, but nobody could downvote it. – terdon♦ Aug 12 '20 at 9:37
  • 1
    @naresh, can you confirm that sed -n '/^â/p' FILE1.txt has output on your PROD machine? Also, what is "UAT" and "PROD"? What are the differences between them? What locale settings do the two machines have? – terdon♦ Aug 12 '20 at 9:40
  • Hey @terdon , UAT and PROD and nothing but different enviroment in IT industry where code is delivered to run on Real time data. in terms of settings .. they are always same with difference in memory thats' all – Naresh Aug 12 '20 at 9:49
  • @Naresh. What locale is set? – fpmurphy Aug 12 '20 at 9:52
2

Since the â you are seeing is almost certainly an encoding issue, and assuming all of your lines are supposed to start with a capital letter, you could try this:

LC_ALL=C sed 's/^[^A-Z]*//; s/   */;/g' FILE1.txt > FILE2

That will run the command using the C locale which should ensure that whatever character your â is isn't included in the A-Z range. Then, the sed command simply removes all characters not in the A-Z range from the beginning of each line, and then converts all occurrences of two or more spaces to ;.

Improve this answer
3
  • above suggestion seems to work in UAT but still have to test in PROD environment. Will confirm once done thanks – Naresh Aug 12 '20 at 14:05
  • thanks a ton man above suggestions worked in PROD. – Naresh Aug 13 '20 at 7:16
  • thanks a ton man @terdon, above suggestion worked perfectly in PROD. – Naresh Aug 13 '20 at 7:17
4

I think that you problem could be link to the character encoding, try to display the FILE1.txt in both env with

hexdump -C FILE1.txt

It can be coded as E-ascii or UTF-8 (see https://en.wikipedia.org/wiki/%C3%82#Character_mappings)

To solve your problem, you can try to match both encoding:

        â in UTF-8                     â in other encoding
        |                              |
        v                              v
sed 's/\xc3\xa2//' FILE1.txt | sed 's/\xE2//' > FILE2.txt

Another solution could be to convert your file to a known encoding before processing it.

It might be dangerous not to test the PROD encoding.

Improve this answer
5
  • hey @Mathieu, I have tried hexdump command in both UAT and PROD with first 10 lines and it seems both matches. – Naresh Aug 12 '20 at 9:23
  • I don't think so, its related to file encoding as i took the same file from prod and processed it in UAT and it works fine. I am not sure where to look at. – Naresh Aug 12 '20 at 9:29
  • 1
    @Naresh, what is the encoding of "â"? in hexadecimal? – fpmurphy Aug 12 '20 at 9:54
  • â in hexadecimal is C3 A2 – Naresh Aug 12 '20 at 10:36
  • that should work i doubt you have tested well – alecxs Aug 15 '20 at 0:25
2

try

sed 's/^â//; s/   */;/g' FILE1.txt > FILE2.txt

And downvote if it doesn't work for you

Improve this answer
7
  • @terdon thx, however i think the g is wrong – alecxs Aug 12 '20 at 10:50
  • 1
    It won't hurt here. The OP stated it is a "double space delimited file", so although the example only has 2 fields, we may as well go for a more general solution that would also work for N fields. – terdon♦ Aug 12 '20 at 10:53
  • was looking into cut command .. it seems i got the solution .. we can use cut command with -c 1- to cut 1st character and storing it to other file after replacing double space to ; semicolon through sed – Naresh Aug 12 '20 at 11:13
  • @Naresh that won't work if the first "character" is a multi-byte UTF letter like â. Try printf 'â' | wc -c, it should print 2, not 1. – terdon♦ Aug 12 '20 at 11:20
  • yeah its printing 2. i have tried above solution but seems to be not working.. – Naresh Aug 12 '20 at 11:42
2

To remove the first character of each line, it should be:

cut -c2- # not with the GNU implementation which is currently not multi-byte aware
sed 's/^.//'
awk '{print substr($0, 2)}' # note the 2 instead of 1 as offsets are 1-based
                            # not with mawk or other non-multi-byte aware awk
                            # implementations.

Beware however that for . to match that â character and substr() to work properly, that â has to be encoded as per the locale's encoding (see output of locale charmap).

To remove the first character and replace all sequences of whitespace with ;, you can either do:

sed 's/^.//;s/[[:space:]]\{1,\}/;/g'

Or:

awk -v OFS=';' '{$0 = substr($0, 2); $1 = $1; print}'

(though beware the latter won't include a trailing ; for lines that end in blank characters, and the list of blank characters that are considered as delimiters varies with the awk implementation and locale).

Now, also beware that â (U+00E2) is encoded as byte 0xe2 in the iso8859-1 charset (aka latin1 and a few other single byte charsets). And that byte 0xe2 also happens to be the first byte of the encoding of a number of 3-byte UTF-8 characters, among which are several Unicode whitespace character (like the U+2000 to U+200B spacing characters).

So, if you're seeing a â displayed in a latin1 terminal, it could be that the input actually contains U+2002 (EN SPACE) for instance encoded in UTF-8 (0xe2 0x80 0x82), and your terminal would display that 0xe2 as â and wouldn't show anything for 0x80 and 0x82 which are not in latin1.

To get rid of that EN SPACE, you'd need to either strip 1 character is a UTF-8 locale, or strip 3 characters in a single-byte locale (like one using latin1 or the C locale).

Improve this answer
7
  • i have tried your suggestions 1st one is not changing anything .. 2nd suggestion is removing 1st letter of each attribute . For example name is ame – Naresh Aug 12 '20 at 13:31
  • @Naresh, then your real input doesn't match what you've shown in your question. Post the output of LC_ALL sed -n 'l;q' < your-file or <your-file head -n 1 | od -vtc -tx1 so we can see what the first line of that input actually contains at byte level. – Stéphane Chazelas Aug 12 '20 at 13:46
  • LC_ALL is blank in locale .. I have shared locale output above, hence its saying command not found.. For second command your-file head -n 1 | od -vtc its returning 0000000 – Naresh Aug 12 '20 at 14:04
  • @Naresh, sorry my bad, typo. I meant LC_ALL=C sed -n 'l;q' < your-file. That's <your-file head -n 1 | od -vtc -tx1, not your-file head -n 1 | od -vtc -tx1, as in you want to feed your-file into head so it can feed the first line of that to the octal dumper (here configured to display the hex and char value of each 1 byte of that line). – Stéphane Chazelas Aug 12 '20 at 15:49
  • i have ran LC_ALL=C command its returning \n 342 before each attribute of file – Naresh Aug 13 '20 at 6:11

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.