asked on

Mixed Data - Need to pull Values from within the table

Hi - We have a 7,000 page Excel Spreadsheet containing customer inforamtion that resides in one column. I imported this into SQL Server 2008 and have run select/delete statments to get ride a bunch of garbage. Now, I wish to pull only address information but need to see if anyone has any idea on how I can script this or if there is a free-ware out there that can pull data.

I have attached a sample bogus listing of the table to show what kind of stuff is mixed in. I need the
Customer's Name
Street Name
City, State, Zip

Nothing else. Common point is the USA at the end of the address statement.
Ideally, if I could write a script that would loop and essentially say: Every time USA is found, capture the 3 lines directly preceeding the row with USA displayed....

I thought 3 lines preceeding as Apartments etc are listed on line 3 of the groupings.
Line 1 = Name
Line 2 = Street (PO Box0
Line 3 = Apartment OR is the City State, zip USA row.

Help Please.

I am using SQL Server 2008 R2

AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO 
AAAA, DONALD O 
1244 A STREET  
SOME CITY, AZ 12345 USA 
10867 125-55-9999 
AAA, BUFFY  01/07/1999 Child BDO 
AAA, SLAYER O 02/13/1956 Self BDO

Open in new window

Wizilling

some scripting ideas:

create an identity column in your table. (if you dont have one)

script logic:

insert into #temp (ID )
select ID from your table where column1 like '%usa'

declare cursor
for select id from #temp

loop begin
--- this will give you the 3 lines preceeding
select column
from yourtable
where ID >= @id-2 and ID <= @id

end loop

sachinpatil10d

Try this

alter table addr add Id int identity(1,1)
go
select * from addr where id in (
select rgbvalue from (
select id id3,id-1 id2,ID-2 id1 from addr tA 
where ta.data like '%USA%')p
unpivot
(rgbvalue for iid in (id3,id2,id1 ))
as unpvt)
go
alter table addr drop column id
go
select * from addr

Open in new window

Kathryn S

ASKER

This looks really good. Now. is there a way to insert a blank row after each USA value?

ASKER CERTIFIED SOLUTION

sachinpatil10d

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kathryn S

ASKER

Thank you both!