Invoice Extract File Blank Placeholders

[cstanley]

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. 

[dworth]

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?

[cstanley]

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. 

[GrantC]

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.