Need help with BCP

Hi,

I'm trying to import a csv into an existing table (CustomerAddress).  I keep getting the following error:

Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Here's a record from the csv:

"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"

I believe it has to do with the comma after the -t terminator but I can't figure out the syntax.  Any help would be greatly appreciated.

Thanks,
blue
declare @FilePath varchar(200)
declare @FileName varchar(100)
declare @FileDirectory varchar(100)
declare @Query varchar(300)
set @FilePath = '"\\nd\gl\AppArc\CSE\PQ\'
set @FileDirectory = 'data\'
set @FileName = 'dataFile.csv"'
set @Query = ' bcp PER.dbo.CustomerAddress in '+ @FilePath+@FileDirectory+@FileName + ' -c -t, -S "der900" -U "temp" -P "temp"'
 
print @Query
exec master..xp_cmdshell @Query

Open in new window

blue44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
No.  that's correct.  It's probably due to the fact that you have double quotes around your data and it's expecting each field to be everything between the , (commas).  To test it, take one line, remove all the double quotes, save it to a new file and try that file.
0
BrandonGalderisiCommented:
You may also want to look at using bulk insert instead of BCP since you are running this from SQL.


http://msdn.microsoft.com/en-us/library/ms188365.aspx
0
blue44Author Commented:
Thanks for the information.  You were correct.  When I removed the double quotes, the script ran without errors.  Is it possible to modify the bcp script so that it can exclude double quotes?  If not, how would you rewrite the above code to use BCP?

Many thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BrandonGalderisiCommented:
You could open the file in notepad (or a GOOD text editor if the file is large) and do a search and replace " with nothing then resave the file.

Otherwise you will need to insert the data into a table that contains all varchar fields, update the fields to remove the " from the beginning and end, then move the data to the correct table.
0
BrandonGalderisiCommented:
On second thought, the search/replace MAY not be the best option since it is possible that you could have " in your data that you want to maintain.  But if you know that you don't, then you can safely remove it.
0
blue44Author Commented:
Thanks for the update.

I decided to try and and load the file into a table with only varchar data types but am getting the following error now:

Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

I used the saem record as above:

"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"

All my code is below:
create table [dbo].[CustomerAddress](
	[DP] [varchar](50) NULL,
	[CD] [varchar](15) NULL,
	[CD] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PD] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SS] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ACODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[REL] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
 
declare @FilePath varchar(200)
declare @FileName varchar(100)
declare @FileDirectory varchar(100)
declare @Query varchar(300)
set @FilePath = '"\\nd\gl\AppArc\CSE\PQ\'
set @FileDirectory = 'data\'
set @FileName = 'dataFile.csv"'
set @Query = ' bcp PER.dbo.CustomerAddress in '+ @FilePath+@FileDirectory+@FileName + ' -c -t, -S "der900" -U "temp" -P "temp"'
 
exec master..xp_cmdshell @Query

Open in new window

0
BrandonGalderisiCommented:
In order to avoid the right truncation error, you need to account for the fact that every field is 2 characters wider.  For example, CD which contains the value that you want as 773 is actually being imported as "773" which is 5 characters.

If you add 2 to every length, it should get you past this error.
0
blue44Author Commented:
Ahhh...that makes sense.  Should've caught that one...thanks!! I was able to load the data now.  One last question, how do I do a search/replace on all the fields in the table to remove the double quotes?
0
BrandonGalderisiCommented:
First....

create table [dbo].[CustomerAddress](
        [DP] [varchar](50) NULL,
        [CD] [varchar](15) NULL,
        [CD] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


you have CD twice.

Now for the update:
update [dbo].[CustomerAddress]
set dp = substring(dp, 2, datalength(dp)-2)
,cd = substring(cd, 2, datalength(dp)-2)
...repeate to end
...
where left(dp,1)='"' and right(dp,1)='"'

Open in new window

0
blue44Author Commented:
Nice piece of code -- that certainly did the trick -- THANKS!!

Unfortunately, there seems to be one last issue.  i tried inserting more than one record and got the string error again.  Any ideas as to why?

Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

As you can see from the record set below, it's the same record six times.  Is this an EOL issue?

Thanks again for sticking with me on this.  TGIF!!
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"

Open in new window

0
BrandonGalderisiCommented:
I'm loading up my SQL 2000 box now to look at this version of BCP and possibly bulk insert.
0
BrandonGalderisiCommented:
Ok.  Use this.  You didn't change all of your field widths.  So I made them a couple characters bigger across the board.

make sure you change your database name and file location again.
bulk insert tempdb.dbo.customeraddress 
from 'c:\temp\sqlstuff.csv' 
with (FIELDTERMINATOR =',',rowterminator='\n')
select * from customeraddress
 
update [dbo].[CustomerAddress]
set dp = substring(dp, 2, datalength(dp)-2)
,cd = substring(cd, 2, datalength(cd)-2)
,cd2 = substring(cd2, 2, datalength(cd2)-2)
,cr = substring(cr, 2, datalength(cr)-2)
,pd = substring(pd, 2, datalength(pd)-2)
,pr = substring(pr, 2, datalength(pr)-2)
,ss = substring(ss, 2, datalength(ss)-2)
,te = substring(te, 2, datalength(te)-2)
,acode = substring(acode, 2, datalength(acode)-2)
,sdate = substring(sdate, 2, datalength(sdate)-2)
,rel = substring(rel, 2, datalength(rel)-2)
 
where left(dp,1)='"' and right(dp,1)='"'
go
select * from customeraddress

Open in new window

0
blue44Author Commented:
Thanks for the code!!!  I ran it and am now getting the following error:

Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 11 (REL).

I've set the datalength on that field to 10 and it doesn't make a difference.  Again, it does work with a single record.  I've attached the actual data file with 5 records (changed it from csv to txt for uploading).  I think it has something to do with the row terminator.  Any ideas?
dataFile.txt
0
BrandonGalderisiCommented:
Did you expand column 11?
0
blue44Author Commented:
Yes.  I changed it to varchar(10) .  Again, it works with a single row but not multiple rows.   Very, very strange....
0
BrandonGalderisiCommented:
Given the below as my CSV file...


"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"
"10/01/2008","222222222","773","970000999999","123456465135","1234567891","RIP","MOH","ONP","1901-01-01-12.00.00.000000","ARC"



The following code works with NO errors.  Granted, this is a simplified data set with multiple copies of the same record, so I can't help but think that if you are getting errors that your second record is larger than your first.
use tempdb
if object_id('[dbo].[CustomerAddress]') is not null
drop table [dbo].[CustomerAddress]
go
create table [dbo].[CustomerAddress](
        [DP] [varchar](50) NULL,
        [CD] [varchar](15) NULL,
        [CD2] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PD] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [TE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ACODE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [REL] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
 
go
bulk insert tempdb.dbo.customeraddress 
from 'c:\temp\sqlstuff.csv' 
with (FIELDTERMINATOR =',',rowterminator='\n')
select * from customeraddress
 
update [dbo].[CustomerAddress]
set dp = substring(dp, 2, datalength(dp)-2)
,cd = substring(cd, 2, datalength(cd)-2)
,cd2 = substring(cd2, 2, datalength(cd2)-2)
,cr = substring(cr, 2, datalength(cr)-2)
,pd = substring(pd, 2, datalength(pd)-2)
,pr = substring(pr, 2, datalength(pr)-2)
,ss = substring(ss, 2, datalength(ss)-2)
,te = substring(te, 2, datalength(te)-2)
,acode = substring(acode, 2, datalength(acode)-2)
,sdate = substring(sdate, 2, datalength(sdate)-2)
,rel = substring(rel, 2, datalength(rel)-2)
 
where left(dp,1)='"' and right(dp,1)='"'
go
select * from customeraddress

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blue44Author Commented:
Very strange!! I ran your exact code and actually copied your data into a new csv file and am still getting these errors:

Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 11 (REL).

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)


I'm using SQL2K if that makes any difference.  I appreciate all your time on this.  I'd like to award you the 500 pts at this point and then add another 500 if you can continue to try and solve it.  Are you agreeable with this?

Thanks!!!
0
BrandonGalderisiCommented:
I was running it on SQL 2000 too but I don't have 2000 in front of me right now to continue testing it.  Did you do it with the Customer table definition I have above using TEMPDB or to your existing table?

I'm not worried about an extra 500 points.  Until we get this completely resolved, I haven't earned the first 500.  Can it wait about 5 hours until I am at home and have SQL 2000 in front of me again?  If not, I can jump through some hoops.
0
blue44Author Commented:
I ran it using my existing database with the customeraddress table.  All the code is exactly as your posted except changes to the database name and file path/file name.  The contents of the file are from your posting.

No worries on getting back to me right away...take your time.  Thank you for your generous offer to try and finish the question.  I really, really appreciate it...:-)
0
BrandonGalderisiCommented:
Double check the customer table definition compared to what I have.  Make sure all the data types and lengths are at least as big, if not bigger, to what I have.  Your last post still had a few fields to small.
0
BrandonGalderisiCommented:
Remember that since your data has text qualifiers (the ") that each field has to be two characters longer than the longest data component for that field.
0
blue44Author Commented:
Here's the exact code I ran.  All the fields seem fine.  I even added a zero to all the field lengths and still the same error.  There must be something with the end of line character??

drop table [dbo].[CustomerAddress]
go
create table [dbo].[CustomerAddress](
        [DP] [varchar](50) NULL,
        [CD] [varchar](15) NULL,
        [CD2] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PD] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PR] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SS] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [TE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ACODE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SDATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [REL] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
 
go
bulk insert xxx.dbo.customeraddress
from '\\networkaddress\g\T6_3.csv'
with (FIELDTERMINATOR =',',rowterminator='\n')
select * from customeraddress
 
update [dbo].[CustomerAddress]
set dp = substring(dp, 2, datalength(dp)-2)
,cd = substring(cd, 2, datalength(cd)-2)
,cd2 = substring(cd2, 2, datalength(cd2)-2)
,cr = substring(cr, 2, datalength(cr)-2)
,pd = substring(pd, 2, datalength(pd)-2)
,pr = substring(pr, 2, datalength(pr)-2)
,ss = substring(ss, 2, datalength(ss)-2)
,te = substring(te, 2, datalength(te)-2)
,acode = substring(acode, 2, datalength(acode)-2)
,sdate = substring(sdate, 2, datalength(sdate)-2)
,rel = substring(rel, 2, datalength(rel)-2)
 
where left(dp,1)='"' and right(dp,1)='"'
go
select * from customeraddress
0
BrandonGalderisiCommented:
What are you using to save the file?  Perhaps you should tinker with different row terminators.

 \n
\r

I found this:
http://msdn.microsoft.com/en-us/library/ms191485(SQL.90).aspx

which is in the 2005 BOL but all should still be valid.  Perhaps a change to \r is all your need based upon how the file is saved.
0
blue44Author Commented:
I've actually come across that same link in my research to this question and tried various combinations but to no avail.  I just tried them again and it still doesn't work.  The only time it works is if I have a single record with no rowterminator (truncated file).  Do you think this a bug with SQL Bulk Insert?

Thanks!
0
BrandonGalderisiCommented:
What is generating the file?  Have you opened/saved the file since it was generated?  If so, with what?  Because different editors use different row terminators.
0
blue44Author Commented:
Bingo!!.  I'm using Textpad to generate this file, however, when I open it in Notepad, I don't get a carriage return but a little box character between each row.  The original data file I think originates from Unix.

I opened the same file in Notepad and changed the little boxes to carriage returns and the file processed fine.  So, the script works!!  I guess all I need is some type of utility that converts unix to dos.  Do you know of any?
0
BrandonGalderisiCommented:
Have you tried running this against an original file, unaltered, from your unix system?

Ok.  So when you open it in textpad, does it prompt you to convert the file from unix to dos format.  I know ultraedit does.  It also allows me to change to binary mode and view the actual ascii character.  Perhaps textpad does the same.

Otherwise, you may be better off opening a new question to assist with some file transformation.  This could actually be done with vbscript then allowing you to automate the file transformation and execution of the bulk insert.

If you know for sure that you will NEVER have a comma in your data (your field terminator) then you could easily rewrite the whole thing in vbs.

It could:
open
split by new line (gives you records)
split each line by , (this is the tricky part because i've never done text qualifiers ie. " in conjunction with vbs splitting)
insert the records into your server.

But I think that's a new question maybe.  One I'd be glad to help you with.  But it's better for the PAQ to keep this one on the topic of bulk insert a CSV.

0
blue44Author Commented:
I actually download the file from a web site as a CSV and when I open it in TextPad it doesn't prompt me.  I'll open a new question regarding this.  Hopefully, there's just an exe I can call via a SQL job that will do the conversion.

Thanks again for all your help!!!
0
BrandonGalderisiCommented:
Have you tried it without opening it in textpad is what I was asking?
0
blue44Author Commented:
I tried opening it in notepad and the row delimiters looked similar.  If I run it against the raw file without first opening it in textpad, I get the following error message:

Bulk Insert fails. Column is too long in the data file for row 1, column 11. Make sure the field terminator and row terminator are specified correctly.
 
0
BrandonGalderisiCommented:
I know you said that you tried a bunch of different delimiters.  But have you tried the various delimiters against the unaltered file?

The problem with writing the vbs is going to be the end of line character.  That's the primary split that I mention above.  Unless we can identify that character (which can be done by various methods) WITHOUT modifying the file in any way, neither the SQL split or VBS split will be able to be coded correctly :)
0
blue44Author Commented:
Great news!!! I got it to work using a utility called 'tofrodos'.  It converted the unix file to win.  

The only remaining issue is that this conversion leaves a blank line in the beginning of the file.  Do you by chance know how to remove the first line in a file in windows progrmmatically? Is there a utility, macro, VI script out there? I could always post this is as a new question if you prefer.

Thanks again for all your help!
0
BrandonGalderisiCommented:
Well the first row can be skipped by using the firstrow option.


FIRSTROW [ = first_row ]
Specifies the number of the first row to copy. The default is 1, indicating the first row in the specified data fil

http://msdn.microsoft.com/en-us/library/aa225968(SQL.80).aspx

It MAY automatically ignore the last line if it's blank.  Worth trying :)
0
blue44Author Commented:
Perfect.  That solved it once and for all.  The final code is below.  It was great working with you on this -- thanks again!  I'm sure new SQL brain teasers await in the future...
if object_id('[dbo].[CustomerAddress]') is not null
drop table [dbo].[CustomerAddress]
go
create table [dbo].[CustomerAddress](
        [DP] [varchar](500) NULL,
        [CD] [varchar](150) NULL,
        [CD2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CR] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PD] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PR] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [TE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ACODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SDATE] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [REL] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
 
go
 
declare @cmd varchar(1000)
set  @cmd = '\\networkaddress\g\todos.exe -a \\networkaddress\g\T6_3.csv'
exec master..xp_cmdshell @cmd
 
go
 
bulk insert CSE.dbo.customeraddress 
from '\\networkaddress\g\T6_3.csv'
with (FIELDTERMINATOR =',',rowterminator='\n',firstrow=2)
 
go 
 
update [dbo].[CustomerAddress]
set dp = substring(dp, 2, datalength(dp)-2)
,cd = substring(cd, 2, datalength(cd)-2)
,cd2 = substring(cd2, 2, datalength(cd2)-2)
,cr = substring(cr, 2, datalength(cr)-2)
,pd = substring(pd, 2, datalength(pd)-2)
,pr = substring(pr, 2, datalength(pr)-2)
,ss = substring(ss, 2, datalength(ss)-2)
,te = substring(te, 2, datalength(te)-2)
,acode = substring(acode, 2, datalength(acode)-2)
,sdate = substring(sdate, 2, datalength(sdate)-2)
,rel = substring(rel, 2, datalength(rel)-2)
 
where left(dp,1)='"' and right(dp,1)='"'
go
select top 10 * from customeraddress
go

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.