Re: BULK COPY changes row order importing Text file
From: Uri Dimant (urid_at_iscar.co.il)
Date: 02/19/04
- Next message: Dejan Sarka: "Re: Detecting Implicit data type conversions"
- Previous message: Vladimir Vasiliev: "Re: Stored procedure tuning"
- In reply to: lindawie: "Re: BULK COPY changes row order importing Text file"
- Next in thread: Steve Kass: "Re: BULK COPY changes row order importing Text file"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 14:02:02 +0200
lindawie
After running this command bcp zap..authors_staging in
authors2.dat -fauthors.bcp -S. -T
NULL
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
NULL
BCP copy in failed
NULL
"lindawie" <lindawie@my-deja.com> wrote in message
news:##D00Is9DHA.3292@TK2MSFTNGP11.phx.gbl...
> Steve,
>
> > One possibility is to add line numbers with
> >
> > C:\> find /V /N "" c:\tempo\prueba.txt > c:\tempo\prueba2.txt
>
>
> Very nice idea! I wish I'd thought of that. :)
>
> We can refine your idea a bit to avoid those extra lines in the
> output file. FINDSTR has more options than FIND, so I would use that
> instead.
>
> This command line adds line numbers delimited by a colon, but
> doesn't add any extra lines to the output file.
>
> findstr /N /C:. authors.dat > authors2.dat
>
> We can then use a bcp format file to load the data into a table with
> separate columns for each file in the file.
>
>
>
> Here's a sorta complete repro
>
> use pubs
> go
>
> -- Create a staging table with a line_nbr column.
> select 1 as line_nbr, *
> into authors_staging
> from pubs..authors
> where 0 = 1
>
> exec sp_help pubs..authors
>
>
> -- Create a tab-delimited data file.
> bcp pubs..authors out authors.dat -c -S. -T
>
> -- Add line numbers to the data file.
> findstr /N /C:. authors.dat > authors2.dat
>
>
> -- Create a format file.
> bcp pubs..authors_staging format nul -fauthors.bcp -c -S. -T
>
>
> -- Edit the first line for the colon delimiter.
> 8.0
> 10
> 1 SQLCHAR 0 12 ":" 1 line_nbr ""
> 2 SQLCHAR 0 11 "\t" 2 au_id ""
> 3 SQLCHAR 0 40 "\t" 3 au_lname ""
> 4 SQLCHAR 0 20 "\t" 4 au_fname ""
> 5 SQLCHAR 0 12 "\t" 5 phone ""
> 6 SQLCHAR 0 40 "\t" 6 address ""
> 7 SQLCHAR 0 20 "\t" 7 city ""
> 8 SQLCHAR 0 2 "\t" 8 state ""
> 9 SQLCHAR 0 5 "\t" 9 zip ""
> 10 SQLCHAR 0 3 "\r\n" 10 contract ""
>
>
> -- Load the file into the staging table.
> bcp zap..authors_staging in authors2.dat -fauthors.bcp -S. -T
>
>
> select * from authors_staging
>
>
> -- Linda
>
>
- Next message: Dejan Sarka: "Re: Detecting Implicit data type conversions"
- Previous message: Vladimir Vasiliev: "Re: Stored procedure tuning"
- In reply to: lindawie: "Re: BULK COPY changes row order importing Text file"
- Next in thread: Steve Kass: "Re: BULK COPY changes row order importing Text file"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
