CSV to MYSQL funny characters

PowerShell Help
1

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!

2

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.

3

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)
4

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!

5

No problem. :slight_smile: