Home Forums Programming General Creating Tables from Excel list RE: Creating Tables from Excel list

  • March 1, 2015 at 9:32 pm

    #1781015

    GrassHopper (3/1/2015)

    I have a list in Excel that has a list of about 30 tables with all column names and types. How can I programatically create the tables using this list with the datatypes listed in the excel file ? I thought of importing the list into a table and using a cursor to loop thru the list and build the Create table command, but I've never used cursors before.

    Column_Heading is just a label...use column_name as the column name to be used. Also, the Char data_type could be Varchar.

    This is a sample of the list:

    TABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_HEADINGDATA_TYPELENGTHNUMERIC_SCALE

    AAHHSTHPAAHCMPN1Company number CHAR3

    AAHHSTHPAAHDIVN2Division number CHAR3

    AAHHSTHPAAHDPTN3Department number CHAR3

    AAHHSTHPAAHVNDN4Vendor number CHAR10

    AAHHSTHPAAHPINB5Journal inv/ref numbCHAR10

    AAHHSTHPAAHLNAH6History line number NUMERIC50

    AAHHSTHPAAHIAMG7Invoice amount-grossDECIMAL152

    AAHHSTHPAAHIAMD8Invoice discount amoDECIMAL152

    AAHHSTHPAAHIAMZ9Invoice amount-no dsDECIMAL152

    AAIHSTDPAAICMPN1Company number CHAR3

    AAIHSTDPAAIDIVN2Division number CHAR3

    AAIHSTDPAAIDPTN3Department number CHAR3

    AAIHSTDPAAIVNDN4Vendor number CHAR10

    AAIHSTDPAAIPINB5Journal inv/ref numbCHAR10

    AAIHSTDPAAILNAH6History line number NUMERIC50

    AAIHSTDPAAILNAP7Payable line number NUMERIC50

    You first need to identify the UNIQUE columns for each table for a PK or, at the very least, what the keys will be used for a clustered index on each table. A lot of people will argue against it but in 99.9% of the cases, at least programmers will benefit by correctly identifying a PK and, whether it becomes the PK or not, 99.9% of all tables should have a Clustered Index. Without those things, you don't really have tables.

    Then, look at the datatypes. Do you really need the "number" columns to be character based, do you really need the 9 bytes that Decimal(15,2) will take or can you live with the 5 bytes of Decimal(9,2), do you really need the 5 bytes of NUMERIC(5,0) or can you live with the 2 bytes of SMALLINT and certainly the 4 bytes of just INT.

    I also think that the table and column names are horrific but I'm thing that there's not much we can do there.

    I also recommend that you read and heed the article at the first link under "Helpful Links" in my signature line below if you want any form of coded help.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)