Solved

How to Import different spreadsheets into a temp table

Posted on 2016-08-18
11
38 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 49

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 49

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

8 Experts available now in Live!

Get 1:1 Help Now