FName Lname Salary Jane Doe 5000 John Doe, Jr 5000 The rows would dump as follows: Jane,Doe,5000 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:
"Jane","Doe",5000
"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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation