CSV to MYSQL funny characters

Welcome Forums General PowerShell Q&A CSV to MYSQL funny characters

This topic contains 4 replies, has 2 voices, and was last updated by

4 years, 4 months ago.

  • Author
  • #19766

    Points: 31
    Rank: Member

    I am executing a script which gathers Windows server names in the domain, then I use the LOAD DATA LOCAL INFILE to import it into MYSQL. Everything is imported, but the first line has a couple of strange characters in the first row only. The MYSQL table collation is set to utf8_general_ci.
    First I get the server names from AD:
    $computerdetails = Get-ADComputer -server $dc -Filter {OperatingSystem -Like "Windows *Server*" } -Property * |`
    Select-Object Name, @{N="DNSHostName";E={$_.DNSHostName.Split(".")[1] + "." + $_.DNSHostName.Split(".")[2] + "." + $_.DNSHostName.Split(".")[3]}}, OperatingSystem, PasswordLastSet

    Then I set the array to the results and create the csv file:
    $result += $computerdetails
    $result | ConvertTo-Csv | Select -Skip 2 | out-file $winfilename -Encoding UTF8 -force

    The results in the table look like this, for an example:

    Name DNSHostName OperatingSystem PasswordLastSet
    "SERVER1" domainname Windows Whenever
    SERVER2 domainname Windows Whenever

    So the first row, first column has a couple of funny characters, and the server name is in quotes. All other information is fine.

    I have opened this in Notepad++ and checked for characters, of which I see only CRLF at the end of the rows.

    Does anyone have any ideas on what might be wrong?


  • #19768

    Points: 0
    Rank: Member

    When you use -Encoding UTF8 in PowerShell, you get a byte order marker at the beginning of the file. It looks like that's what you're seeing here; the MySQL import might be expecting UTF8 with no BOM.

    There are ways around that, but they're not as simple as just changing the value of -Encoding on your call to Out-File. You'll have to access some of the underlying .NET Framework methods directly.

  • #19770

    Points: 0
    Rank: Member

    You'll have to test this, but here's a quick example of what the new code might look like, after you've set up your $result array:

    $string = $result | ConvertTo-Csv | Select -Skip 2 | Out-String
    # Passing $false to this constructor tells it not to emit a byte order marker.
    $encoding = New-Object System.Text.UTF8Encoding($false)
    [System.IO.File]::WriteAllText($winfilename, $string, $encoding)
  • #19773

    Points: 31
    Rank: Member

    Wow, PERFECT, thanks! I worked on this for hours and hours, and you just fixed my issue. I would never have gotten that, thanks soooo much!

  • #19774

    Points: 0
    Rank: Member

    No problem. 🙂

The topic ‘CSV to MYSQL funny characters’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort