This is some simple code from one of my projects for importing XL.
The aNVL function is just a wrapper to NVL to check for null values.
oXL=Createobject("Excel.Application")
oWB=oXL.Workbooks.Open(cfilename)
oWB.Sheets("Sheet1").Select()
oWS=oWB.ActiveSheet
nMax=0
nrow=0
For Each oRow In oWS.Rows
If Len(Trim(aNVL(oRow.cells[1,1].Value,"")))=0 And
Len(Trim(aNVL(oRow.cells[1,2].Value,"")))=0 And
Len(Trim(aNVL(oRow.cells[1,3].Value,"")))=0
Exit
Endif
If lAction
Else
m.pr_code=aNVL(oRow.cells[1,25].Value,"")
* m.rn=aNVL(oRow.cells[1,26].Value,"0")
* If Type("m.rn")="C"
* m.rn=Val(m.rn)
* Endif
If Len(Trim(m.pr_code))>0
Thisform.odataenvironment.Product.cSelectCMDFilter="pr_code='"+m.pr_code+"'"
Thisform.odataenvironment.Product.CursorFill()
Else
Thisform.odataenvironment.Product.cSelectCMDFilter="f_rn=-1"
Thisform.odataenvironment.Product.CursorFill()
Endif
If Eof()
Thisform.odataenvironment.Product.cSelectCMDFilter="upper(pr_manu)='"+Upper(Alltrim(aNVL(oRow.cells[1,1].Value,"")))+"'
and UPPER(pr_model)='"+Upper(Alltrim(aNVL(oRow.cells[1,2].Value,"")))+"'"
Thisform.odataenvironment.Product.CursorFill()
Locate For ;
UPPER(Alltrim(pr_manu))==Upper(aNVL(oRow.cells[1,1].Value,"")) And ;
UPPER(Alltrim(pr_model))==Upper(Alltrim(aNVL(oRow.cells[1,2].Value,"")))
And ;
ALLTRIM(pr_engine)==Alltrim(aNVL(oRow.cells[1,3].Value,"")) And ;
ALLTRIM(pr_enginespec)==Alltrim(aNVL(oRow.cells[1,4].Value,"")) And ;
ALLTRIM(pr_engine_code)==Alltrim(aNVL(oRow.cells[1,5].Value,"")) And ;
ALLTRIM(pr_year_from) == Alltrim(aNVL(oRow.cells[1,6].Value,"")) And ;
ALLTRIM(pr_year_to) == Alltrim(aNVL(oRow.cells[1,7].Value,"")) And ;
ALLTRIM(pr_veh_part) == Alltrim(aNVL(oRow.cells[1,8].Value,"")) And ;
ALLTRIM(pr_orig_manu) == Alltrim(aNVL(oRow.cells[1,9].Value,"")) And ;
ALLTRIM(pr_category)==Alltrim(aNVL(oRow.cells[1,10].Value,"")) And ;
ALLTRIM(pr_system_type)==Alltrim(aNVL(oRow.cells[1,11].Value,"")) And ;
ALLTRIM(pr_manu_part) == Alltrim(aNVL(oRow.cells[1,12].Value,"")) And ;
ALLTRIM(pr_make) == Alltrim(aNVL(oRow.cells[1,14].Value,"")) And ;
ALLTRIM(pr_comPloc)== Alltrim(aNVL(oRow.cells[1,23].Value,""))
Endif
If Eof()
Append Blank
Endif
Replace pr_code With aNVL(oRow.cells[1,25].Value,"")
Replace pr_manu With aNVL(oRow.cells[1,1].Value,"")
Replace pr_model With aNVL(oRow.cells[1,2].Value,"")
Replace pr_engine With aNVL(oRow.cells[1,3].Text,"")
Replace pr_enginespec With aNVL(oRow.cells[1,4].Value,"")
Replace pr_engine_code With aNVL(oRow.cells[1,5].Value,"")
Replace pr_year_from With aNVL(oRow.cells[1,6].Text,"")
Replace pr_year_to With aNVL(oRow.cells[1,7].Text,"")
Replace pr_veh_part With aNVL(oRow.cells[1,8].Value,"")
Replace pr_orig_manu With aNVL(oRow.cells[1,9].Value,"")
Replace pr_category With aNVL(oRow.cells[1,10].Value,"")
Replace pr_system_type With aNVL(oRow.cells[1,11].Value,"")
Replace pr_manu_part With aNVL(oRow.cells[1,12].Value,"")
Replace pr_locn With aNVL(oRow.cells[1,13].Value,"")
Replace pr_make With aNVL(oRow.cells[1,14].Value,"")
** Replace pr_action With aNVL(oRow.cells[1,14].Value,"")
Replace pr_dead With "S"
cUrl=""
Try
If "=HYPERLINK" $ Upper(oRow.cells[1,11].formula)
cUrl=oRow.cells[1,11].formula
cUrl=Left(cUrl,At(",",cUrl)-1)
cUrl=Strtran(cUrl,"=hyperlink(","",1,999,1)
cUrl=Strtran(cUrl,["],"",1,999,1)
Else
cUrl=oRow.cells[1,11].hyperlinks(1).address
Endif
Catch
Finally
Endtry
cUrl1=""
Try
cUrl1=oRow.hyperlinks(1).address
Catch
Finally
Endtry
cUrl=aNVL(oRow.cells[1,24].Value,"")
If Type("cUrl1")="C"
If Len(Trim(cUrl1))>0
cUrl=cUrl1
Endif
Endif
Wait Window Alltrim(Str(Recno()))+Chr(13)+Chr(10)+"
"+Left(Alltrim(pr_veh_part)+Alltrim(pr_manu),50) Nowait Noclear
cPrice=aNVL(oRow.cells[1,15].Value,"")
If cPrice>="A" And cPrice<="Z"
cPrice="0"
Endif
cPrice=Val(cPrice)
Replace pr_cprice With cPrice
sPrice=aNVL(oRow.cells[1,16].Value,"")
If sPrice>="A" And sPrice<="Z"
sPrice="0"
Endif
sPrice=Val(sPrice)
Replace pr_sprice With sPrice
Replace pr_invcnt With Val(aNVL(oRow.cells[1,17].Value,0))
Replace pr_oncomm With Val(aNVL(oRow.cells[1,18].Value,0))
cDesc=aNVL(oRow.cells[1,19].Value,"")
If Isnull(cDesc)
cDesc=""
Endif
Replace pr_desc With cDesc
Replace pr_action With aNVL(oRow.cells[1,20].Value,"")
Replace pr_comment With aNVL(oRow.cells[1,21].Value,"")
Replace pr_fault With aNVL(oRow.cells[1,22].Value,"")
Replace pr_comPloc With aNVL(oRow.cells[1,23].Value,"")
If Alltrim(pr_category)==Alltrim(pr_system_type)
** SET STEP ON
Endif
Endif
If !Eof()
=mTableupdate()
Endif
Next
________________________________
From: ProFox on behalf of John Weller
Sent: Tue 12/02/2013 10:45
To: [email protected]
Subject: Import From Excel
I have some data in the form of an Excel spread sheet which I want to import
into a table. The problem is that one column has some entries that are more
than 254 characters. I want to import that into a memo field but APPEND
does not support appending into memo fields and APPEND MEMO appears to just
append one text file to one field.
Any suggestions? If all else fails I will split them by hand into 200
character blocks and then concatenate them once they are imported but as
there are about 300 rows I'd prefer to do it as a programme.
John Weller
01380 723235
07976 393631
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
