How to change the encoding of a file created by SQL Server Agent job to UTF-8?

Question

I'm trying to create a daily job to query items inventory of my SAP db (SQL Server 17), save the results in a .txt file, and ftp it to my website server, where I'll parse it using PHP, and use it to update stock level on the MySQL db of the website.

I created a job on sql server agent, that will run the following query:

SELECT
Cast (ItemCode as varchar(20)) + '##'+
Cast (OnHand as  varchar(20))+ '@@' AS Item_Stock 
FROM OITW
WHERE OITW.WhsCode='01' AND OnHand>0

The result is outputted to a .txt file like so:

All of the above is working well.

The problem is that the file isn't saved as UTF-8, so after it is uploaded to the server (via ftp), any type of PHP parsing (explode, substr etc) fails.

So what I'm looking for is a way to force the txt file to be saved as UTF-8, or a way to make PHP read the file and be able to parse it as string.

I should add that both fields: OnHand and ItemCode, are numeric. OnHand is inventory field, so obviously only numbers. ItemCode may contain some non-ASCII characters, but if there are such items they are irrelevant anyway; I'm saying it to emphasize there's no fear of data loss in converting encoding, since numbers are numbers in any encoding (I think...).

Any help would be highly appreciated.

Answer 1

You can't, is the simple answer.

The more verbose answer would be to add a further step in your job that runs a Powershell script that does. Something like this should work:

$Path = "C:\{the rest of the file path in your image}\office_stock.txt"
$Content = Get-Content -Path $Path
Set-Content -Path $Path -Value $Content -Encoding UTF8