将340列导入Access 2010.“如何？” [英] Importing 340 columns into Access 2010. "How?"
问题描述
我收到了数据转储在csv文件中找到我。
一旦打开并将其保存在excel中，大约有18000条记录。
标题行有340列。
这将是一个每周过程，我想自动化
需要将excel表导入访问。
思考：
是否有人可以通过访问我的Excel文件来获取代码，并且可能只使用TransferSpreadsheet导入某些列？ （不知道该怎么做）
有没有办法可以将我的csv或excel文件保存为ODBC，然后执行传递查询来检索我需要的列。 （不知道该怎么做）
你有意思。
再次感谢大家我这次也喜欢上了空白数据库和csv文件我正在尝试加载。再次感谢您的帮助
I receive a "Data Dump" that comes to me in a csv file.
Once I open and save it in excel there are about 18000records.
The header row has 340 columns.
This will be a weekly process, I''d like to automate
Needing to import the excel table into access.
Thoughts:
Does anyone have code that will reach out from access to my excel file and import just certain columns maybe with a TransferSpreadsheet? (Not sure how to do this)
Is there a way I can save my csv or excel file as a ODBC, then do a pass-through query to retrieve the columns I need. (Not sure how to do this)
You''re thoughts please.
Thanks again everyoneI also this time attached a like to a blank database and the csv file I''m trying to load. Thanks again for your help
推荐答案
TransferSpreadsheet的导入选项可以正常工作。您可以指定范围，但我认为它必须是连续范围。换句话说，您无法同时指定A1：D18000和R1：V18000。所以你可能只是单独调用TransferSpreadsheet。
一般来说，我会解决这个问题的方法是在Access中创建一个合适的关系表设置，并始终导入所有Excel文件。导入完成后，您可以使用适当设计的SQL查询将数据拆分为规范化表格结构并删除导入的表格。
我明白你的情况我最近构建了一个粗略的ETL（提取，转换，加载）数据库，用户可以导入Excel文件，数据库对数据执行一些聚合计算，并将这些计算的结果加载到我的永久表中schema（临时导入表被删除）。
The import option of TransferSpreadsheet will work fine. You can specify a range, but I believe that it must be a continuous range. In other words, you wouldn''t be able to specify A1:D18000 and R1:V18000 simultaneously. So you could probably just do separate calls to TransferSpreadsheet.
The way that I would approach this problem, in general terms, is to create a proper relational table setup in Access, and always import the Excel file all at one time. Once the import is complete, you can use appropriately designed SQL queries to split the data off into the normalized table structure and delete the imported table.
I do understand the situation that you''re in. I recently built a crude ETL (Extract, Transform, Load) database whereby the user would import an Excel file, the database performs some aggregate calculations on the data, and the results of those calculations get loaded into the permanent tables in my schema (with the temporary import table getting deleted).
首先，您通常可以直接读取CSV文件，我建议不要涉及excel。
第二关，我认为访问表最多有255个字段，这意味着您要么必须限制要导入的字段数量，要么导入到2个表中，指定要导入的列的子集。
您可以手动设置导入规范ification，保存，然后在你的代码中重复使用。
由于最后已经指定了patjones，你（也可能）还需要对其进行规范化。但这取决于您的最终要求。快速查看csv文件告诉我它没有以当前形式进行规范化。
First off, you can usually read the CSV file directly, and I would suggest that, instead of involving excel in the matter.
Second off, I believe access tables have a maximum of 255 fields, which would mean you either have to limit the amount of fields you wish to import, or do the import into 2 tables, by specifying a subset of the columns to import.
You can manually setup a import specification, and save it, and then reuse it in your code.
As patjones has allready specified in the end , you (may) also need to work on normalising it. That however depends on your end requirements. A quick look at the csv file tells me that it is not normalised in its current form.
或者，如果这只是您需要执行该任务的时间，请通过删除在Excel中管理它不必要的列。然后导入Access。
Or, if this is only time you need to perform that task, manage it in Excel by removing unnecessary columns. Then import into Access.
这篇关于将340列导入Access 2010.“如何？”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
