Powershell: Import-Csv does not handle newlines containing just \r (Carriage Return)

[Szeraax]

Steps to reproduce

"one,two,three`rthree,three,three" | out-file test.csv
import-csv test.csv

Expected behavior

Expected result:

one   two   three
---   ---   -----
three three three

Actual behavior

import-csv : The member "three" is already present.

Environment data

Tested on windows 8.1 and windows X (10).

Name                           Value
----                           -----
PSVersion                      5.1.14393.206
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14393.206
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Steps to fix

It seems that the ImportCsvHelper method located in my C:WINDOWSMicrosoft.NetassemblyGAC_MSILMicrosoft.PowerShell.Commands.Utilityv4.0_3.0.0.0__31bf3856ad364e35Microsoft.PowerShell.Commands.Utility.dll is the culprit.

It looks like the code here:

        private bool IsNewLine(char ch)
        {
            bool result = false;
            if (ch == '\n')
            {
                result = true;
            }
            else if (ch == '\r' && this.PeekNextChar('\n'))
            {
                result = true;
            }
            return result;
        }

should include

            else if (ch == '\r')
            {
                result = true;
            }

and then all will be happy. Thanks!

[Szeraax]

By the way, Get-Content does handle this case correctly. My solution for these files is to use Get-Content foo.csv | ConvertFrom-Csv.

[MaximoTrinidad]

Hum! I normally use both "rn" then I can get the expected result. I don't think something is wrong here.

"one,two,threernthree,three,three"| out-file test.csv
import-csv test.csv

Results:
one two three
--- --- -----
three three three

By the way, just a reminder, for Windows 5.x issues need to routed to UserVoice forum: https://windowsserver.uservoice.com/forums/301869-powershell

[Szeraax]

Good to know about UserVoice!

And yes, using rn and n both work fine. Its only just the plain `r that causes this issue. If I could, I would beat whoever creates files with r as a line ending, but that's illegal and stuff.

The fact that Get-Content can read a file that uses just carriage returns means that there is inconsistency between using Get-Content foo.csv | ConvertFrom-Csv and Import-Csv. I would think those two usages should perform the same.

[MaximoTrinidad]

Now I see what you're referring with the 'r`'. I notice the inconsistency between import-csv and get-content. Yeap! This might be a Bug!!

At the same time PowerShell is not working in WSL Bash Console as some of it previous behavior, like Scroll-Up and screen refresh issues, are back again making it hard to work with it.

[jeffbi]

Isn't "`r" carriage-return (CR, 0x0D) rather that line-feed (LF, 0x0A)? Unix-style line endings are LF. Old, pre-OS X mac line-endings were CR.

[Szeraax]

Yes

On May 4, 2017 1:41 PM, "jeffbi" [e-mail] wrote:

Isn't "`r" carriage-return (CR, 0x0D) rather that line-feed (LF, 0x0A)?
Unix-style line endings are LF. Old, pre-OS X mac line-endings were CR.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/PowerShell/PowerShell/issues/3692#issuecomment-299288454,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AF9Az8UrHTvosw2SQeGaOpPc7jMz1bbcks5r2ioDgaJpZM4NP0xM
.

[thezim]

-LineDelimiter would be a nice addition.

[Szeraax]

If we add line delimiter, do we add it to get-content too?

On May 4, 2017 5:35 PM, "Greg Zimmerman" [e-mail] wrote:

-LineDelimiter would be nice addition.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/PowerShell/PowerShell/issues/3692#issuecomment-299337503,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AF9AzxB9aF9IMohz-Bh_hd2-IDCrwx3xks5r2mC6gaJpZM4NP0xM
.

[MaximoTrinidad]

I think it should also be added in the "Get-Content" in order to keep some kind on consistent behavior.

+1 on to add the parameter '-LineDelimiter'.

:)

[iSazonov]

There is the informational RFC 4180 for CSV:

1. Each record is located on a separate line, delimited by a line
   break (CRLF). For example:

  aaa,bbb,ccc CRLF
  zzz,yyy,xxx CRLF

This obviously does not take into account Unix style EOL (LF) that we should support.
I agree that we should support old MAC style (CR, 'r') too.

Modern W3C standard - Model for Tabular Data and Metadata on the Web
line terminators

An atomic property that sets the line terminators flag to either an array containing the single provided string value, or the provided array. The default is ["rn", "n"].

It should also be noted that the SQL bcp utility has support of custom (EOL) _terminators_.

[Szeraax]

Personally, I'm not sure that it is worth making a -LineDelimiter parameter, given that I can Get-Content a csv file, split it using any delimiter I want, and then ConvertFrom-Csv to already accomplish the same thing. As just a regular powershell user, I don't expect import-csv or get-content to have custom EOL characters/terminators/delimiters.

I'll let others decide that one though.

[mklement0]

@MaximoTrinidad You beat me to it: I meant to point you to the fact that Get-Content already has a -Delimiter parameter, though its current behavior is surprising: #3706

[MaximoTrinidad]

LOL @mklement0!!
:)

[iSazonov]

Although files with 'r' EOL is a rare case, we should fix the Issue.

[Szeraax]

I agree that should be fixed to work the same as get content. I don't think
that we should add anything else though (at least, not based on this ticket)

[thezim]

@Szeraax yes, fix it first.

[mi-hol]

@SteveL-MSFT maybe this issue could be fixed using the code proposed by @Szeraax any time soon?

[iSazonov]

I can pull PR on next week if the fix will be approved.

[SteveL-MSFT]

Seems like treating a single CR the same as a single LF is reasonable.

[lzybkr]

There are 3 line ending variants supported by the PowerShell parser - CRLF (Windows), LF (Unix), and CR (old Apple), so it's very reasonable for CSV to do the same.

See here for more excessive details.

[bcrotty]

@iSazonov I'm still having an issue with this. My PS version is 5.1.18362.145. When I import a CSV where a line ends in \r instead of \r\n, the final value gets set to \r instead of an empty string, which is what I'd expect. Here's sample code:

"Name,Date`r`nBob,`r" | Set-Content ".\Test.csv" -NoNewline
$test = Import-Csv .\Test.csv
$test[0].Date -eq "`r"

"Name,Date`r`nBob,`r`n" | Set-Content ".\Test.csv" -NoNewline
$test = Import-Csv .\Test.csv
$test[0].Date -eq ""

Django reporting outputs CSVs that end with \r instead of \r\n, which is causing errors in some scripts I have. Now that I understand the issue, I can work around it, but it seems like the issue isn't fully resolved with Import-Csv.

[mi-hol]

@bcrotty did you notice that this repository is for PowerShell Core (with version 7 preview 5 recently release)?
the difference is explained here
Actually I am surprised that an issue for the very different product Windows PowerShell 5.1 (not covered here) wasn't closed right away.

[bcrotty]

@mi-hol Got it.

[iSazonov]

@bcrotty You can use Windows 10 Feedback tool or UserVoice site to report but before check latest Windows 10 builds.