They used Q&A to create separate information stores about customers, machines we have built and shipped, Technical Service/Field Service calls, etc. I am evaluating each database they currently have and seeing if:
- The information is already collected and we just need to do some database/report magic to extract the info.
- By altering a business process, our ERP system, CRM System, or Microsoft SharePoint becomes a better fit for the information they wish to collect.
My quest seemed extremely unfruitful yielding no direct instructions, save a few 'costly' consulting services who, for the right price, would do it for me. But alas, I am the type of person who wants to the 'how' to do it, and I don't mind getting my hands dirty in the process.
Since I had such a hard time finding this information freely on the internet, once I finally figured it out (my wife knows all about my inability to 'let it go'), I was inspired to join the 21st century, create a blog and share with you my experience.
Below are the steps I tool to get the data out. Looking back it seems so easy, yet in the middle of the process it seems confusing. You may have to change certain variables in the formulae to achieve the desired results. You take full responsibility for your actions should you decide to follow my advice.
Phase 1 - Emotional Preparation
- Get over the fact that you are a stubborn German man, and cannot export to a .csv file to achieve the results you want. This step is paramount, do not overlook this step.
- Accept the fact that you have to export the Q&A database into a temporary Paradox database.
- Brace yourself for the ramifications of bothering the sales quoting staff for 5 minutes so you can start the process.
- Through VNC remote into the sales staff's desktop before she returns from lunch and fire up Q&A in Windowed mode so you can tell WTH you are doing.
- Through Q&A export the data to a file. Choose Paradox
- Name the file 8 characters or less with .db at the end - Q&A is a DOS based lifeform.
- Make a copy of the export file so you always have a source file to open.
- Open Microsoft Access 2007 (if you have it)
- Create a new database
- Go to the 'External Data' section of the Ribbon
- Go to More -> Paradox File
- Select 'Import Source Data...' and browse to the location of your copy of the data.db file you created
- A table is now created with the name of your source file
- If you go to Home->View->Design View you can see the field names/definitions
- Now your data is in Access 2007 and you can clean it up and export it to any format you wish.
- I used Gadwin Software's Gadwin PrintScreen free edition to create my limited screen shots.
- If you find this post helpful, please leave comments below.
4 comments:
Hi
An old post for you and just came across it. Wanted to say that I got a laugh out of reading the steps required and that it may help in what I am trying to do.
Phase 1, step 1 and 2 I know will be an issue for me even though I am not German. I think this is universal!
Have a great day.
I don't know why you think you can't export to CSV. We still use Q&A and have to export to Excel. QA-File > Utilities > Export Data > Standard ASCII > [pick retrieve spec] > ASCII Options (use defaults), F10.
I suggest importing to Excel. Access may truncate fields, Excel won't. (I just now tried a quick test without fully defining the Access field types before importing and some truncated.) Once it's in Excel it shouldn't be difficult to port to Access, but properly defining the fields in advance will probably avoid truncation.
What I meant was that there is no direct mention of .CSV
Super wpis. Pozdrawiam i czekam na więcej.
Post a Comment