How to export keeping the zeros(0001) to Excel?

NavCoder · 2008-02-20

How can I export a number to Excel, keeping the zeros at the beginning, like 0001.

I know that if I add a char to the beginning of the number, it will export all the zeros after that char, but I don't want to add any char.

How can I do it?

kriki · 2008-02-20

Try with:
txtTheNumber := '''' + FORMAT(TheNumber); // This meaning you add a ' before the string. To put a ' in a string, you need to double it, so you need to put 4 ' to get a string with a ' in it.

NavCoder · 2008-02-20

I've tried to add a ' creating a text constant with that value, but the Excel doesn´t ignored the '. :-k

This number is a code field, doesn´t need the FORMAT.

Savatage · 2008-02-20

did you use 4 single quotes?
''''

NavCoder wrote:

I know that if I add a char to the beginning of the number, it will export all the zeros after that char, but I don't want to add any char.

Why? :-k

NavCoder · 2008-02-20

Savatage wrote:

did you use 4 single quotes?
''''

I've created a text constant with ' as value.
The result is the same.

Im exporting to a .csv file, is this a reason to Excel write the '?

Savatage · 2008-02-20

so your exporting from Nav->CSV File->Import Excel

You can go from nav to Excel directly using the excel buffer

This number is a code field, doesn´t need the FORMAT.

Even with code fields - I use this code to export UPC codes

SomeTextVariable := ''''+Item."Item UPC/EAN Number";

tinoruijs · 2008-02-20

Hi,

Just tested how you can get 0001 into Excel.
Just fill a line in table Excel Buffer (370) as follows:

Row No. 1
Column No. 1
xlRowID 1
xlColID A
Cell Value as Text 0001

The @ turns the cell-properties into Text.

Create a codeunit to test it:
recExcelBuffer.CreateBook;
recExcelBuffer.CreateSheet('1', '2', '3', '4');
recExcelBuffer.GiveUserControl;

NavCoder · 2008-02-20

Savatage wrote:
so your exporting from Nav->CSV File->Import Excel

Even with code fields - I use this code to export UPC codes
SomeTextVariable := ''''+Item."Item UPC/EAN Number";

And the Excel ignores the quote?

Savatage · 2008-02-20

NavCoder wrote:

And the Excel ignores the quote?

What do you mean by that? Do you want excel to ignore it or not? :-k

NavCoder · 2008-02-20

Yes, I just want the Excel to write the number with zeros.

tinoruijs · 2008-02-20

NavCoder wrote:

Yes, I just want the Excel to write the number with zeros.

Then you should try the solution with the @ in the field NumberFormat in table 370 (see above)

NavCoder · 2008-02-20

tinoruijs wrote:

Hi,

Just tested how you can get 0001 into Excel.
Just fill a line in table Excel Buffer (370) as follows:

Row No. 1
Column No. 1
xlRowID 1
xlColID A
Cell Value as Text 0001

The @ turns the cell-properties into Text.

Create a codeunit to test it:
recExcelBuffer.CreateBook;
recExcelBuffer.CreateSheet('1', '2', '3', '4');
recExcelBuffer.GiveUserControl;

This might work but when using on a different database, the user always has to add that record into the Excel Buffer Table.

Savatage · 2008-02-20

how you are are exporting the data?

-Dataport -CodeUnit -Report -Excel Buffer -Copy & Paste
I'm not sure what's not working for you - these are proven metods that work.

NavCoder · 2008-02-20

Dataport.

Savatage · 2008-02-20

I did a test exporting the same field "UPC" as a test
Once alone & Once with the upc := ''''+Item."UPC Code"

I see when you click on the CSV file the ' does show in column when opened. But I also Noticed that if you name the file type TXT instead of CSV that an unmodified field will retain it's leading Zero.

Then when you import the file into excel just make sure the Format->Cells->Text for that column.

How are you importing that data? Data->Import External Data?
there you can define the type of field it is.in your case you want it text not number.

If you go right from Nav->Excel Buffer->Excel the 4 single quotes works great.

NavCoder · 2008-02-20

I´m just exporting with a dataport, I´m giving the .csv manually when I´m writing the name of the file.

tinoruijs · 2008-02-20

NavCoder wrote:

tinoruijs wrote:

Hi,

Just tested how you can get 0001 into Excel.
Just fill a line in table Excel Buffer (370) as follows:

Row No. 1
Column No. 1
xlRowID 1
xlColID A
Cell Value as Text 0001

The @ turns the cell-properties into Text.

Create a codeunit to test it:
recExcelBuffer.CreateBook;
recExcelBuffer.CreateSheet('1', '2', '3', '4');
recExcelBuffer.GiveUserControl;

This might work but when using on a different database, the user always has to add that record into the Excel Buffer Table.

No they don't have to do that.
You can make a function that fills the Excel Buffer table. Like in report 82. It uses the Excel Buffer as a temporary table.

Instead of starting the dataport you could use the function export to excel.

tinoruijs · 2008-02-20

It seems my Excel-solution is a bit off topic..

NavCoder · 2008-02-20

tinoruijs wrote:

It seems my Excel-solution is a bit off topic..

Thks for the help, but I´m looking for a simple way.

If there isn´t, I´ll try yours.

Thks in advance.

Savatage · 2008-02-20

NavCoder wrote:

I´m just exporting with a dataport, I´m giving the .csv manually when I´m writing the name of the file.

Did you try giving it a .TXT manually when writing the filename?

then open excel Data->Import External Data.

If you looking for simple what's easier than using the export to excel feature?

You can make a function that fills the Excel Buffer table. Like in report 82. It uses the Excel Buffer as a temporary table.

Instead of starting the dataport you could use the function export to excel.

NavCoder · 2008-02-20

When I say simple, doesn´t mean easier, I have 2 dataports(Import/Export).
I´m just looking for a way to let me write the number correctly, just changing
a property or adding a line of code.

If isn´t possible I will think about using other objects and create functions to solve the problem.

Thks for your support.

Savatage · 2008-02-20

I'm just saying that if you look at the TXT or CSV file you created using NOTEPAD you will see the leading Zeros are there. It is getting lost on the method you are using to open the file in Excel.

If my CODE is 0001 for a customer for example and I export the Cust No using a dataport to a TXT or CSV file I see "0001" which is correct.

My test dataport produced a file like this (item# & UPC Code)
"10030","062765350127"

Becka · 2008-02-21

just take a tour of Excel Buffer..

good luck
:roll:

sadynamics · 2008-02-21

Hi Navcoder,

your issue is solved ? i am also facing the same problem. For ex: in navision suppose customer number is 00778 then i am using dataport to export this value into CSV but after exporting the value in Excel is 778 it will mislead the information.

if table contain huge data it is unable to check and edit the data..

Hope experts can understand the situation . How to rectify the same ?

sadynamics · 2008-02-21

Hi,
below code from savatage works like a charm

txtTheNumber := '''' + FORMAT(TheNumber); in onbeforeexportrecord() i kept this code it worked...

below are my result

Formated(using code) No:(normal no without code)

000114 114

Thanks for the help....

jversusj · 2008-10-10

just wanted to thank the board for the @ in the NumberFormat - just what i needed!

How to export keeping the zeros(0001) to Excel?

Answers

Categories