July 13, 2006 at 10:34 am
I have read the posts on using bulk insert with text files that have text inside quotes but still can't get my file imported. I have a file with 16 fields and a table with only 2 fields. Here is what I have:
8.0
17
1 SQLCHAR 0 1 "" 0 quote ""
2 SQLCHAR 0 8000 "\",\"" 2 fldDate ""
3 SQLCHAR 0 8000 "\",\"" 0 Col002 ""
4 SQLCHAR 0 8000 "\",\"" 0 Col003 ""
5 SQLCHAR 0 8000 "\",\"" 0 Col004 ""
6 SQLCHAR 0 8000 "\",\"" 0 Col005 ""
7 SQLCHAR 0 8000 "\",\"" 0 Col006 ""
8 SQLCHAR 0 8000 "\",\"" 0 Col007 ""
9 SQLCHAR 0 8000 "\",\"" 0 Col008 ""
10 SQLCHAR 0 8000 "\",\"" 0 Col009 ""
11 SQLCHAR 0 8000 "\",\"" 0 Col010 ""
12 SQLCHAR 0 8000 "\",\"" 0 Col011 ""
13 SQLCHAR 0 8000 "\",\"" 0 Col012 ""
14 SQLCHAR 0 8000 "\",\"" 0 Col013 ""
15 SQLCHAR 0 8000 "\",\"" 0 Col014 ""
16 SQLCHAR 0 30 "\",\"" 1 keyChkNo ""
17 SQLCHAR 0 8000 "\""\r\n" 0 Col017 ""
I get the following error message when I run bulk insert:
Invalid destination table column number for source column 17.
The format file worked before with 16 columns and "," but the quotes came along for the ride.
Would someone please help me with this?
Thanks
Charles
July 13, 2006 at 11:26 am
8.0
17
1 SQLCHAR 0 1 "" 0 quote ""
2 SQLCHAR 0 8000 "\",\"" 2 fldDate ""
3 SQLCHAR 0 8000 "\",\"" 0 Col002 ""
4 SQLCHAR 0 8000 "\",\"" 0 Col003 ""
5 SQLCHAR 0 8000 "\",\"" 0 Col004 ""
6 SQLCHAR 0 8000 "\",\"" 0 Col005 ""
7 SQLCHAR 0 8000 "\",\"" 0 Col006 ""
8 SQLCHAR 0 8000 "\",\"" 0 Col007 ""
9 SQLCHAR 0 8000 "\",\"" 0 Col008 ""
10 SQLCHAR 0 8000 "\",\"" 0 Col009 ""
11 SQLCHAR 0 8000 "\",\"" 0 Col010 ""
12 SQLCHAR 0 8000 "\",\"" 0 Col011 ""
13 SQLCHAR 0 8000 "\",\"" 0 Col012 ""
14 SQLCHAR 0 8000 "\",\"" 0 Col013 ""
15 SQLCHAR 0 8000 "\",\"" 0 Col014 ""
16 SQLCHAR 0 30 "\",\"" 1 keyChkNo ""
17 SQLCHAR 0 8000 "\"\r\n" 0 Col017 ""
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 13, 2006 at 11:32 am
Tim,
I don't understand what you are saying "using the wrong theory". Would you please explain either what I am doing wrong or how to accomplish the task.
Thanks
Charles
July 13, 2006 at 12:37 pm
That's part of my signature, not directed at you. It's starting to annoy me now actually; I think I'll get rid of it. Apologies for the confusion. I should have put some text in the post, like 'try this:'. My suggested amendment is highlighted in red in the original post.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 13, 2006 at 3:30 pm
Tim,
That did the trick! My import work perfectly; thanks.
I guess I should have paid more attention to the red and less to the quote.
Thanks for the help.
Charles
July 13, 2006 at 6:17 pm
No problem. I should have explained - for others if not you - the column in question holds the field terminator for that field. For the last field, that typically includes a carriage return,linefeed pair (ascii 13,10), as in this case.
In this column on row 17, the outermost quotes just delimit the string you are specifying. The first backslash \ escapes the quote character which immediately follows it - since otherwise it would be interpreted as the closing mark of the outermost pair, and cause an error when the following text was processed. The second and third backslash escape the letters r and n to give them the special meanings Return and Newline (ascii 13,10) respectively.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply