RE: SQLLDR - whitespace (tab & space) problem
Great! That works too. Now I can do it (modify the data) from either Shell script or control file. Thanks for all the input. Saj Raza <[EMAIL PROTECTED]> wrote: Try something like the lines below, as 9 is the ASCII code for a TAB :load datainfile *INTO TABLE experiment_with_tab_delimiterFIELDS TERMINATED BY X'9'(textcol1, textcol2)BEGINDATASaj-Original Message-From: Helen Zhung [SMTP:[EMAIL PROTECTED]]Sent: Friday, April 27, 2001 7:40 PMTo: Multiple recipients of list ORACLE-LSubject: SQLLDR - whitespace (tab & space) problemHello: I'm new to SQL Loader. Is there a way to code in control file thatcan separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE afield when there is a space or a tab. However, the data fields wereseparated only by the "tab", not 'a space'. Is it possible to identify thetwo? Thanks _ Do You Yahoo!?Yahoo! Auctions ! ! - buy the things youwant at great prices**This email and any files transmitted with it are confidential andintended solely for the use of the individual or entity to whom theyare addressed. If you have received this email in error please notifythe system manager.This footnote also confirms that this email message has been swept byMIMEsweeper for the presence of computer viruses.www.mimesweeper.com**-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Saj RazaINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mai! ! ling list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices
RE: SQLLDR - whitespace (tab & space) problem
Try something like the lines below, as 9 is the ASCII code for a TAB : load data infile * INTO TABLE experiment_with_tab_delimiter FIELDS TERMINATED BY X'9' (textcol1, textcol2) BEGINDATA Saj -Original Message- From: Helen Zhung [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 27, 2001 7:40 PM To: Multiple recipients of list ORACLE-L Subject: SQLLDR - whitespace (tab & space) problem Hello: I'm new to SQL Loader. Is there a way to code in control file that can separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE a field when there is a space or a tab. However, the data fields were separated only by the "tab", not 'a space'. Is it possible to identify the two? Thanks _ Do You Yahoo!? Yahoo! Auctions <http://auctions.yahoo.com/> - buy the things you want at great prices ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saj Raza INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLLDR - whitespace (tab & space) problem
You can no doubt do this with Sql*loader. Personally, I like to clean up the data first instead. I find it easier to work with that way. These task are easy on unix, and not too bad on windoze if you get some decent text tools. The following line for instance will change all tab characters to a space, then compress all consecutive spaces to a single space. tr "\011" " " < dirty.txt | tr -s " " >| clean.txt You can even send the output to a pipe, and read the pipe directly from sqlloader mkfifo loader_pipe tr "\011" " " < test.txt | tr -s " " > loader_pipe & sqlloader ... data=loader_pipe ... Jared On Friday 27 April 2001 11:40, Helen Zhung wrote: > Hello: > I'm new to SQL Loader. Is there a way to code in control file that can > separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY > WHITESPACE", but it will TERMINATE a field when there is a space or a tab. > However, the data fields were separated only by the "tab", not 'a space'. > Is it possible to identify the two? Thanks > > > > > - > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great prices Content-Type: text/html; charset="us-ascii"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLLDR - whitespace (tab & space) problem
Helen, You can use the "optionally" parameter to specify multiple enclosed by/terminated by parameters. NULLIF is also available to assist in loading blanks. Regards, David A. Barbour Oracle DBA, OCP Helen Zhung wrote: > > Hello: > > I'm new to SQL Loader. Is there a way to code in control file that can > separate 'a tab' and 'a blank space' ? > > I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE a field > when there is a space or a tab. However, the data fields were > separated only by the "tab", not 'a space'. Is it possible to identify > the two? > > Thanks > > > > -- > Do You Yahoo!? > Yahoo! Auctions - buy the things you want at great prices -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQLLDR - whitespace (tab & space) problem
Hello: I'm new to SQL Loader. Is there a way to code in control file that can separate 'a tab' and 'a blank space' ? I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE a field when there is a space or a tab. However, the data fields were separated only by the "tab", not 'a space'. Is it possible to identify the two? Thanks Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices
