Skip to main content
Solved

Convert VARBINARY CSV File contents back to VARCHAR

  • November 25, 2024
  • 6 replies
  • 176 views
NickJanssen
Captain
Forum|alt.badge.img+5

Hello,

For a specific usecase, I want users to be able to import a custom CSV file. The import of this file triggers inserts in more than one table. Therefore, the standard import function Thinkwise offers is not sufficient at the moment.

In order to tackle the problem. I have a start task which has 2 relevant task parameters:

  1. file_name (data_type=NVARCHAR(512) and control=File upload). Here, the users selects a file from file explorer
  2. file_data (data_type=VARBINARY_MAX). Here, the varbinary file data is stored

The file which is being uploaded is UTF-8 Encoded. After using 

CAST(@csv_varbinary as varchar(max))

I can see the VARCHAR information. However, special characters like “ö” do not seem to work with this method: “ö” becomes: “ö”.

 

Any advice on how to proceed from here? After doing some research on the internet I tried the following statements already:

select
	@test_varb,
	CAST(@test_varb as varchar(max)),
	CAST(@test_varb as varchar(max)) COLLATE Latin1_General_100_BIN2_UTF8,
	CAST(@test_varb as varchar(max)) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
	CONVERT(VARCHAR(MAX), @test_varb, 1208),
	CONVERT(VARCHAR(MAX), @test_varb, 1)

 

Best answer by Vincent Doppenberg

Hello ​@NickJanssen,

There are two ways to do this in SQL Server.

Method 1

create function [dbo].[convert_utf8_varbinary_to_nvarchar](@varbinary_source varbinary(max))
returns nvarchar(max)
as
begin
	declare @temp table(string varchar(max) collate Latin1_General_100_CI_AS_SC_UTF8);
	insert into @temp select @varbinary_source;
	return (select convert(nvarchar(max), string) from @temp)
end

Method 2

create function [dbo].[convert_utf8_varbinary_to_nvarchar](@varbinary_source varbinary(max))
returns nvarchar(max)
as
begin
	return (select cast(concat('<?xml version="1.0" encoding="UTF-8" ?>', @varbinary_source) as xml).value('.','nvarchar(max)'))
end

Method 1 might seem more convoluted, but I would actually recommend that one, because you won't have to deal with any XML escaping. I've created scalar functions for you so they're easy to reuse.

Regarding your comment:

So it appears Indicium (?) converts the string into a different VARBINARY representation?

Yes, but it is Indicium that uses UTF-8, whereas SQL Server uses UTF-16 (or UCS-2 specifically). By default, SQL Server assumes UCS-2 for all nvarchar<->varbinary conversions and if you declare the variable and assign the text value in SSMS, as you did, the encoding will be UCS-2 and so you will get the correct result. The issue is that the varbinary value supplied by Indicium is a UTF-8 representation of the text.

This is the binary value that Indicium will supply for the word Abtlöbnitz: 0x4162746CC3B6626E69747A .

You were close with your collation solution, honestly one would expect that to work, but sadly it doesn't. Luckily, inserting the value into a table on which the column is configured to use a UTF-8 collation and then selecting the value does work.

I hope this helps.

View original
This topic has been closed for comments

Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Anne Buit
  • Community Manager
  • November 26, 2024

Hi Nick, can you try converting it to nvarchar(max) instead and see what happens?

NickJanssen
Captain
Forum|alt.badge.img+5
Anne Buit wrote:

Hi Nick, can you try converting it to nvarchar(max) instead and see what happens?

Hi Anne,

Using NVARCHAR instead of VARCHAR results in human unreadable text (both for using CONVERT & CAST). My example is a .csv file which I open in Notepad. You can see the encoding at the bottom:

I only display the word “Abtlöbnitz” here because this is a typical example where it goed wrong in our case.

After uploading the file via Thinkwise, a task parameter gets the varbinary representation of the file and we store this varbinary field in the database. In my example this is variable @test_varb.

After your suggestion I tried the following query:

select
 @test_varb,
 CAST(@test_varb as varchar(max)) as cast_varchar,
 CAST(@test_varb as nvarchar(max)) as cast_nvarchar,
 CONVERT(VARCHAR(MAX), @test_varb) as convert_varchar,
 CONVERT(NVARCHAR(MAX), @test_varb) as convert_nvarchar,
 CONVERT(VARCHAR(MAX), @test_varb, 1) as convert_varchar_1,
 CONVERT(NVARCHAR(MAX), @test_varb, 1) as convert_nvarchar_1,
 CONVERT(VARCHAR(MAX), @test_varb, 1208) as convert_varchar_1208,
 CONVERT(NVARCHAR(MAX), @test_varb, 1208) as convert_nvarchar_1208,
 CAST(@test_varb as varchar(max)) COLLATE Latin1_General_100_BIN2_UTF8 as collate_Latin1_General_100_BIN2_UTF8_varchar,
 CAST(@test_varb as nvarchar(max)) COLLATE Latin1_General_100_BIN2_UTF8 as collate_Latin1_General_100_BIN2_UTF8_nvarchar,
 CAST(@test_varb as varchar(max)) COLLATE Latin1_General_100_CI_AI_SC_UTF8 as collate_Latin1_General_100_CI_AI_SC_UTF8_varchar,
 CAST(@test_varb as nvarchar(max)) COLLATE Latin1_General_100_CI_AI_SC_UTF8 as collate_Latin1_General_100_CI_AI_SC_UTF8_nvarchar

Most of the VARCHAR options are able to transform the varbinary representation back to a human readable strong, but unfortunately not one of the options correctly retrieves the character “ö”.

Using the NVARCHAR options results in something like this:

 

NickJanssen
Captain
Forum|alt.badge.img+5

Extra information:

When I copy the value “Abtlöbnitz” directly to SQL Server Management Studio and do the following it works as expected:

  1. Transform the string to VARBINARY
  2. Transform it back to VARCHAR

So it appears Indicium (?) converts the string into a different VARBINARY representation?

SQL Code:

 DECLARE @test VARCHAR(MAX) = 'Abtlöbnitz'
 DECLARE @test_varb VARBINARY(MAX) = (SELECT CAST(@test AS VARBINARY(MAX)))

 SELECT
	@test,
	@test_varb,
	CAST(@test_varb AS VARCHAR(MAX)) as result

 

Result:

 

V
Moderator
Forum|alt.badge.img+4

Hello ​@NickJanssen,

There are two ways to do this in SQL Server.

Method 1

create function [dbo].[convert_utf8_varbinary_to_nvarchar](@varbinary_source varbinary(max))
returns nvarchar(max)
as
begin
	declare @temp table(string varchar(max) collate Latin1_General_100_CI_AS_SC_UTF8);
	insert into @temp select @varbinary_source;
	return (select convert(nvarchar(max), string) from @temp)
end

Method 2

create function [dbo].[convert_utf8_varbinary_to_nvarchar](@varbinary_source varbinary(max))
returns nvarchar(max)
as
begin
	return (select cast(concat('<?xml version="1.0" encoding="UTF-8" ?>', @varbinary_source) as xml).value('.','nvarchar(max)'))
end

Method 1 might seem more convoluted, but I would actually recommend that one, because you won't have to deal with any XML escaping. I've created scalar functions for you so they're easy to reuse.

Regarding your comment:

So it appears Indicium (?) converts the string into a different VARBINARY representation?

Yes, but it is Indicium that uses UTF-8, whereas SQL Server uses UTF-16 (or UCS-2 specifically). By default, SQL Server assumes UCS-2 for all nvarchar<->varbinary conversions and if you declare the variable and assign the text value in SSMS, as you did, the encoding will be UCS-2 and so you will get the correct result. The issue is that the varbinary value supplied by Indicium is a UTF-8 representation of the text.

This is the binary value that Indicium will supply for the word Abtlöbnitz: 0x4162746CC3B6626E69747A .

You were close with your collation solution, honestly one would expect that to work, but sadly it doesn't. Luckily, inserting the value into a table on which the column is configured to use a UTF-8 collation and then selecting the value does work.

I hope this helps.

V
Moderator
Forum|alt.badge.img+4

Hello ​@NickJanssen,

I wanted to add one more thing to my previous answer, because it's probably the best solution for your problem. You can also assign a nvarchar process variable to the file data output parameter of your task process action. This way, Indicium will handle the encoding itself. I expect that this should work without any problems in your case.

I do want to stress that using a text-based data type like nvarchar for a binary output parameter can only work for plain text files like .txt and .csv. In all other circumstances, varbinary is the only correct data type when dealing with reading files.

NickJanssen
Captain
Forum|alt.badge.img+5

Hi ​@Vincent Doppenberg ,

Actually I think that for my use case, your suggested Method 1 is better.

If I were to use a process flow, the file will be saved into the storage location, which is not what I want: I want to capture the varbinary in a task parameter, convert it to back to string and store the fields in a target table.

Thanks a lot for your response, this helps perfectly!

V
1 person likes this
  • V
    Vincent Doppenberg
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings