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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-09 : 10:22:41
|
| Bob writes "Hello SQLTeam!Got a question for ya. I'm trying to determine the best way to get rid of a trailing delimiter from each row of a text file prior to inserting it into my SQL 7 database.Here's a example of how the data is formatted:EmployeeID|FirstName|LastName|BirthDate|HireDate|OfficePhone|When I first started loading this file via the DTS wizard everything was peachy. Why change something that works? Well at the time the import duration didn't seem to be much of an issue because of it's size. Now, however, it's grown to several hundred thousand records and is taking 3-4 minutes to load.To speed things up, I've been playing with BCP. I've got it to load -- finally -- and considerably faster (less than 30 seconds). Unfortunately, in my OfficePhone column (only an example mind you), it's loading the data with the vertical bar tacked onto the end instead of omitting it.I could write a query to delete the "|", but I'm sure there's a better way, I just haven't found it yet.My row terminator in the BCP script is a CRLF. It also seems to work if I just do a LF. Ideally, I'd just specify the terminator as |CRLF, but unfortunately, it doesn't seem to want to work. Whenever I throw the pipe in, it bombs.The way I see it I need to do one of two things: First, either write a script to manipulate the text file to basically do a "left(rowvariable, len(rowvariable) -1)" for each of the several hundred thousand records in the file, or, find a way to make the "|" part of the row delimiter (my preference). What do you think?Thanks,Bob" |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-05-09 : 22:03:43
|
quote:
I'm sure there's a better way,
Oops - sorry - saw the word BCP and had sudden onset of heart failure....open mouth...change feet...close mouth Edited by - rrb on 05/09/2002 22:14:36 |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-05-09 : 22:07:23
|
| Bob,How where your loading the data through DTS..Please don't tell me you where doing it via a Transform!Have you looked at the BulkInsert Method. It comes with a nice little wizard to set up your Format File.It is BCP in disguise...HTHDavidM"SQL-3 is an abomination.." |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-09 : 22:14:13
|
This should make you chuckle, if you look at the entry in Books Online for BULK INSERT, they have an example that uses the |\n row terminator  that's identical to what you have tried already. I tested it using BULK INSERT and it worked fine; I think that format should work with bcp too. You may need to surround it with double quotes (") on the bcp command line, like this:bcp table in c:\myfile.text -r"|\n" -t"|" ...etc. etc.If not, can you add an extra column at the end of your table, say a varchar(1) column? Since there is no data in the input file it will simply add a null value to every imported row, and won't take up any extra space for that. You'd use a regular, single pipe as column delimter and a regular CRLF (\n) as row terminator. Not ideal, but if all else fails...Edited by - robvolk on 05/09/2002 22:37:54 |
|
|
|
|
|
|
|
|
Trailing Delimiter Needs to Go Away