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/
