cancel
Showing results for 
Search instead for 
Did you mean: 
cstanley
Occasional Member - Level 2

Invoice Extract File Blank Placeholders

Hi,

Has anyone else had a problem with blank placeholders not coming through in the extract file? For example, a numerical invoice beginning with a zero "0123" will come over as 123. We also have function codes such as "00" or "01" which come over as blank or "1." We import our data into Microsoft NAV Ceres and it will not accept a blank or 1, since they must match our function code list in our accounting system. I was told that the extract file cannot be changed, and it's a pain to filter through all the invoices and change them individually. Also, Ceres will only accept .xlsx (NOT .xls) formats, so we already have to copy and paste from the .csv file Concur exports. Our Concur Coach told us to just change the format of that column, but but this does not actually change the data stored and still will not import. I've had to insert a formula to fix the function codes, and it seems like much more work than it should be. Has anyone found an easier way? I've attached screenshots to better understand the issue. Concur Function Code.JPG

Caroline
G/L Accountant
Alameda County Community Food Bank
3 Replies
dworth
Routine Member - Level 1

Re: Invoice Extract File Blank Placeholders

I checked mine as well... it appears to be Excel "helping" that is causing the issue of the dropped leading zeros.  Assuming this a repetive process.... can you just VBA macro the creation of the .xlsx file from the .csv file?

Darryl Worth
American Society of Hematology
cstanley
Occasional Member - Level 2

Re: Invoice Extract File Blank Placeholders

Thank you. Yes, that would solve the file extension problem, but the leading zeros are another issue. I was hoping there was a setting in Concur that would correct the extract problems since several people in our department extract data and will have to workaround this issue. 

Caroline
G/L Accountant
Alameda County Community Food Bank
SAP Concur Employee
SAP Concur Employee

Re: Invoice Extract File Blank Placeholders

Hi Caroline,

This is definitely an Excel issue as the Concur system does extract the leading zeroes properly. If you open the .csv file in a text editor like Notepad or Wordpad you will see the leading zeroes are present in the raw data.

The issue is when Excel sees that data it sees a number and thinks numbers aren't supposed to start with a 0 and "helps" you by stripping them off. You'll need to trick Excel into thinking those fields are text fields instead of number fields. The process is manual, but fairly straight forward. The following video on Youtube goes through the process.

https://www.youtube.com/watch?v=9KDK1FRcmSo

I am not an Excel guru nor a programmer, but I would not be surprised if there were a very simple way to program a macro to do the steps in the video automatically.

 

Grant Chase - Sr. Training Consultant - SAP Concur, Global Learning Services