Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2002-10-29 : 15:10:22
|
| I am trying to import a file that has more columns in it than the destination table using a format file, but every time I run BCP, it says there is an I/O error while reading the file. I have tried having the format file in the same directory I am copying from, and pathing it out completely, and it doesn't help. This is what my format file looks like, can anyone spot the problem?7.0281 SQLCHAR 0 10 "|" 1 M_LOAN2 SQLCHAR 0 20 "|" 2 M_LOAN_TYPE3 SQLCHAR 0 20 "|" 3 M_FINANCING_TYPE4 SQLCHAR 0 20 "|" 4 M_PROGRAM_TYPE5 SQLCHAR 0 20 "|" 5 M_PRORGRAM_SUB_TYPE6 SQLCHAR 0 20 "|" 6 M_NOTE_REV_DATE7 SQLCHAR 0 20 "|" 7 M_AUDIT_FLAG8 SQLCHAR 0 20 "|" 8 M_MI_COVERAGE9 SQLCHAR 0 20 "|" 9 M_LENDER_TYPE10 SQLCHAR 0 20 "|" 10 M_STATE_CODE11 SQLCHAR 0 20 "|" 11 M_PURPOSE_CODE12 SQLCHAR 0 20 "|" 0 dummy13 SQLCHAR 0 20 "|" 0 dummy14 SQLCHAR 0 20 "|" 12 CLOSDATE15 SQLCHAR 0 20 "|" 13 CLC_CODE16 SQLCHAR 0 20 "|" 14 M_CONDITION_017617 SQLCHAR 0 20 "|" 15 M_CORR_ID18 SQLCHAR 0 20 "|" 16 M_CORR_NAME19 SQLCHAR 0 20 "|" 17 M_AGENT_ID20 SQLCHAR 0 20 "|" 18 M_AGENT_NAME21 SQLCHAR 0 20 "|" 19 M_ACCT_EX22 SQLCHAR 0 20 "|" 20 M_REGION_MGR23 SQLCHAR 0 20 "|" 21 M_CLOSED_PKG_REC_DATE24 SQLCHAR 0 20 "|" 22 M_REVIEWER_NAME25 SQLCHAR 0 20 "|" 23 M_SOMD_DOCS_YES26 SQLCHAR 0 20 "|" 24 M_SOMD_DOCS_NO27 SQLCHAR 0 20 "|" 25 M_SENT_TO28 SQLCHAR 0 20 "/r/n" 0 dummy |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-29 : 15:20:59
|
Well, the row terminator characters should be \r instead of /r:7.0281 SQLCHAR 0 10 "|" 1 M_LOAN2 SQLCHAR 0 20 "|" 2 M_LOAN_TYPE3 SQLCHAR 0 20 "|" 3 M_FINANCING_TYPE4 SQLCHAR 0 20 "|" 4 M_PROGRAM_TYPE5 SQLCHAR 0 20 "|" 5 M_PRORGRAM_SUB_TYPE6 SQLCHAR 0 20 "|" 6 M_NOTE_REV_DATE7 SQLCHAR 0 20 "|" 7 M_AUDIT_FLAG8 SQLCHAR 0 20 "|" 8 M_MI_COVERAGE9 SQLCHAR 0 20 "|" 9 M_LENDER_TYPE10 SQLCHAR 0 20 "|" 10 M_STATE_CODE11 SQLCHAR 0 20 "|" 11 M_PURPOSE_CODE12 SQLCHAR 0 20 "|" 0 dummy13 SQLCHAR 0 20 "|" 0 dummy14 SQLCHAR 0 20 "|" 12 CLOSDATE15 SQLCHAR 0 20 "|" 13 CLC_CODE16 SQLCHAR 0 20 "|" 14 M_CONDITION_017617 SQLCHAR 0 20 "|" 15 M_CORR_ID18 SQLCHAR 0 20 "|" 16 M_CORR_NAME19 SQLCHAR 0 20 "|" 17 M_AGENT_ID20 SQLCHAR 0 20 "|" 18 M_AGENT_NAME21 SQLCHAR 0 20 "|" 19 M_ACCT_EX22 SQLCHAR 0 20 "|" 20 M_REGION_MGR23 SQLCHAR 0 20 "|" 21 M_CLOSED_PKG_REC_DATE24 SQLCHAR 0 20 "|" 22 M_REVIEWER_NAME25 SQLCHAR 0 20 "|" 23 M_SOMD_DOCS_YES26 SQLCHAR 0 20 "|" 24 M_SOMD_DOCS_NO27 SQLCHAR 0 20 "|" 25 M_SENT_TO28 SQLCHAR 0 20 "\r\n" 0 dummy Also try \r by itself, then \n by itself, in case the \r\n combination doesn't work. If the data file came from a Unix/Linux source then \r\n definitely WON'T work properly. |
 |
|
|
MuadDBA
628 Posts |
Posted - 2002-10-29 : 15:33:09
|
| Nope, \r\n doesn't work, neither does just \r or \n....here's a sample of the data (scrubbed a little)(cut and paste to notepad to view without wraps). I am thinking it's because I am telling it to skip rows in the middle of the data file, but I seem to remember doing that before and getting it to work. UGH! {ps thanks for the help}0111111111|ENDL|1|101|1||N||2|OH|3|||01-JUL-02|||10823|RANDALL MORTGAGE SERVICE, INC.|1041008|CHELSEA TITLE|15|31|05-JUL-02||N|Y||0222222222|ENDL|1|100|1||N||2|NC|3|||10-JUL-02|||17007|UNITED COMMUNITY BANK|267831|KIMBERLY R. COWARD|161|30|16-JUL-02||Y|N|8| |
|
|
|
borstalboy
Starting Member
4 Posts |
Posted - 2002-10-29 : 15:38:25
|
| I sympathize - I've found bcp so frustrating and irrational to use that I keep my own log of weird bcp quirks.Anyway, in the past I've had this error if the last line of the format file is also the end of file. If the end of your file is actually on the last line, add a CRLF by hitting Enter, and try that. |
|
|
|
MuadDBA
628 Posts |
Posted - 2002-10-29 : 15:50:36
|
| HOLY CRAP!That was the problem, no LF at the end of the format file. I can't beleive it was that simple. Thanks so much. |
|
|
|
borstalboy
Starting Member
4 Posts |
Posted - 2002-10-29 : 17:14:23
|
| That's great!For what it's worth, here are some other points that are good to remember when dealing with this whacked-out problem child of a utility:„h Sometimes when you submit a bcp command through a .cmd command file, from the command prompt, nothing happens. Hit CTRL+Z, and enter.„h The same error message ¡§Bcp: Unable to open host data-file.¡¨ is used if bcp cannot find either the format file, or the input data file„h The error SQLState = 22001, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]String data, right truncationusually means there¡¦s a mismatch between the coding in the .fmt file and the target table. For instance, the order of the columns in the .fmt file doesn¡¦t exactly match the order of the columns as defined in the table.„h In the format file, the end of file must at least be on the row after the last entry, or you get an I/O Read error„h One possible reason for the error ¡§BCP Copy failed¡¨ is that you are not populating a field that cannot be NULL„h If the input data file contains fewer columns than the output table, code a format file entry for every row in the table anyway. Code a zero for the prefix, zero for the length, an empty string for the terminator, and a zero for the output field number„h When testing a new bcp, check every output column carefully. The bcp can appear to run correctly, but still produce unexpected results for individual columns (see next bullet point)„h The 6.5 and 7.0 versions of the utility can give different results. One example was of an input text file with two date fields. Version 6.5 wrote NULLs to SQL Server, version 7.0 wrote out the dates correctly. Both versions had ended cleanly„h Books Online contains reasonably good documentation on using format files with bcp. Search for ¡¥bcp utility¡¦, then click on the sub-heading ¡¥format file¡¦„h Hidden characters in ASCII files can lead to an ¡§unexpected null found¡¨ error message. Text editors can often display hidden characters„h To test a new bcp and load only the first n input records, code the ¡VL parameter. The format is ¡VL nnnn, where nnnn is the number of records to load„h The user specified on the bcp command must have at least SELECT permission on the target database. (And maybe UPDATE, INSERT, and DELETE, too.)„h To start processing at a specific row number, use the parameter ¡VF [first row no] |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-29 : 17:32:59
|
| Ummmmm, if you copied and pasted these tips from a file of some kind, you might want to check ITS format...there's a lot of screwy characters in your post. |
|
|
|
borstalboy
Starting Member
4 Posts |
Posted - 2002-10-30 : 09:53:31
|
| There's a lot of screwy characters in my past, too. Come to think of it, some of 'em are still there... |
|
|
|
|
|
|
|
|
Format Files driving me crazy!