bcp CSV to DB table
-
To all:
I am trying to import a CSV file into a database table using bcp, however I am getting the message
"Unexpected EOF encountered in the BCP data-file"
I have looked at many forum posts and articles on Google, but just can't seem to find the solution.
Here is my code:
declare @cmd varchar(8000)
select @cmd = 'bcp STH_D_Test.dbo.BCP_Test in "C:\TestFile.csv" -c -T'
exec master.dbo.xp_cmdshell @cmd
Thank you for any help in advance! =)
-
Have you tried looking at the end of the file to see what it's choking on in something like Notepad++ so you can see the characters?
-
usually when i get that error, it's because the files are coming from a UNIX file type, where the rows end in \r instead of \n
usually this fix, where I explicitly specify your row and field terminators resolves the issue for me.
EXECUTE master.dbo.xp_cmdshell 'bcp BFONRA.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"," -r"\r" -T'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you both!
When I check the file, copying into Notepad, I do not see any extra characters. To be safe, I even created created a test by hand in Notepad. Same results. =(
I am not familiar with Linux, but this is just a MS CSV or notepad, and not sure what the problem could me. Could something be off with the table I am trying to import to?
-
rayh 98086 (4/11/2013)
Thank you both!When I check the file, copying into Notepad, I do not see any extra characters. To be safe, I even created created a test by hand in Notepad. Same results. =(
I am not familiar with Linux, but this is just a MS CSV or notepad, and not sure what the problem could me. Could something be off with the table I am trying to import to?
yeah, you need something like NotePad++ to REALLY see the file.
i think both Erin and i thinkg the file is terminated in CHAR(10) (Line Feed), which is the standard for Macs/unix/linux/some other systems to end represent a new line , where bcp, without identifying the row terminator, expects CHAR(13) + CHAR(10) (the windows standard)
a screenshot of Notepadd++, where it's explicitly showing special characters:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Again, thank you, this is very helpful to see and I appreciate your patience to help me understand.
I think I may be onto the problem. I have a field that has 16 digits, so I currently have it as a Decmial(18,0) data type in my database table. It seems that this is not converting properly.
Is there some kind of cast/convert I need to do for this to pass correctly?
-
Hi,
So I seem to have my code and file somewhat working ... for one row only. For some reason my file successfully inputs the first row but fails on the 2nd row.
I have also created a test file with identical field values, and am getting the error message:
"Invalid character value cast specification"
Code:
EXECUTE master.dbo.xp_cmdshell 'bcp STH_D_Test.dbo.ValidateImportData in c:\4.csv -c -t"," -r"\r" -T'
Here is what my test file looks like:
1,1,3,4,y,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,2013-04-13,2013-04-13
2,2,3,4,y,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,2013-04-13,2013-04-13
3,3,3,4,y,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,2013-04-13,2013-04-13
-
Could you please post the table structure and attach a sample file?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply