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

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.

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.

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?

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.

U need to append ' in ur file just before 2nd column in ur 3gl Baan program.
That will give u the result u need.

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 ?

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.

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.

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

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).

Thanks to all of U.

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

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

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".

Joedi, terrific!! Works perfectly! Thanks a lot!

We can also use TEXT function of Excel using 3GL program. Here is the code...

In Excel :
=======
=TEXT(1234,"0000000")

In LN :
=======
aloc = 31
chr$(061) & "text" & chr$(040) & str$(aloc) & chr$(044) & chr$(034) & "000" & chr$(034) & chr$(041) & " " &

Try this it will definitely work.:):):)

Just this works fine as well -

test.f = "=" & chr$(34) & field.f & chr$(34)

You can write the variable to your CSV. That's it. Excel will handle the rest.