4

I'm confused by character-sets in Unix. I have a CSV file downloaded via SFTP:

$ file -ib myfile
text/plain; charset=us-ascii

The purpose for this character-set quest is that the data within file is seen like:

Flyers: Video Center

While I want:

Flyers: Video Center

I tried:

iconv -f us-ascii -t utf-8 myfile

Which is throwing the following error:

iconv: illegal input sequence at position 528666

Please clarify what's going on regarding character-sets? Can I download in UTF-8 while getting a file via SFTP? How do we usually decide on what is junk within a character set?

$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=  

$  LC_ALL=C sed -n l  
Zimbabwe,175,Unknown Network,-1,Unknown,-1,Unknown,-1,US: Flyers: Video Center:,854088,Standard Display,-998,10/28/2014

$ iconv -f utf-8 -t l1   
iconv: illegal input sequence at position 1228354  

When set Terminal (Under Transalation, character set to UTF-8), I am able to see clean data.
But, when I read this with UTF-8 encoding using a ETL tool; the data is read as junk.

When I grep my file for data

"Flyers: Video Center" 

I don't see result for the fact that data is stored as

"Flyers: Video Center"

Can the file coding be changed so as to see what I want?

hexdump for junk characters:

0000000: 4e42 4353 3a20 4e48 4c2e 636f 6d3a 2055  NBCS: NHL.com: U  
0000010: 533a 2046 6c79 6572 733a c2a0 5669 6465  S: Flyers:..Vide  
0000020: 6fc2 a043 656e 7465 723a 2057 6861 7427  o..Center: What'  
0000030: 7320 486f 740a                           s Hot.  


$dd bs=1 skip=1228300 count=100 < temp1.csv | xxd  
100+0 records in  
100+0 records out  
100 bytes (100 B) copied, 0.000141 seconds, 709 kB/s  
0000000: 3031 342c 320a 556e 6b6e 6f77 6e20 436f  014,2.Unknown Co  
0000010: 756e 7472 792c 2d31 2c48 756c 7520 4c69  untry,-1,Hulu Li  
0000020: 7665 2c33 3738 3834 312c 4e42 433a 2041  ve,378841,NBC: A  
0000030: 6d65 7269 6361 e280 9973 2047 6f74 2054  merica...s Got T  
0000040: 616c 656e 743a 2053 686f 7274 666f 726d  alent: Shortform    
0000050: 2c33 3230 3631 3332 2c55 6e6b 6e6f 776e  ,3206132,Unknown  
0000060: 2053 6974                                 Sit  

Some garbled text:

Junk Americaâs   

must have been (Note that apostrophe is not this ' but ’)

America’s

And

BMW â Golden  

must have been (Note that hyphen is long hyphen not this -):

BMW – Golden 
  • 1
    Can you paste a hexdump of the relevant parts somewhere, please (xxd TheFile.csv | head)? Guessing the correct encoding from your question is difficult. It is definitely not us-ascii, it could be for example latin1. – jofel Dec 3 '14 at 10:49
  • 0000000: 7265 706f 7274 206e 616d 653a 2048 6973 report name: His 0000010: 746f 7269 6361 6c5f 436f 6e73 7472 6169 torical_Constrai 0000020: 6e65 645f 4176 6169 6c73 0a64 6174 6520 ned_Avails.date 0000030: 7261 6e67 653a 2032 3031 342d 3130 2d32 range: 2014-10-2 – Abhishek Dec 3 '14 at 11:06
  • 1
    Please edit your question to add the portion of the output of LC_ALL=C sed -n l on your file that covers that  character. file uses simple heuristics to determine a charset, you can't trust it for that. – Stéphane Chazelas Dec 3 '14 at 11:47
  • 2
    Please add that to your question (click on edit). If you see that  in a terminal, then it could be that your terminal is not in UTF-8 despite your locale, or it could be that the file has undergone a iso-8859-1 -> utf-8 conversion twice (we could confirm if you run LC_ALL=C sed -n l < file.csv. Either way, a iconv -f utf-8 -t l1 would probably fix it (for your terminal at least). – Stéphane Chazelas Dec 3 '14 at 12:03
  • 1
    That's a valid UTF-8 character. The iconv -f utf-8 -t l1 didn't work because that character doesn't exist in l1 (aka latin1 aka iso-8859-1). So your file seems to be valid UTF-8. The problem (if any) probably is with your ETL tool. If it expects iso8859-1 encoding, you can try and get an approximation with iconv -f utf-8 -t l1//TRANSLIT – Stéphane Chazelas Dec 3 '14 at 14:33
1

Issue #1: grepping "Flyers: Video Center"... I don't see the result :

In the hexadecimal dump of the file, notice the two bytes C2A0 between the words Flyers: and Video. This is a the UTF8 encoding for Non-breaking space. grepping NBSP is known to fail For more information, read How to remove special 'M-BM-' character with sed and use sed to replace ...Hex c2a0. Short answer is:

sed -i.bak -e 's/\xc2\xa0/ /' /path/to/file

Issue #2 `America’s' shows as 'Americaâs' (??):

Here, the dump contains three bytes e28099, known as RIGHT SINGLE QUOTATION MARK (’). Actually, there should be no problem here ! You probably got distracted by the problem above (could you confirm?)

If you use grep, sed and other tools with expression that respect your locale (UTF8!), then it will work:

printf 'America\xe2\x80\x99s\n' | grep --only-matching "[[:punct:]]"
printf 'America\xe2\x80\x99s\n' | sed -e "s/[[:punct:]]/?/"

If you want to get rid of all those UTF-8 "special" characters, use can use the tips above or iconv (but nowadays, there are few excuses not to support UTF8).

Drop all non-ascii chars:

type a.txt | iconv -f utf8 -t ASCII//TRANSLIT

Or to preserve chars from one locale:

type a.txt | iconv -f utf8 -t iso8859-15//TRANSLIT | iconv -f iso8859-15 -t utf8
1

Unlike XML, which should contain an encoding tag that describes the original content encoding you have to get that piece of information for csv with other means.

While type works pretty well on binary data, it does not work well on text data.

This is due to the way the command works. see man text. Roughly: It looks at the first couple of bytes and tries to guess the content looking up what was found in a lookup-table.

So usually it is best practice to ask the originator of the content which charset was used.

If that is not possible - for whatever reason, iconv is your best friend here.

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.