How to in Notepad++

Hi,

I have a text file that is like so (records are all different), how can I make it so (recursively)
John     Doe     123 Main St     New York     NY     10010     01/01/13     12/31/13     test@test.net

So I can import it into Excel, I am OK with either a Tab or Comma for a delimiter\, what ever is  easier. I tried Text To Columns and that din't work well.


John Doe
123 Main St
New York, NY 10010
01/01/13
12/31/13
test@test.net
John Doe
123 Main St
New York, NY 10010
01/01/13
12/31/13
test@test.net
John Doe
123 Main St
New York, NY 10010
01/01/13
12/31/13
test@test.net
John Doe
123 Main St
New York, NY 10010
01/01/13
12/31/13
test@test.net
John Doe
123 Main St
New York, NY 10010
01/01/13
12/31/13
test@test.net
LVL 3
Computer GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can replace in Notepad++ crlf with tab, but you should skip end of each record:
replaceMay be better to read text file from vba sub?
0
Computer GuyAuthor Commented:
Hi,

Thats good! Is there a way to make it a new row after every record? Say there are 15 records?

Also, is there a way to split the first and last name of the first part John     Doe
so the first is in a column and so is the last?
0
als315Commented:
Try this sample (with autorun, so you should enable macros)
ImportTextFile.xls
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

als315Commented:
If you are sure that there will be no names like "John F Doe" you can split first and last name, but in general it is not possible
0
Computer GuyAuthor Commented:
I like the auto run, though, I need to seperate some data a little more

First and last names

City, state and zip should all be in their own columns
0
Computer GuyAuthor Commented:
That is very minimal, I can clean up a John F Doe
0
als315Commented:
You can find here (on EE) many discussions how to split address and name fields.
Can you look at real data and answer some questions:
1. Are there any 3-part names?
2. Is City always separated with comma from state?
3. Is zip always separated with space from state?
0
Computer GuyAuthor Commented:
There are no 3 part names

City (comma) State (no comma)

City, ST ZIP
0
als315Commented:
Test this sample
ImportTextFile.xls
0
Computer GuyAuthor Commented:
Great!!

State is in Zip field and zip is not shown:

Not sure if I made a mistake, but this is a copied and pasted example.

NEW YORK, NY 10001


Also, where can I add this code to make the fields I specify be ProperCase?

Sub ChangePCase()
' This module will change case of selected cells to Proper Case.
     On Error Resume Next
     Dim MyCell As Range
     For Each MyCell In Selection.Cells
          MyCell.Value = WorksheetFunction.Proper(MyCell.Value)
     Next
     On Error GoTo 0
End Sub
0
als315Commented:
There was error in State and Zip split. Look at new sample.
You can use strconv function in VBA for proper case:
StrConv("test text", vbProperCase)
In my sample it is used in string with City
ImportTextFile.xls
0
Computer GuyAuthor Commented:
Hi,

Two more questions:

The Zip code if starts with a "0", it gets cut off, so 01234 would be 1234

And in your code, where can I specify email address and street address as separate values so I can do the case thing to it?
0
als315Commented:
Look at new sample. I've added text format for Zip and you can see separate elseifs for street and email (i is index of column from left to right: street = 3, email = 9). You can add any format there.
ImportTextFile.xls
0
Computer GuyAuthor Commented:
Not having luck:

                ElseIf i = 3 Then
                    wks.Cells(R, i) = Trim(Str),StrConv(A(1), vbProperCase) 'Street
                ElseIf i = 4 Then
                    A = Split(Str, ",")
                    wks.Cells(R, i) = A(0),StrConv(A(1), vbProperCase) 'City
                    i = i + 1
                    Str = Trim(A(1))
                    A = Split(Str, " ")
                    wks.Cells(R, i) = A(0),StrConv(A(1), vbProperCase) 'State
                    i = i + 1
                    wks.Cells(R, i) = "'" & A(1) 'Zip as text
                ElseIf i = 9 Then
                    wks.Cells(R, i) = Trim(Str),StrConv(A(1), vbProperCase) 'e-mail
                Else
                    wks.Cells(R, i) = Trim(Str) 'Fill values
                End If
0
Computer GuyAuthor Commented:
I also tried to add the date the data was entered ( I did label column 10 for date) :

                ElseIf i = 9 Then
                    wks.Cells(R, i) = Trim(Str) 'e-mail
                ElseIf i = 10 Then
                    wks.Cells(R, i) = Date    'Date Added
                Else
0
Computer GuyAuthor Commented:
Actually, I think I figured it out, the case portion. Can you please take a look at my file and see if I did it right, I tried adding A = Split(Str, "") at the beginning of the fields I wanted to change case too, is there a better way?

Also, stuck on the date :-(
ImportTextFile.xls
0
als315Commented:
If string has @, new line is started, so  i never will be equal 10. Date should be added after line when @ is detected. Case seems correct and test with names and city in lowercase were converted to proper case
ImportTextFile.xls
0
Computer GuyAuthor Commented:
Cool,

1. Let me ask this, where I have:

ElseIf i = 3 Then
                A = Split(Str, "")

For instance to go to the next line, would that also trim it in the case of there being a random space for the first character on the next line?

2. Why is a new line started if there is an @?

3. Point me in the right direction and I will try to figure it out: Ok, so my address come in sometimes as rd, road, Rd, Road, ave, avenue, etc.....

What is the best way for me to abbreviate any instance of road to Rd and the list goes on for types?

Also, sometimes there could be an apt #b9, what is the best way to format those properly? Again, please let me try so just give me an idea of where to start?
0
Computer GuyAuthor Commented:
And thank you by the way :-)
0
als315Commented:
1. I don't see any reason to have Split, especially without delimiter, for Street. You can use Split function if you like to split delimited parts of a string. Trim is used if you can have some extra space characters.
This part of code could be:
ElseIf i = 3 Then
                    wks.Cells(R, i) = StrConv(Trim(Str), vbProperCase)      'Street
For i = 9 (e-mail) you can also remove split:
ElseIf i = 9 Then
                    wks.Cells(R, i) = StrConv(Trim(Str), vbLowerCase)       'e-mail

2. Line with e-mail is the last line of block in your text (and we can find it if there is @ in the string), so there new block and new row is started.

3. It is very difficult question. Sometimes it is absolutely impossible to find all posiible variants. May be if you can show possible cases and expected results we can find any idea
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.