cancel
Showing results for 
Search instead for 
Did you mean: 
ConwayN
Occasional Member - Level 1

Final Data Dump

For the past 8 years, we had 2 separate Concur instances/systems.  One for the Americas and one shared by EMEAI & APAC.  We have recently merged all countries into 1 global system; whereby EMEAI & APAC were merged into the Americas system which we now call our "global" system.  Here is the BIG issue:  We want our historical data from the EMEAI&APAC Concur system.  Concur said they would provide the data via .dat files whihc we can retrieve via MS Access.  MY IT team is not confidant that all of the data will come across successfully and has concerns. Has anyone gone thru this process?  Does it work?  Are there things we shoud be aware of?

Thanks in advance for your responses!

Natalie

16 REPLIES 16
WendyMos
SAP Concur Employee
SAP Concur Employee

The final Data Dump Extract will be a single zip file that is made up of the following:

* Stable.zip: contains reports and corresponding entries and data that go along with approved and paid reports. Note: not all deliveries will contain this file, it is based on client configuration

* Recent.zip: contains all other data and records that were not provided by extract or not approved for payment.

* DDL.zip: contains schema definitions (SQL scripts) for the tables in the Recent & Stable files to enable the client to create these tables easily in a local database. Note: In the examples below we will use an Access Database so the script in the ddl.zip file will not be used directly.

* Each zip file will contain .txt files, one for each table in the entity database in the stable.zip or recent.zip. The DDL.zip will contain one schema definition.

Image Archives:

* Extract_images_*.exe: contains the receipt image pdf files that accompany the data dump extract. There could range between one and many of these files depending on the number of reports.

 

Kind regards, 

Wendy Mos

justine2_
New Member - Level 2

What format are the reports within Stable.zip and Recent.zip comprised of? Also, is there a fee for the final data dump?

mohaahme
Occasional Member - Level 1

Hi Wendy,

 

Is there an technical document for Data Dump Extract File?

 

Also, I have an additional query 

  • If ct_report.dat file or any of the other file sends approver data? (who approved the expense report)
  • If yes, will it sent historical data as per the approver details of the expense report?

 

 

Thank You.

 

WendyMos
SAP Concur Employee
SAP Concur Employee

Dear Mohaahme, 

Yes we have a manual how you can retrieve the files and comprise both extracts etc. All data will be provided including who approved the expense reports etc. I'm not technical etc, but if you want more information than you can create a support ticket. 

maggieyang322
Occasional Member - Level 1

Dear Wendy, 

 

Our company also tries to obtain the data dump option to house our historical data from Concur. Is it possible to share the manual that you mentioned in this thread? 

Merin
Occasional Member - Level 3

I have done this once, and am preparing to do it again in the near future for companies we have acquired.

The one I have completed only had about 1.5 years of data, but everything worked very smoothly.  All files were downloaded, quality-checked, and loaded into a database without any issues.

 

lvw6187
Occasional Member - Level 2

Was there a cost for the data dump extraction?  We need to extract all images for last 5 years from Concur Expense, Purchase Request & Invoice.  Please advise how to proceed.  Thanks so much.

lvw6187
Occasional Member - Level 2

Hi,

Was there a cost for the data dump extraction?  We need to extract all images for last 5 years from Concur Expense, Purchase Request & Invoice.  Please advise how to proceed.  Thanks so much.

Dale_Lee
Occasional Member - Level 2

Just noticed this was from 2018 - but perhaps my comments will help someone else. We had this same situation. I will say that the files once extracted were huge and I spent several full days downloading them ! We never did create an Access database for them and our files were never recreated into a "report" type format. I have needed to access the images several times since for audit purposes and was able to find them quite easily. I would strongly suggest running report of all expense submissions prior to the account being disabled, our SAP system identifies reports with the legacy report number, the data dump used the Report ID for images. With the spreadsheets I'd downloaded of reports containing these fields I was able to search and find the required information.  One thing you may want to consider, and this was done by one of our other operating companies - is to retain the "old" version of Concur which gives you continued access for reporting and obtaining data. They have been able to retain their version at a minimum cost under what was called an "evergreen" plan. A few other comments, and perhaps some of this has changed since we did this (in 2019). 

 

Timeline – disconnect data was available for us approximately 3 months after our contract termination. Once you have been provided with the disconnect report you have a month to download the data. I had to follow up with Concur several times before obtaining the disconnect instructions and the file access.

 

Note that our system did not permit opening of .exe files which is the format provided by Concur. After contacting our security team and IT group, I was able to download using Chrome Browser. It was not possible to do via Internet Explorer. Unable to comment on other browsers (Edge).

 

Time involved – data download took several days and I was able to download only a few files concurrently. Note that password has to be entered for each file, and you cannot copy/paste this password. This is outlined in the disconnect instructions but until I did it, I did not realize how much additional time this would involve. Password provided is complex - 12 characters including special characters and caps, etc. and you must manually type this in for each file being downloaded.

 

File names (.zip files) of image extracts – unfortunately, these file names are not unique and do not tie back in any way to the reports or to dates.  

 

Image files – are in .pdf format. One .pdf per report and identified by the Report ID in Concur, e.g.: 0771F4604F3A4FEAA666

“Orphan” Images – not all image files will tie back to a report. Any images that the user has uploaded to their account, but did NOT attach to a report, will also be included.

Data files – these are “flat” file of all the data fields to be used as an index and/or to create an Access (or other) data base where you can create reports. There were several different files, for header information and detail information. We did not create a data base with these.

maggieyang322
Occasional Member - Level 1

Hi @Dale_Lee ,

 

Really appreciate your valuable comments here. One more question that would like to clarify with you, when you mentioned that you strongly suggest running report of all expense submissions prior to the account being disabled, do you mean trying to run any report in Concur to get all the submitted reports ID? 

Dale_Lee
Occasional Member - Level 2

Hi @maggieyang322 Yes, I ran a custom report (but you could use any standard report with the fields, or combine reports to get the information needed) that included the SAP Legacy Report Key (because that is the key that feeds into our SAP system so I could tie back with that - you may need something else, the employee name,employee id, the Report ID (e.g. 66D925EEBA3D4FD9B2DB), paid date, total amount due to credit card, and total amount to employee. I was later able to use these reports as keys for searching in the files downloaded from Concur (because we did not create an Access or other data base for the files, we just uploaded them to a Sharepoint site) by using the Legacy Report Key to find the associated Report ID which is what the images were created by Concur. Feel free to email me at dale.lee@ca.nestle.com if you like and I can share additional details.

maggieyang322
Occasional Member - Level 1

Hi @Dale_Lee ,

 

Thanks so much for your inputs! Let me go to our Concur to see if we can find any standard report with these info. Will message you if I have more questions. 

 

JR1982
Occasional Member - Level 3

GM @Dale_Lee !  This is incredibly helpful!  Our IT can't figure it out and is nervous about turning off the legacy system until they can build a database first.  I would love to touch base to get more information if you are available!  Thanks!

Dale_Lee
Occasional Member - Level 2

Hi, as mentioned we did not end up building a data base. In the end, I had to obtain supporting documentation (images of the receipts) for tax audit purposes for perhaps 20 reports on 2 separate occasions but did not need to provide copies of the reports themselves as this information was fed into our SAP system and available there (although not as a report format). Feel free to reach out to me directly at dale.lee@ca.nestle.com and I can share details - it was relatively simple to locate what I needed, the painful and time consuming part was downloading the data initially. If your intent is to build a database to access the data, the instructions for this were provided by Concur - not being technical myself I didn't do that and decision was it would be done by IT at a later date only if absolutely necessary, which it wasn't.

jhouseumich
New Member - Level 1

Dale, can you speak to whether or not all audit trail data was included in the extract?

Dale_Lee
Occasional Member - Level 2

It wasn't provided, only report information and image files