Greetings! I hope some can help with out with this. I am trying to write a scripting using bcp to import large amounts of data from a text file that is tab delimited. The problems I have run into is that when bcp does import the from the file, it will only import 8 lines, then stop. Opening the text file reveals that it contains characters like / \ , ' " . and all sorts of messy things. Looking at the table after importing the 8 lines also shows that the table is not going into the rights places. For example. If I had the phrase "The quick brow fox jumped over the lazy dog" Assume that every two words should go into a column. ie. "the quick" "brown fox" "jumped over" "the lazy" "dog" It would produce. "the qu" "ick brown fox" "jump" "ed ov" "er the lazy dog" instead. Due to the nature of the information I cannot give exact examples. So my question is. What am I doing wrong? Is there anyway escape those characters without editing the text file? Other people outside of our company create the files and won't be willing to change how the do things. Any ideas will be appreciated. Thanks.
-
-
Check the structure of your .FMT (format) file that you are using. If the structure is too small, it will definitely spill over to the next field. Also, try and check your text qualifier. Try uploading it using the Import Wizard first and see how the sql server treat each field.
-- CK -
I've tried to use the import/export wizard and here are my results. When using the wizard to import the data I have columns that don't have anything in them and the data that should go in them is in the next column. This happens at several places. I can try to add filler columns but then I have extra columns at the end and it doesn't like that either. Help!Comment
-
Do you have a header?
-- CKComment
-
sorry it keeps taking me so long to reply, for some reason I'm wasn't getting notification that there are new messages (I am now). Anyway, yes I do have headers. I have since spoken (again) with the tech guys on their end of things, and they (the company) have changed us over to a mixed delimited file. Now the file is both tabbed and | (piped) delimited. Confused yet? It gets better. After they switched us over, I have setup new columns with new headers and the new file. It still doesn't work. I have a feeling this is their problem and not mine. Viewing the text file shows that while some rows have 20-30 pipe separated columns the row below may only have one or none at all. I don't think the company realizes what this does to the people trying to get this to work.Comment
-
Thanks for the help you have given me. I have, for the moment come up with a work around for my problem. I have decided to leave all of the pipe columns in one column instead, and use substring in whatever programming language we decide to use give us our individual values. Our next problem will be making a page load quickly while having to process several if then statements.Comment
Comment