As you all know the bulk insert / bcp commands are made to work with UTF-16le files.
on a daily basis, We receive multiple CSV files which are in UTF-8 coding.
I found a great way to convert the file using powershell:
Set @SQLStr = 'xp_cmdshell ''powershell.exe -command "get-content ' + @SourcePathFile + ' | out-file -filepath ' + @DestinationPathFile + ' -encoding Unicode"'''
The problem is that the customer that we are dealing with doesn't want to enable xp_cmdshell (which can be understandable for some and not for others)
I tried the Bulk insert on an UTF-8 encoded file and it seems to work but I see extra characters in the first row, which puts in doubt the validity of loading a UTF8 file with bulk insert...
drop table #tmp
create table #tmp (line varchar(8000));
bulk insert #tmp from 'C:\Users\John_Doe\The_UTF8_File.csv' with (DATAFILETYPE = 'widechar',Firstrow = 1, ROWTERMINATOR = '')
select * from #tmp
Without having to install or change anything on the client's server, how can I automate this conversion process, so that the bulk insert works properly?
The idea is so simple... "convert a UTF8 file to UTF16le"... but it doesn't seem to be that simple since I can't find it anywhere on the web...
any idea is welcome.