Text field into Columns

[jpaokx]

Hello,

Is there a function or query that I can split a field into different columns?
For example, I have First Name, Last Name, Address. But I want to split the address field into Address1, Address2, Address3, Address4 as the initial Address field has a lot of characters with commas e.g. 11 London Road, Liketown, Likeshire, London. So, I want to be Address1: 11 London Road, Address2: Liketown, Address3: Likeshire, Address4: London.
Any ideas?

I thought that I can export the field using a simple query and then re-import it with using the text field into columns option, but it is time consuming.


Thanks

 

[jdraw]

Many people have spent entire careers on this topic. Cleansing names and addresses is not simple especially when there is total inconsistency from record to record regarding punctuation/format in the incoming data.

Does your data have consistent format? Do all records have Address1 thru address4?

It can be an excellent learning tool and, as long as you are not expecting 100% accuracy, can be "effective".

As with most data formatting and cleansing, the closer to the source of the data as possible the better. Look for patterns; look for records with common structure and process them as a group... Do the work in manageable chunks, not all at once.

Work on getting the input into a proper/standard format for new data.

You will learn a lot about Right(), Left(), Mid(), InStr().
Good luck.

 

[plog]

I thought that I can export the field using a simple query and then re-import it with using the text field into columns option

One of my side jobs is compiling mailing lists for junk mail campaigns (the lord's work as I call it) from varying source files . I always get a bunch of files that have Names and addresses compressed into one field. I think exporting/importing is your best bet.

First, I wouldn't numerate the field names all the same (Address1, Address2...) I would name them appropriately. I'm American, so I don't have the frame of reference for exactly what Liketown, Likeshire and London exactly are, but I would name them appropriately (i.e. burrough, township, city, or whatever the Queen uses).

In the US we StreetAddress1, StreetAddress2, City, State, PostalCode. So let's use that as the final fields and this as the starting data:

AddressData
"123 Main Street, Washington, DC, 20030"
"456 Broadway, #7, Las Vegas, NV, 70081"
"789 Fifth Street, East, Room 74, Chicago, IL, 60090"

First, export that as a space delimited text file (.txt) without using any kind of quotes around the fields. Exporting space delmited is key because your data really is comma delimited (a lot of what follows is predicated on that, so I hope you stated that correctly). Next, import that file using a comma as a text delimiter and brining all the field in as strings/text. Your data should come in like this:

Field1, Field2, Field3, Field4, Field5, Field6
"123 Main Street", "Washington", "DC", "20030", "", ""
"456 Broadway", "#7", "Las Vegas", "NV", "70081",""
"789 Fifth Street", "East", "Room 74", "Chicago", "IL", "60090"

To that table add text fields for all the fields that you ultimately need (for this US example its StreetAddress1, StreetAddress2, City, State, PostalCode) plus an additional text field called "Completed". Next bring the table into a query, bring down all the "Field" tables and then all your properly named fields, "Completed" and a calculated field using this:

Finished: "X"

Set the criteria under "Completed" to Null and run the query. Every "Completed" is Null so you see everything. Starting in Field6, filter it so that it only shows records that are null. Then go to Field5 and filter the same, then Field4 etc. until you get no results back. Once you have no results, back up a step and remove the Null filter on the last Field you set. Those are you're first set to copy to the proper columns.

Using my sample data, I would filter Field5 and Field6 to null and my query would look like this:

Field1, Field2, Field3, Field4, Field5, Field6, StreetAddress1, StreetAddress2, City, State, PostalCode, Completed, Finished
"123 Main Street", "Washington", "DC", "20030", "", "", "", "", "", "", "", "", "X"

Then, I would copy and paste all the columns of Field1 to StreetAddress1, Field2 to City, Field3 to State, Field4 to PostalCode and finally Finished to Completed which will mark that I have done that row of data. After that, you clear your filters and start the process of filtering by columns again.