Solved

Mixed Data - Need to pull Values from within the table

Posted on 2011-09-22
5
183 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:SQLwatcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Wizilling
ID: 36583945
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





0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36585861
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

0
 

Author Comment

by:SQLwatcher
ID: 36587515
This looks really good. Now. is there a way to insert a blank row after each USA value?  
0
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 500 total points
ID: 36597411
check below query for blank row
alter table addr add Id int identity(1,1)
go
select * from 
(select '' data,0 id union all
select * from addr 

)tt right outer join 
(select rgbvalue from (
select ID-2 id1,id-1 id2,id id3,0 id0 from addr tA 
where ta.data like '%USA%')p
unpivot
(rgbvalue for iid in (id1,id2,id3,id0 ))
as unpvt)t on tt.id = t.rgbvalue 
go
alter table addr drop column id
go
select * from addr

Open in new window

0
 

Author Closing Comment

by:SQLwatcher
ID: 36816882
Thank you both!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question