Post reply

How to create a table which has same column structure as the excel

  • April 6, 2011 at 9:01 am

    #145482

    Hi,

    I want to create a table in database dynamic once we have the excel. Is it possible to create the table if we only know the excel name? because we might have many excel file with different column structure, I hope I can create one script to create the table in DB dynamincally. Really appreciated if you can share anything with me. Thanks a lot

    Thanks

    Lindsay

  • April 6, 2011 at 9:24 am

    #1308061

    You can use OpenRowset to query all the columns of an Excel worksheet, and use that in the From clause of a Select Into statement. That will create the table on the fly from the Excel structure.

    Look up "Select Into" and "OpenRowset". Since you'll be querying Excel, you'll also want to search "IMEX" online, with regard to importing from Excel, because otherwise it'll trip you up somewhere along the way and you'll lose data. Search those three things, and you should be good to go.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 6, 2011 at 9:52 am

    #1308085

    Thanks a lot GSquared.

    Do you mean that we should use the openrowset with excel provider? Could you please give me a sample? Thanks a lot:)

    Thanks

    Lindsay

  • April 7, 2011 at 2:12 am

    #1308382

    I have found the way to use openrowset. Thanks a lot:)

    Thanks

    Lindsay

Viewing 4 posts - 1 through 3 (of 3 total)

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