mibuso.com
Home› Navision Attain
Options

Format CSV

DesmedtSDesmedtS Member Posts: 53
edited 2004-06-25 in Navision Attain
hello,

I export navision data to a CSV file.

this works fine, the only problem i have got is formatting data. For example, if i want to export the value 00001 i got in my CSV file 1.
So I thougt, lets make all the sheet set as text.

But no effect.

anybody got a solution ?

Thx

Comments

  • Options
    RobertMoRobertMo Member Posts: 484
    1. what value do you have stored in navision ?
    2. what is the data type ?

    you will have to use FORMAT function and/or some other string functions. please read in help how to use it.

    a hint. if you want to format an integer iMyInt with a fixed length (iTargetLength) string (tMystr) with leading zero here's one way to do it:

    tMyStr := PADSTR('',iTargetLength - STRLEN(FORMAT(iMyInt)),'0') + FORMAT(iMyInt);
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • Options
    DesmedtSDesmedtS Member Posts: 53
    The value stored in Navision is 000001
    The datatype is code

    I Hope this can help you
  • Options
    RobertMoRobertMo Member Posts: 484
    If you are using dataport then for sure in csv file should be leading 0. Open your csv file with text editor (notepad) not Excel and check this.
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • Options
    DesmedtSDesmedtS Member Posts: 53
    I have checked my CSV, this is correct.
    But when he is automaticly opened in Excel, the leading zeroes are gone.

    I tried to set my worksheet as text but no result
  • Options
    RobertMoRobertMo Member Posts: 484
    Excel is so "smart" that is sometimes to smart. It doesn't help that you set cell format to text after opening csv, because the "damage" has already been done.
    What you can do is to name your file with .txt extension. and open a file with notepad (etc.). use CTRL+A (Select All) and CTRL+C to copy all and paste it to clear excel sheet. BUT before pasting in Excel select all cells in Excel and set cell format to text.

    If the filed delimiter in your dataport is , or ; you will have to split your cell data. But instead of doing this, just change the field delimiter in dataport to <TAB>.

    Then when pasting to Excel fileds will be placed into own cells...
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • Options
    alensoalenso Member Posts: 4
    There is another good solution in Excel:
    The file extention should be ".TXT" (not ".CSV"). When you open the file a window will open where you select the delimeter, etc.
    When you press next you can select a format for each column - select text format for your column.
    Wish you success!
  • Options
    SavatageSavatage Member Posts: 7,142
    if you insert an [ ' ] apostrophe (did i spell that right?) before 00001

    '00001 - when it goes into excel does it leave 00001 in the field?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Options
    DesmedtSDesmedtS Member Posts: 53
    Nop, no effect :x
  • Options
    RobertMoRobertMo Member Posts: 484
    but the 0000 are in the file (as you said), so it's just the matter how this file is opened in excel... and there are some hints to do that.
    are we missing something ?
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sign In or Register to comment.