Import from tab delimited text file in Excel - CR character in quote marks is ignored

The following answer partly highlights the issue.

As J Ashley correctly points out, there is a difference between how Excel handles the CSV file when double-clicking vs File->Open (or Data->import).

Additionally to J Ashleys remark, I've tested and concluded the following:

When encapsulated fields contain \n (LF) or \r\n (CR-LF) they open correctly when just double-clicking, but produce the issue mentioned by OP when using File->Open (or import)
When encapsulated fields contain \r (CR) they produce the issue mentioned by OP no matter what you do. Using UTF8-BOM, No bom, double-click, File->Open, Data->Import ... always the same issue.

Hence, there doesn't seem to be any way to work around this issue from within Excel.

Possible workaround
Do a Regex find/replace in your file to change '\r([^\n])' into '\n\1'. This changes all CR's that are not followed by a LF into an LF. The \1 is just to preserve the trailing character.

Final toughts
Excel keeps surprising me in mysterious ways as to how arbitrary it handles flat text data files without providing feedback to the user... Then again, most users would be overwhelmed and confused by the notion that flat text files are not Excel files.

Edit: Powershell script to find replace in huge files

$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
Get-Content -Encoding UTF8 -ReadCount 1000 input.txt |
     Foreach-Object { 
        [System.IO.File]::AppendAllLines(
              [string]'output.txt'
            , [string[]]($_) // TODO: add regex replacement here
        ) 
    }

Wouter

Posted 2018-05-29T16:41:49.780

Reputation: 1 259

Unfortunately editing txt file is difficult as it is very big. Actually, there are several, but size varies between 1 and 10 GB and contain anything between 500k and 12M rows. Majority of text editors balk at the size. – AcePL – 2018-06-12T15:57:39.983

Ah, I also have huge files like that. I've attached a PowerShell script that can deal with that. – Wouter – 2018-06-12T19:12:27.000

Could you perhaps provide a screenshot of the issue, and possibly what result you are expecting? – piko – 2018-05-30T13:52:58.277

@piko GDPR... Data in question is address of one of the customers, down to safe place for deliveries. Will try and sanitize it, but then might as well type it all, as it will be basically the same. – AcePL – 2018-05-30T14:52:31.760

of course, don't post real information ;) Also, you might want to check this similar questions. Various solutions have been given, perhaps one might suit your need? Even though it's intended for a CSV file and not a TXT file, but since both formats are similar... You can also easily convert your TXT to a CSV, which would be easier to work with in Excel.

– piko – 2018-05-30T17:28:00.803

Import from tab delimited text file in Excel - CR character in quote marks is ignored

Answers