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

NavCoder

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

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

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

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

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

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

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

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

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

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

tinoruijs

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

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

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

Dataport.

Savatage

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

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

tinoruijs

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

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

NavCoder

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

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

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

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

just take a tour of Excel Buffer..

good luck
:roll:

sadynamics

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

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

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