CODEWITHSUNDEEP
sqlimportazure-sql-databasebcp
Marc
Marc

Reputation: 13194

BCP bulk import throws 'Invalid character value for cast specification' on first line

I'm tyring to import data to Azure SQL using the BCP utility. The data file has not been created by BCP export, but by me.

A line of the data file looks like this:

61b7d233-b6ee-47df-862f-46adfc487eae;Calcimycin;;;;;;;;enUS;0;1;0;0;MeSH

As you can see, there are no DateTime's involved, which are known to cause the problem.

The BCP command:

bcp dbo.Word in Word.txt -c -t; -S auu5nuabcl.database.windows.net -d dbname -U username -P pass -e error.txt

The import works apart from that the first line in the data file is not imported. The error file says:

#@ Row 1, Column 1: Invalid character value for cast specification @# 61b7d233-b6ee-47df-862f-46adfc487eae  Calcimycin                              enUS    0   1   0   0   MeSH

All other lines of the file are imported correctly. The file is created automatically by code I've written, so there is no difference in the first line of the data file and the others.

What could be the cause for this problem?

There is a similar but unanswered question here.

Addtional information:

This is the table in SQL:

CREATE TABLE skicocat.dbo.Word (
  Id uniqueidentifier NOT NULL,
  NominativeSingular nvarchar(max) NOT NULL DEFAULT (''),
  NominativePlural nvarchar(max) NOT NULL DEFAULT (''),
  GenitiveSingular nvarchar(max) NOT NULL DEFAULT (''),
  GenitivePlural nvarchar(max) NOT NULL DEFAULT (''),
  DativeSingular nvarchar(max) NOT NULL DEFAULT (''),
  DativePlural nvarchar(max) NOT NULL DEFAULT (''),
  AccusativeSingular nvarchar(max) NOT NULL DEFAULT (''),
  AccusativePlural nvarchar(max) NOT NULL DEFAULT (''),
  Culture nvarchar(50) NOT NULL DEFAULT ('en-US'),
  IsCaseSensitive bit NOT NULL DEFAULT (0),
  IsDisplayName bit NOT NULL DEFAULT (0),
  IsAmbient bit NOT NULL DEFAULT (0),
  Hits bigint NOT NULL DEFAULT (0),
  Comment nvarchar(max) NULL DEFAULT (''),
  CONSTRAINT PK_Word PRIMARY KEY CLUSTERED (Id)
)

Upvotes: 3

Views: 4416

Answers (2)

wBob
wBob

Reputation: 14379

The best file format for moving data between databases is native (-n). Therefore use the -n switch instead of character file (-c). The file is not readable to the human eye but you will have less of this type of problem.

bcp dbo.Word out Word.bcp -S someOtherServer -d dbname -U username -P pass -e error.txt -n

bcp dbo.Word in Word.bcp -S auu5nuabcl.database.windows.net -d dbname -U username -P pass -e error.txt -n

Upvotes: 1

simon coleman
simon coleman

Reputation: 373

As per @ckarst : there is a space prior to your first character. This changes the behaviour for me from working to not working & removing the space fixes it.

` Starting copy... SQLState = 22005, NativeError = 0 Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification

0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 `

Upvotes: 0

Related Questions