Post reply

SQL Server Import and Export Wizard - Writing a SQL Statement, how to link to excel?

  • June 20, 2012 at 12:11 pm

    #258345

    Hi all,

    In the wizard, my source table is an excel worksheet. Instead of choosing to copy data from one or more tables or views, I choose to write a query to specify the data to transfer. But, I don't know how to start and write the sql statement.

    I tried,

    SELECT * FROM abc.xls

    WHERE F7 <> NULL

    F7 is the column name.

    I am not too sure how the syntax works in this situation, please guide me through.

    Thanks,

    Alan

  • June 20, 2012 at 1:03 pm

    #1503650

    this is a working, tested example of openrowset on an excel spreadsheet for a 64 bit installation of SLq server.

    you need to know the name of the spreadsheet in the document...that is the "table"

    if you open your document, and the name is the default of Sheet1, the name is Sheet1$ for the example:

    in my example, the sheet is named "AK", so the driver requires "AK$" for the sheet name

    SELECT * FROM OPENROWSET('MSDASQL',

    'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

    UID=admin;

    UserCommitSync=Yes;

    Threads=3;

    SafeTransactions=0;

    ReadOnly=1;

    PageTimeout=5;

    MaxScanRows=8;

    MaxBufferSize=2048;

    FIL=excel 12.0;

    DriverId=1046;

    DefaultDir=C:\Data\BlockGroups_2010;

    DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

    'SELECT * FROM [AK$]')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply