将 Excel 连接导出到 Access - .ODC 信息到 .ODBC
我有很多数据需要处理，我需要一些帮助。
我一直在使用一个 Excel 文件，其中有两个我感兴趣的工作表。它们各自生成一个 OLAP 数据透视表，其中包含我需要使用的数据。我想做的就是移动这些 (.odc) 连接来访问查询，这样我就不必手动粘贴所有这些信息并对其进行操作，然后再重复整个过程几次。
一张表是按零件编号和日期列出的吞吐量（通过一次操作的零件数量）。另一个是按部件号和日期记录的操作时间。我还有一个所有零件号的主列表，其中包含一些我必须混合的更多数据。
最大的问题：每个图表都生成自己的日期和零件号子集，因此我必须小心匹配数据才能运行计算。我试过：
- 用手。很快就厌倦了。
- 使用 LOOKUP、VLOOKUP、MATCH 和 INDIRECT 以及各种技巧。
真是一团糟。但我相信，如果我可以将原始数据透视表放入 Access，我可以添加一些联接并编写几个查询，结果将会很漂亮。
最糟糕的是，我可以手动将数据透视表数据复制/粘贴到访问中，但是如果我想更改或扩展数据集怎么办？我宁愿使用原始数据。
编辑：
数据保存在 SQL Server 上，我无法更改它。
Excel 数据透视表使用 .ODC 文件进行连接。他们给出了以下连接字符串：（
Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
我替换了实际的目录和源）
我可以使用 .odc 文件信息在 Access 中创建直通查询吗？
I have lots of data to wrangle and I need some help.
I have been using an excel file that has two worksheets of interest to me. They each produce a OLAP pivot table with the data I need to work with. What I would like to do is move those (.odc) connections to access queries so I don't have to hand paste all of this info out and manipulate it and then go through the whole process several more times.
One table is Throughput (number of parts through an operation(s)) by Part Number and by Date. The other is Hours Logged at the operation(s) by Part Number and by Date. I also have a master list of all part numbers with some more data that I have to mix in.
Biggest problem: Each chart is producing its own subset of dates and part numbers so I have to take care to match up the data to run the calculations. I've tried:
- By hand. Got tired with that real quick.
- Using LOOKUP, VLOOKUP, MATCH with INDIRECT and all sorts of tricks.
It's a mess. But I'm confident that if I can put the original pivot tables into Access I can add a few joins and write up a couple queries and it will turn out beautifully.
Worst comes to worse I can copy/paste the pivot table data into access by hand, but what if I want to change or expand the data set? I'd rather work with the raw data.
EDIT:
The data is held on SQL Server and I cannot change that.
The excel pivot tables use a .ODC file for the connection. They gives the following connection string:
Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
(I replaced the actual catalog and source)
Can I use the .odc file information to create a pass through query in Access?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是否考虑过使用合适的 OLAP 服务器？
OLAP 服务器比较
设置完成后，您将能够将 Excel 的数据透视表连接到服务器（以及其他报告工具）。
Have you consider using a proper OLAP server?
Comparison of OLAP Servers
Once setup you'll be able to connect your Excel's Pivot Table to the server (as well as other reporting tools).
与我们的 IT 部门交谈。构建 Cube 的人正在为我在 MS Access 中查询相同的信息。
谢谢大家。
Talked to our IT dept. The guy who built the Cubes is working on querying the same info into MS Access for me.
Thanks everyone.