hi All,
how open excel file and read cell information in excel file?
please help me.
thanks.

Please be a little more specific. Do you want to open Excel with a certain file for the user, or do you want your Baan program to read certain data from an Excel file in the background? Both are easy to achieve. Please specify your requirements and we will give you suggestions...

Regards,
Eli Nager

If you keep file in csv format then you can easily read from baan by using file operators. But I don't think that baan program directly read file in xls format.
May be there are some possibility like using macros.


Good Luck !

hi Eli Nager,
i want to open CSV file under fix address and read CSV information via baantools program.
can you explain find and open CSV file syntax format?I need to design program from baantools,Thanks.

In the following script i show you this situation:

the file contains two fields, ";"-seperated (.csv):
field1: business partner
field2: business partner name



|******************************************************************************
|* Script Type: 3GL (without stnd. program)
|******************************************************************************

|****************************** DECLARATION SECTION ***************************

table ttccom100 | Business partners

extern domain tcmcs.long ret
extern domain tcmcs.long fileid
extern domain tcmcs.long count

extern domain tccom.bpid bpid
extern domain tcnama nama
string curr.line(40)


#define PATH "${BSE}/tmp/customers.csv"
#define TR(x) strip$(shiftl$(x))


function main()
{
ret = 0
count = 0

fileid = seq.open(PATH, "rt")

if fileid < 0 then
message("Can not open file")
else
db.retry.point()

while ret = 0
ret = seq.gets(curr.line, 40, fileid)

string.scan(curr.line, "%s;%s", bpid, nama)

bpid = TR(bpid)
nama = TR(nama)

select tccom100
from tccom100 for update
where tccom100._index1 = {:bpid}
order by tccom100._index1
as set with 1 rows
selectdo
tccom100.nama = nama
db.update(ttccom100, db.retry)
selectempty
db.set.to.default(ttccom100)
tccom100.bpid = bpid
tccom100.nama = nama
tccom100.seak = nama
etc... fill all fields
db.insert(ttccom100, db.retry)
endselect

count = count + 1

if count >= 250 then
count = 0
commit.transaction()
endif
endwhile

if count > 0 then
commit.transaction()
endif
endif
}

|****************************** PROGRAM SECTION ***************************
|****************************** ZOOM FROM SECTION ***************************
|****************************** FORM SECTION ***************************
|****************************** CHOICE SECTION ***************************
|****************************** FIELD SECTION ***************************
|****************************** MAIN TABLE SECTION ***************************
|****************************** FUNCTION SECTION ***************************

U can you this as examples.
It's working well here

thanks,if i want open no format file,i try to use under program,but find description and material group fields cannot add to ttcibd001 table.

no format file:tcibd001
content:
7701,0.020,101
7702,0.100,201

first field:item code
second field:item description
third field:item group


program script:
|******************************************************************************
|* Script Type: 4
|******************************************************************************

|****************************** declaration section ***************************
declaration:

| table ttcmcs900 | Areas

table ttcibd001 | Material

extern domain tcitem item.f
extern domain tcitem item.t

extern domain tcmcs.long fileid

extern domain tcmcs.long count

extern domain tcitem item
extern domain tcdsca dsca
extern domain tccitg citg

extern domain tcnama nama
extern domain tcpono n

string curr.line(40)
string error(100)

long ret

#define PATH "c:\baan\lhm_tmp\tcibd001"
#define TR(x) strip$(shiftl$(x))
#pragma used dll ottstpapihand



|****************************** form section **********************************

form.1:
init.form:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
read.main.table()

|****************************** field section *********************************
|****************************** function section ******************************

functions:

function read.main.table()
{
count = 0
fileid = seq.open(PATH, "rt")

if fileid < 0 then
message("Can not open file")
else
|db.retry.point()

while count = 0
count = seq.gets(curr.line, 40, fileid)

string.scan(curr.line, "%s;%s;%s", item, dsca, citg)

item = TR(item)
dsca = TR(dsca)
citg = TR(citg)
message("%s",item) | dialog show item code
message("%s",dsca) | dialog show null
message("%s",citg) | dialog show null


stpapi.synchronize.dialog("tcibd0501m000", "add", error)
stpapi.put.field( "tcibd0101s000", "tcibd001.item.segment.2", item)
stpapi.put.field( "tcibd0101s000", "tcibd001.dsca", dsca )
stpapi.put.field( "tcibd0101s000", "tcibd001.citg", citg )


ret=stpapi.insert( "tcibd0501m000",1, error )

if ret=0 then
message(error)
else
n = n+1
count = count + 1
if count >= 250 then
count = 0
commit.transaction()
endif
endif

display("item")
display("n")
endwhile

if count > 0 then
commit.transaction()
endif
endif



}

please tell to me program script error,thanks.

thanks,if i want open no format file,i try to use under program,but find description and material group fields cann't add to ttcibd001 table.

no format file:tcibd001
content:
7701,0.020,101
7702,0.100,201

first field:item code
second field:item description
third field:item group


program script:
|******************************************************************************
|* Script Type: 4
|******************************************************************************

|****************************** declaration section ***************************
declaration:

| table ttcmcs900 | Areas

table ttcibd001 | Material

extern domain tcitem item.f
extern domain tcitem item.t

extern domain tcmcs.long fileid

extern domain tcmcs.long count

extern domain tcitem item
extern domain tcdsca dsca
extern domain tccitg citg

extern domain tcnama nama
extern domain tcpono n

string curr.line(40)
string error(100)

long ret

#define PATH "c:\baan\lhm_tmp\tcibd001"
#define TR(x) strip$(shiftl$(x))
#pragma used dll ottstpapihand



|****************************** form section **********************************

form.1:
init.form:
get.screen.defaults()

|****************************** choice section ********************************

choice.cont.process:
on.choice:
read.main.table()

|****************************** field section *********************************
|****************************** function section ******************************

functions:

function read.main.table()
{
count = 0
fileid = seq.open(PATH, "rt")

if fileid < 0 then
message("Can not open file")
else
|db.retry.point()

while count = 0
count = seq.gets(curr.line, 40, fileid)

string.scan(curr.line, "%s;%s;%s", item, dsca, citg)

item = TR(item)
dsca = TR(dsca)
citg = TR(citg)
message("%s",item) | dialog show item code
message("%s",dsca) | dialog show null
message("%s",citg) | dialog show null


stpapi.synchronize.dialog("tcibd0501m000", "add", error)
stpapi.put.field( "tcibd0101s000", "tcibd001.item.segment.2", item)
stpapi.put.field( "tcibd0101s000", "tcibd001.dsca", dsca )
stpapi.put.field( "tcibd0101s000", "tcibd001.citg", citg )


ret=stpapi.insert( "tcibd0501m000",1, error )

if ret=0 then
message(error)
else
n = n+1
count = count + 1
if count >= 250 then
count = 0
commit.transaction()
endif
endif

display("item")
display("n")
endwhile

if count > 0 then
commit.transaction()
endif
endif



}

please tell to me program script error,thanks.

Hello,

in file you use "," as seperator, in string.scan it's ";" !

! "," and ";" are bad fieldseparators, because it's often used in strings.
I suggest to use a seperator like "|".


Regards

bdittmar is right.
Change that and tell us if it works.

yes,bdittmar is right,thanks.