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 |
|
edb2003
Yak Posting Veteran
66 Posts |
 Posted - 2003-09-26 : 18:23:36
|
| Happy Friday to everyone, I have made a container in SQL Server. I have a Fixed width tab text file to port into SQL server which I got to work using BCP.When I look at the table I see something that looks like thisCol1 Col2 Col3 Col4 Col51 Happy Wed to You3 Happy Fri 2 HappyWhen it should look like this1 Happy Wed to You2 Happy Thu3 Happy Fri to YouThere was no information to the remaining fields in record 2 and therefore I noticed a Non-Displaying Escape Sequence in the text file.I figured out that if I add spaces to complete each of the missing characters in the blank fields, I CAN run my stored procedure and the files populate into the two tables I want it to be inserted. Otherwise, if I do not do it, I get a wrap around the next record as noted in display 1 above.MY PROBLEMHow do I run a search and replace in a fixed width text file having 50,000 records that have the Non Display Escape Sequence after the col2 'Thu' to have spaces to cover the remaining blank areas? Can I do this in BCP? Please note: The text file is outside of SQL with this hidden Escape funtion.I appreciate your help :)Thanks bunchesEd |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-26 : 19:11:54
|
| What is the format of the file?Sounds like you are specifying tab delimitted whereas this is not the case in all rows. bcp will just take the next row to fill in the missing data.If it has crlf or some other sequence at the end of each row then you should be able bcp it into a single column table specifying something that doesn't exist in the file as a column delimitter. You can then split the row up into columns in an SP.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
edb2003
Yak Posting Veteran
66 Posts |
Posted - 2003-09-26 : 20:12:45
|
| Thanks NR It uses Fixed Width format.The big problem I have has to do with this is to find a way to automate a search and replace. I need to search all data in my text file that has 'thur' and after it add spaces to fill up the characters that add up to fill the remaining 2 columns. I ran BCP with spaces and it works fine with spaces. My problem - How can I automate a search and replace to a text file prior to running BCP?I appreciate everyones help :)edb |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-27 : 00:23:49
|
| Don't. Import it then do the replace. With a fixed width file it's easier because you can just use substring and replace. With just 50,000 records performance shouldn't be a problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Non Display Escape Sequence Problem