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:
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,
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
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));