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,
Accepted Answer
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
Popular Answer
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));