When we export information from an IFS window (ie. Customer Order Lines) the information is exported in to Excel with a format of General. Unfortunately, it drops the zeros off the front of our customer numbers. Right now our work around is it export the information to a txt file, then use Excel’s “Get Data” functionality to convert the cells from General to Text format.
Is there a set-up available that will automatically do this when we export from IFS?
+24
This was always an issue, and the recommendation was/is to avoid key fields starting with zeroes. For example, if you need about 5000 values with consistent character length, start at 10000 and go to 15000.
What are you exporting?
If you can do it by exporting the result set from SQL Query Tool or a SQL Quick report, you can concatenate in something like #, then export it, and it will preserve the zeroes. Then do some work in Excel to strip out the # without losing the zeroes.
select '#'||customer_id from customer_info where customer_id like '0%'
We are exporting to excel from an IFS screen - in this instance it was Customer Order Lines. We just highlight the lines RMB select output then select excel. It is our customer number that is 5 digits. When it exports it to excel it leaves off leading digits like 00002 exports as 2.
I’ve had to find a work around for this as well. If you have VLOOKUP or other formulas written, you may have to change them looking for “00002” to just “2” within that specific column.
That being said, you may also have to standardize the column layouts that your using to export the data.
+8
In IFS 9, it is possible to define an output channel as XML but open in Excel. You can either associate XML with Excel within Windows System settings or choose, Application to Start and navigate to excel.
You are then prompted when excel loads the file to open as an XML table.
This will retain leading zeros for you, but the downside is that it will treat all numeric fields as text too, so those that you need as numbers will need to be converted from text. This can be done with the Excel Error checking which will identify that you have a number stored as text.
You could also make cf with an Excel formula:
result:
+8
our customers normally solve the “002” and “0002” problem using formatting in Excel in combination with our Microsoft integration solution.
I have created a quick report and added CHR(9) in the select, for example
select CHR(9)||customer_no from &AO.customer_order_line, this will also solve another issue when long numbers for examlple “1020019393400100” are formatted in excel to 1,02002E+15
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
