extra space lines in data file that exported from a table

[shanghai2004]

Dear Expert,

I have a table created as following:

CREATE TABLE [dbo].[TMAPDATA] (
[ORGANIZATIONID] [int] NOT NULL ,
[TABLENAME] [varchar] (32) COLLATE Latin1_General_CS_AS NOT NULL ,
[FIELDKEY] [varchar] (80) COLLATE Latin1_General_CS_AS NOT NULL ,
[LANGUAGE] [smallint] NOT NULL ,
[SORTKEY] [int] NULL ,
[FIELDDESC] [varchar] (80) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
GO

I use the BCP utility to export data from the tables. The BCP commmand is:

C:\>bcp tenrox.dbo.TMAPDATA out "c:\sama data\MSSQL\data files\tenrox\_TMAPDATA
txt" -c -T -C ACP

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000

6483 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 341


Then I use BCP command to import the data file to the same table of another database. The data file contains both English and French letters.

C:\>bcp citenrox.dbo.TMAPDATA in "c:\sama data\MSSQL\data files\tenrox\_TMAPDAT
.txt" -c -T -C RAW -E -h "CHECK_CONSTRAINTS"

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast speification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast speification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast speification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast speification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast speification
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000

6478 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 2404

5 rows are missing in the destination table. I found the missing rows. Then I check the data file, I found that there is an extra spaces line in front of each missing row.

1 ORGANIZATIONTYPE IT 1 3 Service informatique

1 ORGANIZATIONTYPE IT 4 3 IT Afdeling

1 ORGANIZATIONTYPE PSO 0 1 Professional Services Organization

1 ORGANIZATIONTYPE PSO 1 1 Soci‚t‚ de services professionnels

1 ORGANIZATIONTYPE PSO 4 1 Prvanessional Services Organization

Where are the space lines come from? I use the commands export and import for 677 tables. Only very fewer have this problem. Any explanation? Help please!


Thanks

 

[SimonSellick]

It looks as though you might have some special characters (most likely carriage-return CR = 0x0D = 13, or line-feed LF = 0x0A = 10) in your data - perhaps in the FieldDesc column. Can you check for that?

 

[shanghai2004]

I don't know how to view data in Hex codes in DOS environment. But I did upload the data file to a HP mainframe. I used fcopy command to view the data in Hex code. There is no CR or LF but blanks for the extra rows. How comes there is no Hex codes for the extra rows? Anyway, I deleted the extra rows from the data file exported from table. Then do the importing, it went successfully.
By the way, could you tell me how to view data in Hex code in DoS or Windows environment?

Thanks

 

[Catadmin]

Try downloading TextPad. I think it has the option of viewing all the interesting data in a file.

http://www.textpad.com

Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.