data manipulation with macros

[mtntgr]

Hello,

I have a program that pulls addresses from online listings, and exports them (csv) as -
5-590 Highway 33 West, Kelowna, BC V1X 6A8, Canada (into 1 column)
(Usually using on USA locations, but this example raises additional question.)

Unfortunately, I have to import them into an xls file in the following format -
STREET ADRRESS | CITY| ST | ZIP |
(i.e.- 4 columns).

Is there any way (macro, etc...) to automate doing this?
Bonus Question - And stripping off Country if necessary?

Using Excel 2003 Professional.

Thanks,
Ken

 

[Joe4]

If you open a CSV in Excel, it will separate the data into mutliple columns, like you want. You can delete any columns you do not want.

If you use the Macro Recorder and record yourself performing these steps manually once, you will get most of the VBA code you need to create a macro to do this.

 

[mtntgr]

Hi Joe4,

The following assumes I fully understand your solution.
(I assume I do.)

Unfortunately the program I'm using (otherwise pretty good) generates the address as -
one cell in the csv/tsv format.
e.g.- "street,city, zip code"
e.g.- 1193 Liberty St SE # 200, Salem, OR 97302
Not as separate cells.

I know how to record macros, but the problem is (imho) -
this requires a wildcard type of solution.
As there is every address is a little bit different.
i.e.- number of digits, Street vs St, etc...

If I save it (the output) as a txt file, I get -
"Company","Address","Webpage","Phone","E-mail","m.domain",".mobi","Wap"
Vick & Glantz Llp: Glantz Rick J,"201 Ferry St SE # 400, Salem, OR 97301","http://vickandglantz.com/","(503) 581-6333","zv7qk6525q4@networksolutionsprivateregistration.com","No","No","No",
So it appears that the fields are defined in this case by being in quotes.
e.g.- "Vick & Glantz Llp: Glantz Rick J","201 Ferry St SE # 400","Salem","OR 97301","http://vickandglantz.com/","(503) 581-6333","zv7qk6525q4@networksolutionsprivateregistration.com","No","No","No",

For a short while I thought I had it, by opening as text file, then -
redefining the fields
("BUSINESS NAME","STREET / AVE. / SUITE","CITY","ST","ZIP","WEBSITE","PHONE","EMAIL","m.domain",".mobi","Wap")
and then
find and replacing "space with ","
That worked, but due to irregularities with address formatting, I still have to eyeball results and hand adjust (quite a bit).

I'm thinking my best (only?) bet is to talk the developer into changing the output...

Thanks for your help.

If you open a CSV in Excel, it will separate the data into mutliple columns, like you want. You can delete any columns you do not want.

If you use the Macro Recorder and record yourself performing these steps manually once, you will get most of the VBA code you need to create a macro to do this.

 

[Joe4]

I assume from your response that you are not familiar in working with CSV files in Excel. If Excel KNOWS it is a CSV file, when you open it Excel, Excel will AUTOMATICALLY separate the entries into multiple columns (using the comma to indicate each new cell).

The file that is output from your "other" system, does it have a "CSV" extension? If not, change it/give it one.

Now, go into Excel and open that CSV file. Excel should automatically parse it for you.

 

[mtntgr]

I assume from your response that you are not familiar in working with CSV files in Excel.
I am fairly familiar with csv.

If Excel KNOWS it is a CSV file, when you open it Excel, Excel will AUTOMATICALLY separate the entries into multiple columns (using the comma to indicate each new cell).
No its not doing that, I assume because of the header info setup?
(and commas are also used inside of the "address" column to define BOTH the parts of the address as well as the column itself?
i.e.-
This what I see when I download it as a text file -
"Company","ADDRESS","Webpage","Phone","E-mail","m.domain",".mobi","Wap"
Which explains to me why the csv displays as -
Address
201 Ferry St SE # 400, Salem, OR 97301
rather than the desired -
STREET ADDRESS | CITY | ST | ZIP ("|" indicating columns)

The file that is output from your "other" system, does it have a "CSV" extension? If not, change it/give it one.
Yes they are csv (the other option being txt).

Now, go into Excel and open that CSV file. Excel should automatically parse it for you.
Afraid its not, which is why I wrote in.

So I still require a workaround for the non-standard output...
And if I try to redefine the headers, it doesn't always work -
"Law Office of Roger K. Evans P.C." "675 Church Street Northeast Salem
e.g. - "Law Office of Roger K. Evans P.C."
In this case "P.C." ends up in the "Address" column, rather than being part of his name.

Note: The open file dialog that excel gives doesn't offer any options that give me very usable csv results...

Thanks

I assume from your response that you are not familiar in working with CSV files in Excel. If Excel KNOWS it is a CSV file, when you open it Excel, Excel will AUTOMATICALLY separate the entries into multiple columns (using the comma to indicate each new cell).

The file that is output from your "other" system, does it have a "CSV" extension? If not, change it/give it one.

Now, go into Excel and open that CSV file. Excel should automatically parse it for you.

 

[mtntgr]

I don't think I' doing a very good job of describing the issues (and the desired outcome).

Software I'm using (an address scraper) is outputting a csv file.
Excel has no problem parsing that file, but due to peculiarities of the scrapers output,
the data isn't in a state that would allow immediate input into the next stage.

Next stage being a fairly sophisticated xls with semi-automated functionality,
that does all sorts of further data massaging.
That massaging allows several outputs to be created -
push 3 buttons for 3 outputs.
One is for a mailmerge using Thunderbird, and 2 for other functions.

Because I could be processing 100-1000s of leads this way, I naturally wanted to "bridge the gap" between initial output and required input to "data massager".

On the bright side, I've discovered I can get started with just pasting in the state/province/county name (which is always the same for each run), and I'll worry about the rest later.

Thanks!