 |
|
Topic: Excel - Microsoft Query
|
| By: Steve T | Posted on: Aug 9 2022 at 03:20:57 AM | | Is it possible to pull data from the MRP using Excels "Get Data: From Other Sources: Microsoft Query"? | |
| By: Guest | Posted on: Aug 9 2022 at 06:46:03 AM | Yes. We do this all the time.
Although the data isn't easy to read. What I mean is that if you look in the purchase orders table you won't see any actual part numbers so it won't be immediately obvious what you're looking at.
For example the data file is usually named MRP5DATA and that data file contains a number of tables. One of the tables is named "tblstockitems" This contains all the part numbers and descriptions. In that table is a column "ItemID". Everything else in the database, purchase orders, BOMs etc. All just contain that ItemID instead of an actual part number.
But if you 'GET DATA and have a few tables in excel shets AND if you're able to create a macro or VBA script to join or merge sheets based on the relevant IDs you can get pretty much anything you want.
| |
| By: Steve T | Posted on: Aug 10 2022 at 01:51:53 AM | | ok so this is going to be fun then. The excel sheet I'm making is already pretty macro-heavy and slow, and so far I can't seem to find the MRP folder so I am guessing it's on one of our closed access servers :( | |
| By: Guest | Posted on: Nov 17 2022 at 09:22:01 AM | You can see where the MiniMRP data file is saved if you go to Setup/Tools>>DataBase Setup>>Browse. This assumes that you are using the first option "Use standard/local MiniMRP database. (Default)"
I think that if you are using one of the SQL options it should make importing data into Excel easier assuming you know how to connect to the SQL server.
I have not had any luck getting Excel to import data on Excel 2019. This used to work for me but has since stopped working. I just get an unexpected error when trying to open the database in Excel. | |
| By: Guest | Posted on: Nov 17 2022 at 10:05:48 AM | | I just tried importing as an Access database on another machine and it works fine. I guess I just need to figure out why my excel install is not working correctly. | |
| By: SimonQ | Posted on: Nov 21 2022 at 02:16:03 AM | When you figure it out, could you please report back? I have the same/similar problem.
Thanks. | |
| By: Guest | Posted on: Nov 29 2022 at 03:50:39 PM | For my machine I found that updating to 64 bit Excel fixed this issue.
I had to uninstall 32 bit office and install 64 bit using the same license and the data import worked fine.
I used Data> Get Data> From Database> From Microsoft Access Database and selected my MRP5DATA file (you have to change your filter to view "All Files" to see MRP5DATA). It brings up the Power Query Editor then I clicked on the binary file then went up to the top and said to load as Access Database. From here I was able to get to all the data I needed. | |
| By: SimonQ | Posted on: Dec 1 2022 at 03:05:15 AM | Thank you for that.
I haven't tried the Power Query Editor. I usually extract data using ODBC, Data > Get Data > From Other Sources > From Microsoft Query > MS Access Database.
I can import some tables, but the tblstockitems fails with an error [Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification on column number 10 [TotalAllocQty]. Removing that (and TotalQty) from the import allows the import to proceed, although that renders the data fairly useless.
I will have a play with Power Query Editor. Thank you.
| |
| By: Guest | Posted on: Dec 1 2022 at 05:09:06 AM | An error such as "Invalid character value for cast specification" The word "Cast" suggests something is being converted. For example when an decimal is converted into a integer it's called a Cast which usually results in the decimal being rounded up/down. Or when, for example text such as "1234.56" is Cast (converted) to a decimal 1234.56
Such errors are quite common when pulling data out of a US/EN based database driver and casting it into another locale where the decimal point is expected to be a comma. Or when 1234.56 goes into text as "1,234.56" and then something else tries to cast that into an integer.
TotalAllocQty and TotalQty are both decimal with n characters after the decimal point. You need to make sure your importer is handling that correctly.
Having said all that I've never had a problem. Just in Excel GetData from an Access db and point it at the MRP5DATA file. Mine doesn't mention power query or odbc or anything like that. But then my Excel is quite old. I think newer version just added more complexity.
| |
| By: SimonQ | Posted on: Dec 5 2022 at 02:30:28 AM | Thanks Guest?
I will investigate further.
| |
Reply - add a comment to this topic.
You may enter letters, numbers and standard punctuation only. HTML and other scripts/tags will be rejected.
|
|
|
