August 8, 2017 at 6:06 am
Sergiy - Tuesday, August 8, 2017 5:39 AMCheck your field terminators.Field 2 in the example above has a terminator "/r" - is it how it is in the actual file?"/r" would be expected for the last column, not the 2nd one.
Sorry Sergiy that's my fault, I posted and edited portion of my test file and cocked it up :blush:
Far away is close at hand in the images of elsewhere.
Anon.
August 8, 2017 at 6:14 am
This is weird. I'm not seeing the text file anywhere in this thread. I thought maybe the OP sent it to you via PM, David, but after the last few replies, I'm sure that's not the case.
Which post in this thread has the file attached?
August 8, 2017 at 6:22 am
Brandie Tarvin - Tuesday, August 8, 2017 6:14 AMThis is weird. I'm not seeing the text file anywhere in this thread. I thought maybe the OP sent it to you via PM, David, but after the last few replies, I'm sure that's not the case.Which post in this thread has the file attached?
No, the only information available is the sample portion the OP posted.
I took them at their word that the format file has 24 lines and has "\t" as the row terminator except the last which was "\r\n".
Far away is close at hand in the images of elsewhere.
Anon.
August 8, 2017 at 9:31 am
This is what failing to help us help you looks like. Without seeing the query, the format file, and the data file (with sample data, not live data) all you are going to get are shots in the dark.
August 8, 2017 at 11:22 am
sqlenthu 89358 - Friday, August 4, 2017 7:21 AMHi Guys,
I an trying to do a simple upload from a txt file to a table. I know it should be a simple bcp command but if i have to load with a where clause how can we do it. Take any small text file as an example.
If you need a WHERE clause during your initial load from the file to a "table", then you're probably trying to load the file directly into the final destination table, which I normally consider to be a bad idea for multiple reasons. My recommendation would be to always load to a staging table rather than the final table so that you can do some high performance data validation and/or cleansing prior to sending the data to it's final destination.
The error you reported has to do with the format file. You provided a part of it and made a mistake in posting it. Stop trying to paraphrase your problem. Post the actual format file as it is in your environment.
Also, your OPENROWSET command has an error in it. "Format File" should not be two words. It should be one. I suspect that you're having the same "paraphrasing" problem there. Stop messing around with all that and post the actual code that you have.
Last but not least, your format file identifies the incoming data as SQLNCHAR. Are all of the columns in the file actually Unicode? And what code page does the file actually use? If it's a UTF-8 file, you'll need to find a way to do some preprocessing because UTF-8 isn't supported in SQL Server for imports or exports until SQL Server 2016.
--Jeff Moden
August 14, 2017 at 10:43 am
Sorry for the late update everyone. Had not been a good time for me health wise. I was able to figure out what was the issue. One of the column in for mat file had SQLFLT8 with length of just 2. I changed it to 10 and it worked fine.
Once again apologies for replying late on this.
August 14, 2017 at 11:17 am
sqlenthu 89358 - Monday, August 14, 2017 10:43 AMSorry for the late update everyone. Had not been a good time for me health wise. I was able to figure out what was the issue. One of the column in for mat file had SQLFLT8 with length of just 2. I changed it to 10 and it worked fine.Once again apologies for replying late on this.
Glad you figured it out. Thanks for posting the details and solution.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply