Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: