Inserting GUIDs with SqlBulkCopy

Question

I'm trying to do a bulk insert with the SqlBulkCopy class from flatfiles created by the SQL Server Management Import Export Wizard. The files are comma separated.

One row in the file can look like this:

{DCAD82A9-32EC-4351-BEDC-2F8291B40AB3},,{ca91e768-072d-4e30-aaf1-bfe32c24008f},900001:1792756,900001:1792757,basladdning,2011-04-29 02:54:15.380000000,basladdning,2011-04-29 02:54:15.380000000,{20A3C50E-8029-41DE-86F1-DDCDB9A78BA5}

The error I get is:

System.InvalidOperationException was unhandled
Message=The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

So the problem is the converting from string to GUID.

I have tried the following:

• Tried different encodings, UTF8 and ANSI
• Removed the { } surrounding the GUIDS
• Added ' ' around the GUIDS, with and without { }

Any suggestions? In the flat file where the '' is, is a column that also has Guid as datatype. The column is NULL where the wizard exported it as ''. Can that be the problem?

The code:

using (var file = new StreamReader(filePath))
using (var csv = new CsvReader(file, false)) 
using (var bcp = new SqlBulkCopy(CreateConnectionString()))
{
    bcp.DestinationTableName = "table";
    bcp.WriteToServer(csv);
}

Table definition:

CREATE TABLE [beata].[T_FeatureInstance]( 
    [FeatureInstanceId] [uniqueidentifier] NOT NULL, 
    [FeatureInstanceParentId] [uniqueidentifier] NULL, 
    [FeatureTypeAliasId] [uniqueidentifier] NOT NULL, 
    [Uuid] [varchar](1000) NOT NULL, 
    [VersionId] [varchar](50) NOT NULL, 
    [SkapadAv] [varchar](255) NULL, 
    [Skapad] [datetime] NOT NULL, 
    [UppdateradAv] [varchar](255) NOT NULL, 
    [Uppdaterad] [datetime] NOT NULL, 
    [Gs] [uniqueidentifier] NOT NULL,

Answer 1

In your DataTable.Columns.Add include the data type as a second parameter. That should do the trick. Example:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(Guid));

Answer 2

The problem is that the empty column in {DCAD82A9-32EC-4351-BEDC-2F8291B40AB3},,{ca91e768-072d-4e30-aaf1-bfe32c24008f} is being interpreted as '' instead of NULL. I just tried an INSERT STATEMENT on my local database, and that error went away when I specified NULL instead of '' There is another error you might get:

Conversion failed when converting date and/or time from character string.

The reason for this is that SQL only allows 3 digit decimal precision for milliseconds. So you have to round off '2011-04-29 02:54:15.380000000' to '2011-04-29 02:54:15.380', and it will work fine.

One way of doing it would be as mentioned here Alternatively, you could modify CsvReader code to return DBNULL when the value is string.Empty in CsvReader.GetValue(int) function. Have a look here

Answer 3

I think I found answer for those who are using a popular freeware code class called BulkDataReader. I have the same issue and I tried to post this a bit ago but was since I was asking another question and not providing an answer that post was removed. That's ok I didn't realize that was against the rules.

This time, I've got an answer so hopefully this post will stay on here. I'm going to leave the setup portion of my problem so it's well defined and nobody is confused as to why the solution doesn't work for them. I got the code for the BulkDataReader class from a colleague and it seems he might have gotten that from another well-known answer source so everyone will know how to find BuldDataReader from a search engine.

This issue is setup like this:

I too, tried all sort of formats for the GUIDS in the CSV file including: N’3192E434-F479-4B32-B516-AC658F4B7B89’ {3192E434-F479-4B32-B516-AC658F4B7B89} (3192E434-F479-4B32-B516-AC658F4B7B89) “{3192E434-F479-4B32-B516-AC658F4B7B89}”

A real sample line from my CSV would be: 1,AAPL,452.2012,2013-01-24 13:24:27.000,3192E434-F479-4B32-B516-AC658F4B7B89,3192E434-F479-4B32-B516-AC658F4B7B89

If I remove the 2 Guids and import to a table without those columns it works fine. With the Guids to Unique Identifier columns I get this error: Message = {"The given value of type String from the data source cannot be converted to type unique identifier of the specified target column."}

My control code is pretty basic where the BulkDataReader is quite cumbersome to walk through so be prepared if you're trying to debug it.

using (IDataReader reader = new BulkDataReader(new StreamReader("C:\\test.csv"), false))
        {
            String connectionStr = GetConnString();
            SqlConnection connection = new SqlConnection(connectionStr);
            connection.Open();

            SqlTransaction transaction = connection.BeginTransaction();
            try
            {
                SqlBulkCopy copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction);
                copy.DestinationTableName = "TestTable6";
                copy.WriteToServer(reader); //ERROR OCCURS HERE: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column
                transaction.Commit();
            }
            catch (Exception exe)
            {
                transaction.Rollback();
            }
            finally
            {
                connection.Close();
            }
        }

So the error is actually occurring within the .NET SqlBulkCopy class. However, it happens right after the BulkDataReader reads a Guid value. That GetValue method is called from External Code meaning it's buried in the plumbing between the BulkDataReader's streamReader and the StreamWriting stuff in the SqlBulkCopy class. It wasn't necessary to dig into all that using my favorite reflection utility. I found that when the BulkDataReader's method IDataRecord.GetValue(int i) returns a string that is really a Guid, SqlBulkCopy cannot convert that string to a Unique Identifier no matter what format it's in. There is likely some obscure and encoding format but I could not find one that would work. However, if I simply return the value as a proper System.Guid type, then SqlBulkCopy converts it to unique identifier just fine. Thus, a simple solution to what seems to be a nightmarish serialization issue. Just copy over the entire IDataRecord.GetValue(int i) method with this and it should work. I tried many of the CLR to SQL data types but not all of them so there could still be another one where you have to play this deserialization game but this should solve the issue for the most part.

  object IDataRecord.GetValue(int i)
    {
        ValidateDataReader(DataReaderValidations.IsInitialized | DataReaderValidations.IsNotClosed);

        if (((IDataRecord)this).IsDBNull(i))
            return DBNull.Value;
        else
        {
            //For some reason the SqlBulkCopy.WriteToServer method will call this GetValue method when reading 
            //the value and it doesn't seem to know how to convert the string value of a Guid to a unique identifier.
            //However, it does actually know how to convert a System.Guid to a UniqueIdentifer so we can return that if
            //the value parses to Guid
            if (IsGuid(this[i]))
                return Guid.Parse(this[i]);
            else
                return this[i];
        }
    }

    bool IsGuid(object value)
    {
        if (value != null)
        {
            Guid testGuid = Guid.Empty;
            if (Guid.TryParse(value.ToString(), out testGuid))
                return true;
        }

        return false;
    }

I hope this helps someone and sorry I broke the blog rules the first time.