Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th August 2002, 01:41
BigJohn's Avatar
BigJohn BigJohn is offline
Member
 
Join Date: Feb 2002
Posts: 87
BigJohn is on a distinguished road
Baan: Baan IV c3 - DB: Oracle - OS: UNIX
.csv extract : "01" is being shown as "1"

Hi,
I am creating a .csv extract.
One of the columns is a string of size 2.
However the values are always numerical.
01,02,05,56 etc

Now my problem is that, excel always considers this as a
numerical column. So "01" appears as "1".

How do I solve this problem?
Thanks.
BigJohn
Reply With Quote
  #2  
Old 6th August 2002, 02:42
Ravenscross's Avatar
Ravenscross Ravenscross is offline
Guru
 
Join Date: Aug 2001
Location: Manchester
Posts: 318
Ravenscross is on a distinguished road
Baan: Lots of different versions - DB: Most of them - OS: Almost all of them
Long solution

Well as far as I can work out there is only one way to do this and thats to write a macro that converts single char numbers into a text string that holds "0" + the number. I remember doing this once for a spreadsheet but for the life of me cannot remember the way to do it, I think I added a colum, hid the original and created a formula in the new column, a lot of work after the import needing to be done.
__________________
Christopher Cross

"Grace is there for only one reason, to fall from"
Reply With Quote
  #3  
Old 6th August 2002, 03:32
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
If you are willing to put up with the appearance of a single quote before the field, you could construct the .csv with your particular field preceded by a single quote, example:

'01, nextfield, ...

I intend to try preceding the field with a SPACE character sometime, but if you beat me to it, let me know if THAT works.
Reply With Quote
  #4  
Old 6th August 2002, 04:57
benito's Avatar
benito benito is offline
Guru
 
Join Date: Jan 2002
Location: Eastern US
Posts: 517
benito is on a distinguished road
Baan: ERPLn 10.2.1 / BaanIVc4 - DB: Oracle11/Informix - OS: Unix/Linux
Try this...

I am assuming you use open the .csv file with with the Text Import Wizard of Excel.

Step 1 - check Delimited
Step 2 - Delimiters - check Comma
Step 3 - Column format - look and select the problem Column and check Text (Note: General will format your column to numeric because it sees a number)

Hit Finish. Did it work?
Reply With Quote
Sponsored Links
  #5  
Old 21st April 2004, 10:50
TheBurniou's Avatar
TheBurniou TheBurniou is offline
Member
 
Join Date: Aug 2002
Location: France
Posts: 34
TheBurniou is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: NT
Question

Hi,

I am using a solution explained on the board wich consist of using a 3GL script that calls appl_start("excel.exe " & file.name,"","","","").
I wonder if it is possible to specify some parameters to excel so that the second column for exemple is to be string.
Reply With Quote
  #6  
Old 21st April 2004, 11:15
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
append '

U need to append ' in ur file just before 2nd column in ur 3gl Baan program.
That will give u the result u need.
Reply With Quote
  #7  
Old 21st April 2004, 11:29
TheBurniou's Avatar
TheBurniou TheBurniou is offline
Member
 
Join Date: Aug 2002
Location: France
Posts: 34
TheBurniou is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: NT
If i add ' before the value ('00213) in the file, when opening excel with this file the value of my cell is '00213.

Any idea ?
Reply With Quote
  #8  
Old 21st April 2004, 12:39
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
custom cell format

The reason is it's still a csv file and no Excel cell editing function is applied to it .Once u edit the cell and do not do any modification,it displays it the way u wish.

This however is not good solution. Instead u can apply excel custom cell format 00000 (Format --> Cell --> Custom ) to display 5 digits (12 as 00012) and apply the same to entire column . If it's frequent , u may write a macro to this. In this u don'y need to append ' in Baan 3 GL program.
Reply With Quote
  #9  
Old 21st April 2004, 14:56
TheBurniou's Avatar
TheBurniou TheBurniou is offline
Member
 
Join Date: Aug 2002
Location: France
Posts: 34
TheBurniou is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: NT
Arrow

The pb is that the report is to be sent not only to one client but many so the update by a macro on the client side is probably not the good solution.
Reply With Quote
  #10  
Old 21st April 2004, 15:19
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,935
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
What I do

I take you are talking about the import into Excel? When I create reports from Baan I always use pipes so the user has to run the import wizard. Then the user can determine what they want to see as text or numbers. I believe if you just changed the name from ".csv" to something different then the user would have to go through the import wizard.

Mark
Reply With Quote
  #11  
Old 21st April 2004, 15:32
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
network folders

If u have Visual Basic , then u can create a VB exe and call that exe instead of excel.exe in device . That exe should in turn call the excel object internally ,format the cells , save it in xls format and show the users the way they want.

U can keep the such an exe in a mapped network folder whcih can be accessed by all clients running Baan.

Same way the macro file can also be put in a network folder which can be accessed as an startup open files (tools --> Options --> General) and this macro can be run with a short cust keys like CTRL+SHIFT + <some key>.

In both cases u either need to hard code network mapped location in ur 3 GL program or the network folder has to be in path of client running the program.

If users are comfortable running import wizard as suggested by Mark, then it's very quick and easy solution (from programmer's perspective).
Reply With Quote
  #12  
Old 21st April 2004, 19:07
TheBurniou's Avatar
TheBurniou TheBurniou is offline
Member
 
Join Date: Aug 2002
Location: France
Posts: 34
TheBurniou is on a distinguished road
Baan: 4c4 - DB: Oracle - OS: NT
Smile

Thanks to all of U.
Reply With Quote
  #13  
Old 22nd April 2004, 19:31
nneilitz's Avatar
nneilitz nneilitz is offline
Member
 
Join Date: Feb 2002
Location: Midland, TX
Posts: 40
nneilitz is on a distinguished road
Baan: IVc4sp15+ - DB: Oracle 8.1.6.3 - OS: HP-UX 11.11 (11i)
Another approach is to delimit the .csv as "value","value" etc.

output string

""""&string value&""","""&string value""","""....&""""

If you have a "01" string, excel will open it as a string rather than a number
Reply With Quote
  #14  
Old 18th January 2006, 14:29
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
I ran into this old thread, because I am running into the same issue. I export data in csv format from Baan to the server. One of the fields is tel. number. This is a file based on user input, so some users input with seperators and spaces (i.e. between area code and tel. number), so those are being displayed fine, but the ones which are inputted as one numeric string, are seen as numeric fields when opened in Excel, and thus formatted as such (-> right allignement instead of left, overflow symbols etc).

Now I have to explain to all users who use it (which could be a lot) how to right click on this column in Excel, and change the cell type from numeric to text, so that the data is displayed correctly. I also looked for a way to avoid that. Of course preferably by having access to the Excel cell-setings and setting it as text, but otherwise by any other way.

I haven't succeeded so far. I cannot settle with adding a ' or so in front of it. I could maybe still deal with one space, but this doesn't work (I tried it). Neilitz's suggestion does not deliver any result either.

So I was wondering if anyone has a solution for this.

Thanks in advance!!


Kind regards,

En
Reply With Quote
  #15  
Old 18th January 2006, 14:39
joedi01's Avatar
joedi01 joedi01 is offline
Junior Member
 
Join Date: Apr 2002
Posts: 6
joedi01 is on a distinguished road
Hello all,

I faced the same problem with item code fields. Excel converted the item from "001000000" to "1000000".

The solution was to put the item code in Quotation Marks with an "=" in first position, for example ="001000000".
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +2. The time now is 10:42.


©2001-2017 - Baanboard.com - Baanforums.com