BCP copies only Integer data

I'm using BCP to copy data from a fixed length delimited file into a
SQL table, i'm using a format file, here is the format file:
8.0
14
1 SQLINT 0 4 "" 1 numero_llamada ""
2 SQLNCHAR 0 1 "" 2 operario
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1 "" 3 cabina
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 21 "" 4 numero_marcado
SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 11 "" 5 destino
SQL_Latin1_General_CP1_CI_AS
6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
""
7 SQLNCHAR 0 1 "" 7 minuto
SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 1 "" 8 hora
SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 1 "" 9 centesima_segundo
SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 1 "" 10 segundos
SQL_Latin1_General_CP1_CI_AS
11 SQLINT 0 4 "" 11 duracion_segundos
""
12 SQLINT 0 4 "" 12 costo_centavos
""
13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 3 "" 14 fin
SQL_Latin1_General_CP1_CI_AS
now my problem is that the INT and SMALLINT get into the table where
they should be, but the character fields just show "NULL" and some
fields just show squares and the last character on that string, any
ideas?
Thank you(kibagami23@.gmail.com) writes:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
...
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
Is the file a binary file or a text file? Since the integers make it,
I assume that it is a binary file.
Really what a fixed-length delimitted file is I don't know. Judging
from your format file, your file is fixed-width only.
One possibility that the character column has length-specifiers. In
such case you should specify the length of these specifiers in the
third column in the BCP file.
For more substantial input, and less speculation, please post CREATE TABLE
statement for the table, and a sample input file. (preferably enclosed
in a zip archive as attachment.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for the reply, yes the file is Fixed width, and i am putting
the length of the specifiers in the third column of the format file,
the file is a binary file created from a thrid party software developed
in Borland C Builder , i'll post what you ask for later today, any
other ideas in the meantime?
Thank you|||I suggest a row terminator on your last column (column 14). Rather than "",
specify "\r\n".
Is bcp importing all the rows? (i.e. Input file has 1000 records and your
table has 1000 rows.)
Hope that helps,
Joe
"kibagami23@.gmail.com" wrote:

> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
> 2 SQLNCHAR 0 1 "" 2 operario
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 1 "" 3 cabina
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 21 "" 4 numero_marcado
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLNCHAR 0 11 "" 5 destino
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
> ""
> 7 SQLNCHAR 0 1 "" 7 minuto
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLNCHAR 0 1 "" 8 hora
> SQL_Latin1_General_CP1_CI_AS
> 9 SQLNCHAR 0 1 "" 9 centesima_segundo
> SQL_Latin1_General_CP1_CI_AS
> 10 SQLNCHAR 0 1 "" 10 segundos
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLINT 0 4 "" 11 duracion_segundos
> ""
> 12 SQLINT 0 4 "" 12 costo_centavos
> ""
> 13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
> SQL_Latin1_General_CP1_CI_AS
> 14 SQLNCHAR 0 3 "" 14 fin
> SQL_Latin1_General_CP1_CI_AS
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
> Thank you
>|||SSd2ZSBhbHJlYWR5IHRyaWVkIHB1dHRpbmcgdGhl
IHJvdyB0ZXJtaW5hdG9yIGFuZCBpdCBrZWVw
cyBkb2luZyB0aGUKc2FtZSwgYW5kIHllcyB0aGUg
YmNwIGltcG9ydHMgYWxsIG9mIHRoZSByb3dz
LCBidXQgaW4gdGhlIHNhbWUgd2F5IG9ubHkKdGhl
IGludGVnZXIgZmllbGRzIHNob3cgb24gdGhl
IHNxbCB0YWJsZSB0aGUgY2hhcmFjdGVyIGZpZWxk
cyBzaG93cwpOVUxMIGFuZCBzb21lIG90aGVy
cyBzaG93IHNxdWFyZXMgd2l0aCB0aGUgbGFzdCBj
aGFyYWN0ZXIgb2YgdGhhdApmaWVsZHMgc2hv
d2luZywgbGlrZSB0aGlzOgoKNDQ0NDIgTlVMTCBO
VUxMCuOQsOOYtOOQtuOkseOcs+OMsDUJ5JWD
5ZWM5IWMUgk0CU5VTEwJTlVMTAlOVUxMCU5VTEwJ
ODQJNzAwCU5VTEwJTlVMTAo0NDQ0MwlOVUxM
CU5VTEwJ44C246C545i1MQnkvYzkhYNMCTUJTlVM
TAlOVUxMCU5VTEwJTlVMTAkzNAkyMDAJTlVM
TAlOVUxMCjQ0NDQ0CU5VTEwJTlVMTAnjgLbjkLnj
hLEzCeS9jOSFg0wJNQlOVUxMCU5VTEwJTlVM
TAlOVUxMCTE0MTE3CTUxMjAwCU5VTEwJPwo0NDQ0
NQlOVUxMCU5VTEwJ46C245Sw45C3MgnkvYzk
hYNMCTUJTlVMTAlOVUxMCU5VTEwJTlVMTAkyNgky
MDAJTlVMTAlOVUxMCjQ0NDQ2CU5VTEwJTlVM
TAnjoLbjpLbjgLY1CeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTI0NTg2CTc2ODAwCU5V
TEwJPwo0NDQ0NwlOVUxMCU5VTEwJ46C245S246Sw
MwnkvYzkhYNMCTUJTlVMTAlOVUxMCU5VTEwJ
TlVMTAk4NjI4NgkxNzkyMDAJTlVMTAk/ CjQ0NDQ4CU5VTEwJTlVMTAnjiLbjpLLjhLM5CeS9
jOSF
g0wJNQlOVUxMCU5VTEwJTlVMTAlOVUxMCTg1MDcJ
NTEyMDAJTlVMTAk/CjQ0NDQ5CU5VTEwJTlVM
TAnjoLbjoLLjkLkxCeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTcyMDQJNTEyMDAJTlVM
TAk/ CjQ0NDUwCU5VTEwJTlVMTAnjkLDjmLTjkLbjoLLj
pLPjnLM5CeSVg+WVjOSFjFIJNAlOVUxM
CU5VTEwJTlVMTAlOVUxMCTU4OTYJMTAyNDAwCU5V
TEwJPwoKVGhhbmsgeW91Cg==|||I forgot to say that the last sample is the select from the destination
sql table|||For some reason I can't see your entire thread, but my first suggestion is
for you to view the file with a hex editor to see whether the file contains
what you expect it to.
A very odd thing is that your format file indicates that you have
1-character
SQLNCHAR columns called minuto, hora, segundos, and centesima_segundos.
How can these be represented with a single character? It seems much
more likely to me that these would be represented as unsigned 1-byte
integers
(SQLTINYINT) or other numeric data type.
Things to check with the hex editor are: is the string data actually stored
as Unicode? Is the string data terminated with char(0), or does it
contain non-printable characters (<= 001F Unicode)? (This could
cause problems, I think.)
Posting the CREATE TABLE statement for the destination table would
help.
Steve Kass
Drew University
Kibagami23 wrote:

>I forgot to say that the last sample is the select from the destination
>sql table
>
>