Dear All,

I have created new print session to print item information and 1 of the information required is tiitm001.item

Data : item code : 000111

if i am print to Decive : D(Display) or EXCEL => Item code displayed as per expected. Output : 000111

But i am facing issue when print to CSV format. Item code displayed as 111 which leading 000 is missing.

I have tried below commands but still not successful.

1) put.fld( str$( tiitm001.item )) or
2) put.fld( tiitm001.item ) or
3) put.fld( strip$( tiitm001.item ))

Perhaps you need to put double quote marks before and after the item code?

or add an ' to the value:

'000111 so excel treats this value as text.

Kozure

Dear EdHubbard,

If i added " before and after item code as below, the out will be displayed as word tiitm001.item

put.fld( str$( "tiitm001.item" )) or
2) put.fld( "tiitm001.item" ) or
3) put.fld( strip$( "tiitm001.item" ))

Dear Kozura,
I have added ' to the value by command below

Deli = "'"
itemCode = Deli & tiitm001.item
put.fld( itemCode )

The output in the excel will be '000111 and we can see the quote '. It little bit helpful but not acceptable to other system during export.

Hi,

First of all you need to follow certain coding conventions before creating the codes in Baan. The most important thing to be followed is, the codes should not start with 0.

Anyway, now that you have created them, I would suggest you to replace 0's with some character like * or @ or something like that. And you can tell the users to replace this character with 0 when CSV file is created.

Try this......
(it would be helpful only in the report while opening in excel , i am not really sure about the integration with other systems)

itemCode = "=" & quoted.string(tiitm001.item)

for integration just a suggestion
probably you can concat the previous field with item
i.e concat$("|",previous.field,item) {accordingly adjust import program of other system}

Dear Bhushan,
The BaanIV already implemented 10 years ago and lot of item already prefix with 0. We can change anymore since lot of integration with other system.

Dear Vamsi,
quoted.string() not available in BaanIV.

Hmm, I can just think of one more way to do it.

Replace 0's with some character say @. Now, you can use seq.* functions to open the output file and replace every @ with a 0.

So, if you are aware, a simple way would be:-

1. Create your .csv file using seq.open()
2. Write your data to it using seq.puts()
3. Close your file using seq.close()
4. Open it using seq.()
5. Find the @ character in the file and replace it with 0.
6. Close it using seq.close()

Use server2client function to transfer it to client machine. Try it. I hope it works.

In that case...... use ascii value for '"'(double quote)
Just re create this ( ="0011").....
One more thing.....when a CSV file is created.....the file does have prefixed zeros. But once you open it in excel the CSV is opened ....once you save the file....the excel auto converted it to numeric value......
Basically what I mean is when you create a file ,it does have preceded zeros......

*typo error...

Dear Bhushan,
The BaanIV already implemented 10 years ago and lot of item already prefix with 0. We can't change anymore since lot of integration with other system.

*typo error...



That is what we call loyalty! ;) Anyway, all the best with this. Post your final solution when you are done.

Dear All,

User has accepted the solution given since they need the report in csv format. There is no issue if they print in excel format.

Below are the code used.

code = tiitm001.item(1;1)
if code = "0" then
itemCode = itemDeli & tiitm001.item
put.fld( str$( itemCode ))
else
itemCode = tiitm001.item
put.fld( str$( itemCode ))
endif


the output will look like this

item code
'00011 <= those item which prefix with 0
11111 <= thos item which not prefix with 0

We actually wrote some macros to convert reports for the users - we have to use leading 0's also. We use the report name to figure out which macro to run and where to download the report. Not sophisticated, but works for us.

Hi Mark,

We actually wrote some macros to convert reports for the users - we have to use leading 0's also

Can you share with us?

Actually I think the script we use is in the code and utilties forum. Let me go check.

What we have is two tables tuddc905 and tuddc906, plus a device call PCEXCEL. Tuddc905 contains the reportname, site location, the download filename, macro to run and the macro location. Tuddc906 contains the site and the location of excel for that site. When we originally developed these each site that ran baan had different network drives, directories and desktop setups. At this point all sites have consolidated and are pretty much the same.

What the user does is runs a report we have a macro for, selects device PCEXCEL, and in the spool fileout name they enter the site. In most cases the site is ALL now - since all the macros are now stored in one location. The device PCEXCEL has our own write ttstpconv script called tuddcconv1(actually otuddcconv1 is the parameter) in the 4GL on the program. It only has ascii has the argument. So when the user selects PCEXCEL it runs this 3gl program - you can see other examples in the Code and Utilities forum. I pulled some of our old commented code out. We have had to tweak it from time to time with each desktop rollout. This should give you some ideas on how you could do something like this.

|******************************************************************************
|* tuddcdownload 0 VRC B40C c3 dev
|* Download a report to the PC.
|* hollandv
|* 08-24-99 [07:53]
|* Script provided by Kevin Brock and located on the www.baanfans.com
|* website.
|*
|******************************************************************************
|* Script Type: 4
|******************************************************************************

|****************************** DECLARATION SECTION ***************************
| Declare api - not needed, just testing.
#pragma used dll ottstpapihand
#pragma nowarnings
#include <bic_tt> |include tools functions

table ttuddc905 | Excel Macro Locations
table ttuddc906 | Excel locations by Site

function main()
{
| This session will run the standard program conversion session
| ttstpconv using a temporary output file and the arguments
| passed in 3+ verbatim.

| Arguments are passed from Baan spooler as follows:
| 1 = Name of input (temporary) file contain dev independent report
| 2 = Name of output file
| 3 = Additional arguments (from Device data)
| 4 = Specilar parm for ttstpconv (unknown reason)

string tmp.file(1024)
string local.path(1024)
string mdrive(1024) |20061025

| In this conversion program spool.fileout contains the site where
| the macro will be run from. This allows the user to input a lower
| case site. Site can be any three character field.
spool.fileout = toupper$(spool.fileout)
select tuddc905.*
from tuddc905
|where tuddc905.rprt = :spool.report
where tuddc905._index1 = {:spool.report,:spool.fileout}
selectdo
selectempty
message("Unable to locate report macro info.")
exit()
endselect

| Convert the report file
tmp.file = creat.tmp.file$( bse.tmp.dir$() )
wait.and.activate("ttstpconv", argv$(1), tmp.file, argv$(3),argv$(4))

| Set the path on the client's pc and send the file down
| local.path ="c:\baan\tmp\"&tuddc905.dnld
| 092602.st - Change to C:\program files\baan\tmp.
local.path ="C:\Program Files\Baan\tmp\"&tuddc905.dnld
| 092602.end - Change to C:\program files\baan\tmp.
mdrive = "m:\baantmp\"&tuddc905.dnld |20061025

send.report.to.client(tmp.file, strip$(local.path), strip$(mdrive)) |20061025
start_excel_with_macro()

| Remove our work file
seq.unlink(tmp.file)

}

|function send.report.to.client(const string src(), const string dest()) |20061025
function send.report.to.client(const string src(), const string dest(), const string dest2())
{
long err, dir

err = server2client(src, dest, 1)
if (err) then |20061025
dir = create.local.directory("m:\baantmp\") |20061025
err = server2client(src, dest2, 1) |20061025
if (err) then |20061025
message("Error %d copying file to PC (may not have permission)",
err)
endif |20061025
endif
}
function start_excel_with_macro()
{
string appl(132), parm(215),start(1024)
long app_id, rc
| Setup application to start and macro name

select tuddc906.*
from tuddc906
where tuddc906.site = :spool.fileout
as set with 1 rows
selectdo
endselect
appl = tuddc906.loca
|appl = "C:\program files\microsoft office\office\excel.exe"
parm = strip$(tuddc905.macl) & tuddc905.macr
start= appl&" "&parm
| Start the apllication
app_id = app_start(start,"","","","")

rc = app_status(app_id)
if(rc = 0) then
message("Application start failed.")
endif
}


PS - there are tools that will do this for you. Checkout http://www.nazdaq-it.com/.

Mark Is right ;). NAZDAQ (http://www.nazdaq-it.com/) has a bunch of tools, that many of them has Excel and CSV both can be done in jobs or regularly in front of a nice friendly interface like b2Win (http://www.nazdaq-it.com/products/b2win/).

Actually, i want to convert the xlsx/xls file to csv so that it can be read easily into baan.

So what i did, i wrote a VBS file which has this functionality and then wrote a batch file which goes to the specific location and then call the specific Batch file .

Now the problem is that i want somehow the Windows command line, which pops up to get supressed, like everything should happen in Background. right now it pops up and then it exits. I just want everything to be happening in background....

What is the command you are using that is popping up? I know for like the shell command on our platforms we can suppress the window from appearing.

Hi All,

There is a solution for current problem without code change(no need to append extra colon etc ) by using B2WIN.

In B2WIN, using BRC (B2WIN Report Customiser) one can set for particular report A and device Excel..... U can set datatype for that particular column as Text. If Your Column is Text, you need not worry for preceding zeros or anything.
It will come as it is!!!!

Provided you are using B2WIN Excel device to print the concern report.

Hope it helps!!!!

What is the command you are using that is popping up? I know for like the shell command on our platforms we can suppress the window from appearing.

I used start.application.local and in that i provided a string which opens the Batch file , with command of "cmd c:/.../temp.bat" and in that batch file i used the further commands for running the VB script file..

Hi,

Try using start or combination of start and exit 0(or just exit)

e.g.

start C:\Users\Yiwei\Downloads\putty.exe -load "MathCS-labMachine1"

or With an extra ""

start "" "C:\Program Files (x86)\Xming\Xming.exe" :0 -clipboard -multiwindow

tncserver.exe C:\Work -p4 -b57600 -r -cFE -tTNC426B
exit 0

this is the code which i am using




application.string = local.path.name & "\temp.bat"

ret.val = start.application.local(application.string,true,ret)


server.path = bse.tmp.dir$()




When i am running the program the sequence goes unpredictable. The batch file is run and then i suppose the program goes ahead at the same time which it should not.

But when i am debugging, the sequence is quite perfect...

When debugging you probably wait for each step to complete. Sometimes I add suspend statements to pause things for a few seconds.