You have single quotes in your data that you are importing.
To get around this I have always used a ^ symbol as my quote symbol
rather than single or double quotes, when doing this type of task. It is
unlikely that you users will use this symbol on a daily basis. That way
your users can put anything they want in the data and you don't have to
deal with it.
Fred Antrobus
On Tue, 2002-03-19 at 18:13, Tom Grimshaw wrote:
> 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/
>
--
-- Fred Antrobus
-- [EMAIL PROTECTED]
-- (425)235-7796
================================================
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/
