Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

External table from Windowsly exported CSV

nferreriFeb 17 2010 — edited Sep 7 2010

Hey guys -

I'm having a problem using a CSV as the source for an external table due to the CRLF at the end of the line put on by windows.

Normally I would use the RECORDS DELIMITED BY NEWLINE parameter on the table but apparently the last field is putting a CR at the end. I assume this is because Unix format for NEWLINE only looks at LF so it assumes CR is part of the field.

I have remedied this temporarily by replacing CHR(13) on the field but I would ideally like to have the table structure handle these characters properly.

I tried doing as much googling as I could and I see you can specify the RECORDS DELIMITED BY with HEX by using 0x'0a' but I don't really see any cases that would use 0D0A as the delimiter... I'm not really sure how to have it use both characters as the delimiter so it doesn't stick the CR at the end of the last field.

Any help would be appreciated!

Ohh also... can someone tell me what the proper way to specify hex characters is? I've seen:

FIELDS TERMINATED BY x'2c'

and

RECORDS DELIMITED BY 0x'0A'

I've used x'HEX # HERE' but I'm not sure what the 0 in front of the x is doing...

Thanks!