CSVed noob

[98yanks]

I am not very savvy when it comes to software and I am trying to split an address column using CSVed. The current column has the address number as well as the street name and i want to split them into there own columns. Can someone please tell me how to do that. I could not find it in the help section of the software or the developers site. Any advice would be greatly appreciated.

[alanbcohen]

I am not very savvy when it comes to software and I am trying to split an address column using CSVed. The current column has the address number as well as the street name and i want to split them into there own columns. Can someone please tell me how to do that. I could not find it in the help section of the software or the developers site. Any advice would be greatly appreciated.

I don't know how to do it with CSVed, but I've done this type of task many times using a simple text editor. The key to doing this is to determine which type of CSV file it is (not hard, open it in Notepad or another editor - I use Notepad++ - and look at it).
Most CSV files have the following format:

[data1] [delimiter] [data2] [delimiter]...[last data]<line-delimiter>
second row as data, same as the first...

The delimiter may be any character but common ones are the comma, the semicolon, and the tab. If the data items are character or mixed character and number, it is usually surrounded by double-quotes.

Okay, now we know how the data is formatted, we can start with a COPY of the file (just in case!). Find the address data item
(like "123 Broad Way") and change it to "123","Broad Way" (changing the space between the number and the street to [","] (all three characters).

One thing to watch out for are any rows of data without a street number; make sure to insert a space in place of the number (better than leaving it empty, that is called a NULL value is generally regarded in database worlds as a horror!).

One other thing, many CSV files have heading titles on the first row to label what the column contains. Don't forget to insert a heading for the "Number", if you have headings in your file.

Hopefully this helps you to understand what you are doing, even if someone else tells you how to do it in CSVed.

(Edited for minor spelling and grammer corrections.)

[98yanks]

Thanks but the data sheet i'm working with has over 80,000 addresses so splitting each item individually would be impossible.

[usdcs]

Hi 98yanks,

I usually use a spreadsheet program for jobs such as this. You might want to try Spread32 which is in the database. You can locate the last freeware version here. I like this program so much that I registered it. The homepage is here.

Note that Spread32 has a limit of 65536 rows. Therefore, you will have to split your file to work on it. I will assume that you know how to do that.

Let's assume that you have a copy of Spread 32, and that you have a blank worksheet open.

In Cell A1 type, or copy this code, into it:

Code: Select all

123 East Main Street

In Cell A2:

Code: Select all

=SEARCH(" ",A1,1)

In Cell A3:

Code: Select all

=LEFT(A1,SEARCH(" ",A1,1)-1)

In Cell A4:

Code: Select all

=MID(A1,SEARCH(" ",A1,1)+1,40)

Now, I'll explain.

Cell A1 contains a sample street address that contains the house number followed by several words, all separated by spaces. I am assuming that all of your addresses - or most of them, anyway - are constructed like this.

Cell A2 contains the formula that will tell us the position of the first space. That is, the position of the space following the house number. We'll call it N. You won't need this in your address file. It is included here for illustration only.

Cell A3 uses the formula the we have in A2. It copies the leftmost N-1 characters from Cell A1.

Cell A4, similarly, starts with the N+1 character, and copies 40 characters to it. (I am making an assumption here. Adjust the number 40 to suit your data.)

Hopefully, you can follow the logic. There also may be easier ways to do this, too. But, here is one way, at least.

To apply this in your file,
1. Open a copy of your file in Spread32.
2. Depending upon the organization of your data, you can either insert a couple of columns, or append some columns that will contain these formulas.
3. Enter the formulas and copy them down a few rows to ensure that they are working as expected.
4. Copy the formulas to all rows.
5. Check the results.
6. Optional:
6a. Highlight each column that contains the formulas and pick Edit -> Copy.
6b. Pick Edit -> Paste special -> Values -> OK
(This will replace the formulas in each cell with the calculated results. the formulas will be gone.)
7. Pick File -> Save As...
8. In the Save as type: box, select Comma Separated Values, if you want to save it again as a .csv file. Otherwise, you can save it as an Excel file, if you wish.

After you complete this process on the two halves of your file, you can join them together again.

I hope that this was clear to you.

Best wishes,
Dan

[alanbcohen]

Just an addon to usdcs's great response (THANKS for the formula - a new way to do a repetitive task!). If you use OOo, the cell formula for the 'finding the first space', the formula would be:

Code: Select all

=FIND(" "; A1; 1)

And you need to adjust the other formulas to reference the position returned in the 'FIND' cell.