CSV to MYSQL funny characters

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 2 years, 1 month ago.

  • Author
    Posts
  • #19766
    Profile photo of bvi1998 .
    bvi1998 .
    Participant

    Hi,
    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?

    Thanks!

  • #19768
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of bvi1998 .
    bvi1998 .
    Participant

    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
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    No problem. 🙂

You must be logged in to reply to this topic.