Post reply

Importing an Excel file

  • May 4, 2007 at 5:28 pm

    #173250

    Hello all,

    I have been tasked with importing several Excel files into SQL 2000 tables.

    I normally don't have a problem but these specific files are formatted differently.

    Example:

    The first row has the column names, there are 20 or more columns.

    The next row contains the company name, street address and other info.

    The next row contains information that applies to the previous company like a doing business as name as well as city, state and zip.

    CompanyName

    Owner

    Address

    May Day Crafts

    Mike Adams

    1501 Hwy

    Dba Krafts for Kids

     

    Westchester  AL ostalCode w:st="on">95223 2415ostalCode>

     

     

     

     

    As you can see in the table above all the data belongs to one company. I am looking for a syntax or script that would help me to parse this into one row.

    There are over 18,000 rows in this excel file.

     

    Thanks

     

    Gary

     

  • May 6, 2007 at 11:27 pm

    #704569

    Import the data from excel into permenant temp tables and then write your own tsql based on your needs to parse....

    How to import data from Excel to SQL Server

    http://support.microsoft.com/default.aspx/kb/321686

     

    MohammedU
    Microsoft SQL Server MVP

  • May 7, 2007 at 8:04 am

    #704642

    I'm not sure this is the best way, but here goes. This is dependent on your data being exactly as you state above where each record has 2 rows! First backup your data in case you find more fun stuff in this file!  In excel create a new column that is a row id - make sure it is a continuous counting id (1,2,3,4 ...) No blanks or repeats! Then create another new column and use the formula =MOD(A2,2) . Copy that all the way down. This will give you a 1 or 0 depending on if the row is odd or even.

    RowID ModID CompanyName Owner Address
    1 1 May Day Crafts Mike Adams 1501 Hwy
    2 0 Dba Krafts for Kids   Westchester  AL 95223 2415
    3 1 Brians SQL Brian

    1234 home

    4 0 Brians SQL shop   Salt Lake City Utah, 84121

     

    Then do an filter on the data to select either only the 0 rows or 1 rows. Cut and paste the filtered set to a new excel sheet. Then create a new row id column that is a counter in each of the spreadsheets. In essence you have pulled out every other row, then you will use a row id as the primary/foreign key to link the 2 back together.  Now import the 2 sheets as different tables into SQL Server. Join the 2 tables by the last row id that you created. And walla - crap data into something good.

    This is not very fancy, but it is quick and easy - good luck.

    Brian

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

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