Format CSV

DesmedtS

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

RobertMo

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

DesmedtS

The value stored in Navision is 000001
The datatype is code

I Hope this can help you

RobertMo

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.

DesmedtS

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

RobertMo

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

alenso

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!

Savatage

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?

DesmedtS

Nop, no effect :x

RobertMo

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 ?