Solved

How to Import different spreadsheets into a temp table

Posted on 2016-08-18
11
44 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 52

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 52

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 41768225
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

739 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