Solved

How to Import different spreadsheets into a temp table

Posted on 2016-08-18
11
40 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 50

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 50

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 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 49

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 49

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 49

Expert Comment

by:Gustav Brock
ID: 41768225
You are welcome!

/gustav
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now