February 19, 2019 at 6:59 am
Hi All,
I am trying to one bcp command I am not able to execute it correctly.
I have created below table:
CREATE TABLE [dbo].[Colabcp] (
[ID] varchar(60)
)
I have tried with Char, numeric, float and Int data types for ID.
Ran below query from command prompt
bcp [dbo].[Colabcp] in "D:\test\cola.xls" -c -t -S servername,portnumber -d DBNAME -T
bcp bcptest in "D:\test\cola.xls" -c -t -S servername,portnumber -d DBNAME -T
Below are the errors:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 484
One more error:
Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for ca
st specification
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 499
One more error:
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for ca
st specification
BCP copy in failed
attached the excel sheet FYR.
Please help me he here how to work on this. Am I missing anything here.
Thank You.
Regards,
Raghavender Chavva
February 19, 2019 at 7:23 am
Raghavender Chavva - Tuesday, February 19, 2019 6:59 AMHi All,I am trying to one bcp command I am not able to execute it correctly.
I have created below table:
CREATE TABLE [dbo].[Colabcp] (
[ID] varchar(60)
)I have tried with Char, numeric, float and Int data types for ID.
Ran below query from command prompt
bcp [dbo].[Colabcp] in "D:\test\cola.xls" -c -t -S servername,portnumber -d DBNAME -T
bcp bcptest in "D:\test\cola.xls" -c -t -S servername,portnumber -d DBNAME -T
Below are the errors:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 484One more error:
Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for ca
st specification
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Numeric value out of range0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 499One more error:
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for ca
st specificationBCP copy in failed
attached the excel sheet FYR.
Please help me he here how to work on this. Am I missing anything here.
BCP will not work with Excel files... period.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2019 at 7:28 am
If you want to be able to import Excel data directly into a table in SQL Server, you need to install the ACE drivers on your SQL Server and then use OPENROWSET with the proper call to import the data. It's a bit testy to originally setup but is usually worth it. I don't have the time to explain it all just now but might be able to get to it late tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2019 at 8:08 am
Thank You Jeff.
Is BCP supports .csv ?
Thank You.
Regards,
Raghavender Chavva
February 19, 2019 at 8:12 am
bcp works with text files, with any delimeter, so a csv would work.
February 19, 2019 at 8:16 am
Steve Jones - SSC Editor - Tuesday, February 19, 2019 8:12 AMbcp works with text files, with any delimeter, so a csv would work.
If I use .csv file below are the errors:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 577
Thank You.
Regards,
Raghavender Chavva
February 19, 2019 at 8:34 am
Even I have tried with .txt file also. Still below error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 515 Average : (7.77 rows per sec.)
Thank You.
Regards,
Raghavender Chavva
February 19, 2019 at 8:49 am
Raghavender Chavva - Tuesday, February 19, 2019 8:34 AMEven I have tried with .txt file also. Still below error:Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 515 Average : (7.77 rows per sec.)
Is it a .txt file that you have created and or manipulated in Excel? If so, try something else like notepad.
Here are a couple suggestions.
1. Create a error file, and output the errors. You may be able to get more information. Add this to your command /e D:\test\cola.err
2. Take a subset of the data, maybe the first 20 rows, and paste them from Excel into notepad. See if that works.
3. You likely need to specify the row delimiter. Add this to you command. /r\n
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 19, 2019 at 9:03 am
This is almost always data related. You need to look at the .csv in a text editor like notepad++ that allows you to see characters that might not be visible. Make sure you have terminators at the end of line and if you use Unicode, you set the switches for that.
February 19, 2019 at 6:56 pm
Raghavender Chavva - Tuesday, February 19, 2019 8:34 AMEven I have tried with .txt file also. Still below error:Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 515 Average : (7.77 rows per sec.)
You need to post your modified BCP command for this. Also, if the data isn't proprietary and contains no PII or any other sensitive data, please attach the text or csv file that you're trying to read.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2019 at 1:26 am
Thank You Every One for your help.
After changing the value of datatype in the SQL Server table bcp worked very well with .txt and also with csv.
Previous value was Varchar(50), I changed it to Varchar(250).
Thank You.
Regards,
Raghavender Chavva
March 4, 2019 at 12:50 pm
I have one more issue with BCP again.
Trying to execute below bcp command. Column delimiter is |. When I am trying below command getting the error.
bcp [dbo].CDBI in "D:\FEED\Extract.txt" -c -t| -r -d DSP -T -S Servername
'-r' is not recognized as an internal or external command,
operable program or batch file.
How to use | as delimiter.
Thank You.
Regards,
Raghavender Chavva
March 4, 2019 at 1:19 pm
Raghavender Chavva - Monday, March 4, 2019 12:50 PMI have one more issue with BCP again.Trying to execute below bcp command. Column delimiter is |. When I am trying below command getting the error.
bcp [dbo].CDBI in "D:\FEED\Extract.txt" -c -t| -r -d DSP -T -S Servername'-r' is not recognized as an internal or external command,
operable program or batch file.How to use | as delimiter.
bcp [dbo].CDBI in "D:\FEED\Extract.txt" -c -t\| -r\n -d DSP -T -S Servername
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2019 at 2:11 pm
Michael L John - Monday, March 4, 2019 1:19 PMRaghavender Chavva - Monday, March 4, 2019 12:50 PMI have one more issue with BCP again.Trying to execute below bcp command. Column delimiter is |. When I am trying below command getting the error.
bcp [dbo].CDBI in "D:\FEED\Extract.txt" -c -t| -r -d DSP -T -S Servername'-r' is not recognized as an internal or external command,
operable program or batch file.How to use | as delimiter.
bcp [dbo].CDBI in "D:\FEED\Extract.txt" -c -t\| -r\n -d DSP -T -S Servername
Thank You... This error now gone. But new one.
Specified path could not be found.
The file is already placed in the path and also I have full access on that file. Basically I am administrator on that box.
Moved the file to different location and tried. But same error.
Thank You.
Regards,
Raghavender Chavva
March 12, 2019 at 6:33 am
Can you please help me here.
bcp.exe [dbo].Tablename in "D:\ABC\Extract.dat" -c -t\| -r\n -d DSP -T -S Instancename
When I am executing this bcp command I am receiving below error:
The system cannot find the path specified.
Files are already placed and the account which is executing the BCP command has admin privilages on that box.
Not sure where I am missing.
Thank You.
Regards,
Raghavender Chavva
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply