Hi Tom,
Does the data contain any apostrophes? That would fail under the single
quote scenario.
Brent Skean
Current Solutions
-----Original Message-----
From: Tom Grimshaw <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Monday, March 18, 2002 11:45 PM
Subject: Import Failures and other brick walls
>G'day,
>
>I have a client wanting to import data into BizMan.
>I'm _sure_ I had the following code working in 6.1
>but no cigar in 6.5++.
>
>1. If you can spot an error in it, please let me know
>as it crashes and burns with an "Incorrect number of
>values for the table" message on single quotes but
>works fine with double quotes.
>
>2. As an aside, do you know how to save an Excel
>spreadsheet into a CSV with double quotes round the text?
>
>3. SET VAR vErrorTest = .SQLCODE following the --** does
>not retrieve an other than 0 error, probably due to the
>prior command not being an SQL command. What do you
>use here?
>
>Thanks in advance...
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>$COMMAND
>LoadData
>-- Loads data from file into selected table
>-- Called by:
>-- ImptAdr2 in DataXfer.apx
>-- ImptCD2 in DataXfer.apx
>-- ImptJob2 in DataXfer.apx
>-- ImptOCD2 in DataXfer.apx
>-- ImptOCN2 in DataXfer.apx
>-- ImptPCD2 in DataXfer.apx
>-- ImptPCN2 in DataXfer.apx
>-- ImptPsn2 in DataXfer.apx
>-- ImptPS2 in DataXfer.apx
>*( Requires var
>vFileName
>vTableName
> Returns var - none
>)
>-- Created by Tom Grimshaw 05-03-2001
>-- Modification history
>-- 18-04-2001 tlg Modified setting of QUOTES and usage of & vars
>-- 20-04-2001 tlg added option to choose between LOAD and GATEWAY
>--
>
>SET QUOTES=NULL
>SET QUOTES='
>SET DELIMIT=NULL
>SET DELIMIT=','
>SET LINEEND=NULL
>SET LINEEND='�'
>SET SEMI=NULL
>SET SEMI=';'
>SET PLUS=NULL
>SET PLUS='+'
>SET SINGLE=NULL
>SET SINGLE='_'
>SET MANY=NULL
>SET MANY='%'
>SET IDQUOTES=NULL
>SET IDQUOTES='`'
>SET CURRENCY '$' PREF 2 B
>SET CASE OFF
>SET AUTOSKIP ON
>SET REVERSE ON
>SET BELL OFF
>SET NULL -0-
>SET DATE YEAR 50
>SET DATE CENTURY 19
>SET DATE SEQUENCE DDMMYYYY
>SET TIME SEQUENCE HHMMSS
>SET TOLERANCE 0.
>SET ZERO ON
>SET RULES OFF
>SET VAR vScreenMsg TEXT = 'You can choose to import data with minimal+
> quoting around text strings (quotes only around strings that have a
comma+
> in them - such as that exported from a spreadsheet) or quotes around all+
> text strings.|+
>Click [Yes] to import from a file with minimal quoting,|+
>Click [No] to import from a file with full quoting,|+
>Press [Esc] to quit:'
>SET VAR vScrnMsgChoice TEXT = 'Q'
>RUN SM6YesNo IN GlobBlok.apx
>IF vScrnMsgChoice = 'Q' THEN
> RETURN
>ENDIF
>IF vScrnMsgChoice = 'Y' THEN
> SET VAR vImportCmd TEXT = +
>('GATEWAY IMPORT CSV ' + .vFileName + ' APPEND ' + .vTableName)
>ENDIF
>IF vScrnMsgChoice = 'N' THEN
> SET VAR vImportCmd TEXT = +
>('LOAD ' + .vTableName + ' FROM ' + .vFileName + ' AS ASCII')
>*( Tried LOAD &vTableName FROM &vFileName AS ASCII
>but returned error message vTableName FROM illegal table name)
>ENDIF
>
>-- Select text quoting character
>
>LABEL LBegQChr
>SET VAR vScreenMsg TEXT = 'You need to specify the character used to+
> surround text strings and date values.|+
>Click [Yes] to set it to something other than single quotes,|+
>Click [No] or press [Esc] to leave it as a single quote:'
>SET VAR vScrnMsgChoice TEXT = 'N'
>RUN SM4YesNo IN GlobBlok.apx
>IF vScrnMsgChoice = 'Y' THEN
> SET VAR vQuoteChar TEXT = NULL
> DIALOG 'Enter the character:' vQuoteChar=1 vEndKey 1
> IF vQuoteChar IS NULL THEN
> PAUSE 1 USING 'No character entered.' AT CENTER CENTER
> GOTO LBegQChr
> ENDIF
> IF vQuoteChar IN
('a','b','c','d','e','f','g','h','i','j','k','l','m','n',+
> 'o','p','q','r','s','t','u','v','w','x','y','z','1','2','3',+
> '4','5','6','7','8','9','0','.') THEN
> PAUSE 1 USING 'Ilegal character entered.' AT CENTER CENTER
> GOTO LBegQChr
> ENDIF
> IF vQuoteChar IN ('\','/','!','@','#','$','(',')','=',':') THEN
> PAUSE 1 USING 'That character not recommended.' AT CENTER CENTER
> GOTO LBegQChr
> ENDIF
> SET VAR vCmd3 TEXT = ('SET QUOTES=' + .vQuoteChar)
> &vCmd3
>ENDIF
>&vImportCmd
>--**
>SET VAR vErrorTest = .SQLCODE
>SET QUOTES=NULL
>SET QUOTES='
>IF vErrorTest = 0 THEN
> SET VAR vScreenMsg TEXT = 'Data loading appears to have succeeded.|+
>Click [Yes] to view data in table format,|+
>Click [No] or press [Esc] to quit:'
> SET VAR vScrnMsgChoice TEXT = 'N'
> SET VAR vScreenMsg TEXT = (SRPL(.vScreenMsg,'|',(CHAR(13) +
CHAR(10)),0))
> SET VAR MICRORIM_NOSTATUS INTEGER
> EDIT USING ScrnMsgYesNo3 +
> AT 35,20,762,146 +
> CAPTION 'Message'
> CLEAR VAR MICRORIM_NOSTATUS
> IF vScrnMsgChoice = 'Y' THEN
> BROWSE * FROM &vTableName
> ENDIF
>ELSE
> PAUSE 1 USING 'There was a problem with data loading.' AT CENTER CENTER
>ENDIF
>CLEAR VAR vCmd,vCmd2,vImportCmd
>SET RULES ON
>RETURN
>
>
>
>Warmest regards,
>
>
>Tom Grimshaw
>coy: Just For You Software
>tel: 612 9552 3311
>fax: 612 9566 2164
>mobile: 0414 675 903
>
>post: PO Box 470 Glebe NSW 2037 Australia
>street: 3/66 Wentworth Park Rd Glebe NSW 2037
>
>email: [EMAIL PROTECTED]
>web: www.just4usoftware.com.au
>
>This email and any files transmitted with it are confidential to the
>intended recipient and may be privileged. If you have received this email
>inadvertently or you are not the intended recipient, you may not
>disseminate, distribute, copy or in any way rely on it. Further, you should
>notify the sender immediately and delete the email from your computer.
>Whilst we have taken precautions to alert us to the presence of computer
>viruses, we cannot guarantee that this email and any files transmitted with
>it are free from such viruses.
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
