Preferred delimit methods

In your answer about connecting SQL Server 6.5 to Oracle9i you had responded, in part, "Otherwise, you can dump...

the SQL Server data to comma-delimited files and load them into Oracle with SQL*Loader." Using commas is probably one of the worst choices. Consider the following data stored in a table:

FName    Lname    Salary
Jane     Doe      5000
John     Doe, Jr  5000

The rows would dump as follows:

John,Doe, Jr, 5000

The first row would load properly, the second would not.

I prefer to use the | or ~ symbols to delimit.
Then what if your data has the '|' or the '~' symbol? You'd have to use a different symbol and hope that your data doesn't contain that symbol.

To aid in this quest (although it is still not perfect), when you extract data to a flat file using MS Data Transformation Services (DTS), you can make it comma delimited and select to optionally enclose each field in double quotes. So your example would look like the following:

"John","Doe, Jr", 5000

In this case, the number fields don't use the double quotes as they are numbers, not strings. Notice that there are only three fields, even in the second line! The second field of the second line is "Doe, Jr". The comma in that line will be part of the string, not used to delimit the next column.

And if that still does not satisfy the problem, you can always use tab-delimited fields. In either case, Oracle's SQL*Loader (and DTS) support comma delimited files, comma delimited files with optional field characters, and tab delimited files.

This was first published in March 2004

Dig Deeper



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: