3

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)
)
Marc
  • 12,706
  • 7
  • 61
  • 97
  • Is there an option to include/ exclude header row? It might be trying to read the first row as the header row. – artm Feb 29 '16 at 12:25
  • Good idea... There is such an option, but the default value is 1, which means first line in the file. – Marc Feb 29 '16 at 12:27
  • Add a header row, see what it says – artm Feb 29 '16 at 12:29
  • You need to edit your question and show (1) the table layout and (2) a few example records. – Gordon Linoff Feb 29 '16 at 12:29
  • @GordonLinoff I've added the CREATE TABLE information. An example record is posted in the question (first yellow quote). – Marc Feb 29 '16 at 12:32
  • @artm Doesn't change the behavior. Only thing that helps is adding the first line twice and ignoring the error. – Marc Feb 29 '16 at 12:42
  • The error message looks like there is a space preceding the unique identifier. I'd try checking your file to see if there are any spaces on the unique identifier – ckarst Feb 29 '16 at 18:27

2 Answers2

1

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
wBob
  • 13,710
  • 3
  • 20
  • 37
  • 2
    this presumes that it's a db to db transfer. BCP is commonly used to inject data content derived from other systems (or just hand built in excel, txt file etc). – simon coleman Jan 10 '20 at 09:31
0

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 `

simon coleman
  • 349
  • 2
  • 5