May 4, 2007 at 5:28 pm
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 | ||
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.
Thanks
May 6, 2007 at 11:27 pm
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
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 |
| |
| 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