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

x
?
Solved

Import Excel Spreadsheet into Access using SQL...

Posted on 2004-08-25
12
Medium Priority
?
367 Views
Last Modified: 2008-02-01
Hello. I'm trying to import an Excel spreadsheet into Access using SQL. Below is the SQL statement that I'm using... any ideas why it's not working?


SELECT * INTO [TestTable] FROM [Data$] in [EXCEL 8.0;HDR=YES;IMEX=2;DATABASE=C:\Temp\Book1.xls]


The error I get is: '[EXCEL 8.0;HDR=YES;IMEX=2;DATABASE=C:\Temp\Book1.xls]' is not a valid name.


When I use this same type of method with .txt files, I have to create a schema first... is that also necessary for Excel?


WATYF
0
Comment
Question by:WATYF
[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
  • 3
  • 2
  • +2
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 11895830
Good God, don't do it that way.  Use this VBA code line instead...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TestTable", "c:\Temp\Book1.xls", {HasFieldNames: True or False}, {Excel cell range, if anything but the entire sheet}

Hope this helps.
-Jim
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11895893
Jim is right.
There are commands built into access (TRANSFERSPREADSHEET is the best)
Although you can also link the spreadsheet as a table.
0
 
LVL 11

Author Comment

by:WATYF
ID: 11895950
LOL... Good God, I'm working from Excel, not Access.


This is being done entirely programmatically from Excel, not by someone working in an Access database.


The procedure was written (a while ago) to allow the user to upload the file they are currently working on into an Access DB. It currently opens an instance of Access in the background (waste of memory) and uses the TransferSpreadsheet method (the you mentioned above) to import the file,... but I am making improvements to this particular program, and one of those is switching completely to SQL which is more efficient and faster.


I have done this before in the past (quite a while ago), and just need a refresher on the syntax of this particular statement.


WATYF
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 11896017
In that case, it might not be a bad idea to re-post this question in the Microsoft Excel channel.  (After giving danny and me points for moral support...  :)

It might not be a bad idea to export from Excel as a comma-separated text file, then let Access import it as part of its processing.  There could be a lot of probems going from Excel straight to Access, namely table locks, someone changing the Access table definition, etc. etc.

My only comment on SQL would be if your SQL Server is guarded by a Database Administrator (DBA)-type individual, they will likely howl at the idea of letting a super-user export from Excel directly into a SQL Server table.  

Hope this helps.
-Jim
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11896134
When you did this with text files, I believe you linked to the text file using a schema. Then the resulting Access table can be queried appropriately. If you did the same in the instance of Access and linked to the Excel file, the resulting Access table can also be queried appropriately.  However, you cannot query an Excel file in its native Excel directory. It must first be imported or linked. If it is linked, you cannot update the table, and consequently the Excel file because of formulas and other Excel features which make non-excel updates unwise.
0
 
LVL 11

Author Comment

by:WATYF
ID: 11896194
Well... you won't get points for moral support, but you may get them for suggesting the interim step. :o)

This process is a necessary evil unfortunately... I have an Access database that houses tables that are accessed by a UDF in Excel. Most of those tables are built by me (or rather, by a program I wrote which runs on a daily basis). But the users want the ability to upload their own "tables" as they need them. I'm aware of the implications of this, and have gone to great lengths to instruct them on how to "cleanse" their Excel sheets before uploading. It works out OK. Since it's all done through a GUI, they have no way of accidentally corrupting the DB or altering any pre-existing tables.


If I can't get this statement to work, I may have to use an interim step (much like you mentioned above) in which I save a copy of the Excel file as a tab-delimited file and use an SQL statement to import that into the DB. I already have working code that I use to do that (for the tables that my program builds daily). But it requires that I build a schema each time the user uploads a file, and that bring variables into the picture that I'd like to avoid if at all possible...


....which is why I'm attempting to do it straight from Excel in the first place.



WATYF
0
 
LVL 11

Author Comment

by:WATYF
ID: 11896432
well... looks like I figured it out.


SELECT * INTO [TestTable] FROM [EXCEL 8.0;HDR=YES;IMEX=2;DATABASE=C:\Temp\Book1.xls].[Data$]


No schema necessary.


Dang... I could swear I tried that earlier too. Oh well.



WATYF
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 11896498
Cool beans man.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11896578
WATYF: Good work! I've got A2K and the help files are rather skimpy on this subject. This is a good one to keep tucked away.
0
 
LVL 11

Author Comment

by:WATYF
ID: 11896807
Yeah... I'll have the mods PAQ it.

It's a pain, because using SQL directly, instead of relying on the crumbs that Access throws you (i.e. their pre-define methods), just gives you so much more flexibility. There really should be a good place to go to find the breakdown on all the SQL statements that you can use to communicate between Office apps. Standard SQL is easy to find online... but when you get into this weirdness, you don't really have many places to go.


WATYF
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 11934628
Closed, 250 points refunded.
CetusMOD
Community Support Moderator
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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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