Results 1 to 2 of 2
  1. #1
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Import Excel sheet with query

    I import an Excel sheet with the following SQL statement.

    SELECT T1.*, 1 AS SheetSource INTO [Excel Import]
    FROM (Excel 12.0;HDR=NO;IMEX=1;Database=C:\Access2007\ODBC-Writeback\SFtest.xlsx) [TCM IMPORT - SF$A10:L65536] AS T1;

    This works except that it also bings in any 'blank' cells that were in the Sheet. The first try brought in 24 valid records and 200+ blank records.
    I created an additional query to delete all of the blanks but it would be nice to take care of it on import.
    I have tried to add a Where clause for A10 Is Not Null but get a Syntax error with the cursor positioned on the semi-colon before the HDR paramenter.


    Any help is much appreciated.

  2. #2
    Join Date
    May 2010
    Posts
    339
    You could try setting a second primary key with the index set to yes with no duplicates. That would throw out all the empty records for you. See sample.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing full excel sheet to access
    By karakal in forum Import/Export Data
    Replies: 0
    Last Post: 03-22-2010, 03:48 PM
  2. Replies: 2
    Last Post: 07-15-2009, 04:08 AM
  3. Replies: 0
    Last Post: 02-21-2008, 09:52 AM
  4. Importing Excel Sheet into Access dbase
    By tonystowe in forum Import/Export Data
    Replies: 0
    Last Post: 12-08-2006, 11:35 AM
  5. problem setting Excel sheet name in vba from a form
    By dataman in forum Programming
    Replies: 2
    Last Post: 04-18-2006, 07:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums