Excel is automatically stripping the zero from the field because it thinks it should format the column as a number instead of text. By the time you have opened the CSV file in Excel the leading zeros have been stripped off. Formatting the column as text will not add the leading zeros back. A simple workaround is to add a tab character at the end of the field before it outputs it to a CSV file. When Excel opens the file it will treat the column as text and not strip off leading zeros.

If you need to get rid of the tab character you can use an Excel macro to automatically remove them.

and you are seeing the # characters because the excel column width is not enough to show the full content. You can double click the corners of the columns to expand them as necessary. Also if you format the given cell as text, the numeric notation will also removed.

Hi @KHALIDU,

Showing the values in scientific format is a behaviour comes up with Microsoft Excel. However you would be able to handle your requirement in either way.

1. You could try to convert the value to char and create the report.
    

   

   

2. Change the cell format in Excel.
       
   Select that particular column of cells, the Right click on Format Cells…

   

   
    
   Hope this would be helpful.
    

Thanks every one for replying, I have already  tried

•  to_char() and format column in excel , both of these still does not recover leading zero’s.

@alanbclark , I looked into adding tab character at the end, couldn't figure out something to fix following:

ifsapp.mixed_payment_lump_sum_api.get_ledger_item_id('01',ifsapp.client_sys.Get_Key_Reference_Value(t.keys,'MIXED_PAYMENT_ID'),ifsapp.client_sys.Get_Key_Reference_Value(t.keys,'LUMP_SUM_TRANS_ID')) Payment_Doc_No

any pointers. I cant change anything in the table. I have to fix it within report

Thanks

Hi @KHALIDU ,

I tested this issue and I didn’t encounter any issue.  The steps are mentioned below. 

1. This is how I save the data in the database.

1. Quick report

1. View Report

1. Export to excel - (Here leading zero can be visible)

1.  By default “Number Stored as Text”

1. If I change above to “Convert to Number” then only I can see what you have seen in your issue. 

Hope below link will help.
https://support.office.com/en-us/article/fix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0#__toc260840020

We have the same issue where auto-opening CSV files lose the leading zeros, e.g. with this configuration…

What does work is XML…

Which looks like this when opened in Excel…

One alternative is to save the CSV and then when you open it in Excel, it is treated as an import and you can customise the column types. Long winded but reliable…

I’m sure we did find a way around this during our IFS 7 to 10 data migration process but I can’t remember what it was!

Hi @KHALIDU ,

I tested this issue and I didn’t encounter any issue.  The steps are mentioned below. 

1. This is how I save the data in the database.

 

1. Quick report

1. View Report

 

 

 

 

1. Export to excel - (Here leading zero can be visible)

 

 

1.  By default “Number Stored as Text”

 

 

1. If I change above to “Convert to Number” then only I can see what you have seen in your issue. 

 

 

Hope below link will help.
https://support.office.com/en-us/article/fix-text-formatted-numbers-by-applying-a-number-format-6599c03a-954d-4d83-b78a-23af2c8845d0#__toc260840020

 

 

@Sandun Madola , i appreciate your help. i understand ‘ method. just dont understand how i can use ‘  with my  results coming from API like the one below: Let me know if there is a way to use ‘ in following line. Bundle of Thanks

ifsapp.mixed_payment_lump_sum_api.get_ledger_item_id('01',ifsapp.client_sys.Get_Key_Reference_Value(t.keys,'MIXED_PAYMENT_ID'),ifsapp.client_sys.Get_Key_Reference_Value(t.keys,'LUMP_SUM_TRANS_ID'))Payment_Doc_No

Hi @KHALIDU ,
Could you please elaborate above. Is this a part of the SQL statement you use in the quick report?  I didn’t get what you mean by the above API call and the export report to excel?

@Sandun Madola , yes this is the part of statement that brings in check number and looks fine in IFS, when user export the report to excel we lose leading zeros as most of checks are started with 0. and i am trying to fix this in IFS 8. 

@GPIE  we are in the process of upgrading to IFS 10 ,as you mentioned that you find a way in IFS 10 for this. i have hope now . The process you showed me seems interesting and i tried but it gives me true xml file instead of excel.

@KHALIDU I remember now!

We used Toad for Oracle. It can export to native Excel and has an Automation Designer, which is really VERY simple to configure. This isn’t much use for day-to-day export requirements but for bulk export for data migration, cleansing etc, it is invaluable:

1. In Toad, Open Automation Designer
2. Create an “Export Dataset” Action
3. On the Options tab, specify the “Output Directory”
4. On the Dataset tab, select Database Objects (for example) and click Add
5. Choose tables or views based on a filter and click Load Rows
6. Click Run and the Excel files are generated

So easy…!

@GPIE Thanks for this information. its very valuable. Appreciate your and everyone else's help in this thread.