Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Import different spreadsheets into a temp table

Posted on 2016-08-18
11
Medium Priority
?
46 Views
Last Modified: 2016-08-23
Within MS Access VBA I want to:

Delete a table including its structure called tempTableDailyImport
Import a spreadsheet into tempTableDailyImport

In short I am looking to load a spreadsheet into a temporary table that will reflect the column names of a spreadsheet.  The spreadsheets loaded into this temp table will consistently have different column names with each iteration of the code (will be run on a daily basis), thus I do not want to set up a static table.  

Once the data is loaded into this temp table I have code that will read the column name, etc, however I am struggling on low to load data into a non static table on a daily basis.
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41761909
you probably can create a generic table with field names like: Col1, Col2, Col3... ColN.

and then load your data in.

but since there is no static table, you may consider to load the header as the first record in your generic table.

hope that make sense.
0
 

Author Comment

by:upobDaPlaya
ID: 41761923
Hi Ryan I cant do Col1, Col2, etc because further down in my code I am reading in the column names.  Essentially I want to import a spreadsheet into a temp table...run my code, then delete the table object..

Since each iteration will be a different spreadsheet I can not use a static table nor Field1, Field2, etc.  unless there is a way to read in the column headers..  thx
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41761924
I got some time didn't do programming in Access but think you probably can use ADOX object library to create a table structure in Access.

But before I propose further, I would like to know how big is your spreadsheet file? is that a lot data in it?

can you also provide us a sample spreadsheet here?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 41762083
There is no such thing as a non-static table in Access. Tables can be created and deleted, but that's it.

However, what you need is to link the spreadsheet as a linked table. This will have field names F1, F2, etc.
Then create a simple select query using this table as source where you rename (using alias) those fields to the fieldnames you wish. Also, convert and filter the fields as needed so you have only the useful rows returned.

Now, use this query for your further processing in importing.

/gustav
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 41762233
Agree with Gustav on the concept.

However, when you link the spreadsheet to your database, it will contain valid column headers if there is an entry in the first row of every column being imported.

The challenge is mapping those columns with the column in the table you want to actually import the data into.
0
 

Author Comment

by:upobDaPlaya
ID: 41764715
If I link it to the spreadsheet and then as an example I change all the column names within the spread-sheet will the table within MS Access reflect the changed column names from the spreadsheet.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41764817
No.
But if you don't read the column names, the fields will still be named F1, F2, etc.

Which method to use depends ...

/gustav
0
 

Author Comment

by:upobDaPlaya
ID: 41766289
On a linked spreadsheet how do I tell MS Access not to read the column names
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41766567
It's the third-last parameter:

    Docmd.TransferSpreadsheet , , , , False

Or you can adjust the range you link to not include the row with the column names.

/gustav
0
 

Author Closing Comment

by:upobDaPlaya
ID: 41768127
Thanks this gets me over the hump....thx
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41768225
You are welcome!

/gustav
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question