0

I have a series (thousands and thousands) of call record that I'm trying to create a spreadsheet of. They're all in a text file. The format looks like this:

12/ 13/ 05 Syracuse, NY 10: 22 AM 111- 111- 1111 2 $ - $ - $ -
12/ 13/ 05 New York, NY 10: 28 AM 111- 111- 1111 (F) 2 $ - $ - $ -
12/ 13/ 05 Orlando, FL 10: 48 AM 111- 111- 1111 (F) 4 $ - $ - $ -

3/ 9/ 09 Internal 4: 51 PM 111- 111- 1111 (E) 23 $ - $ - $ -
10/ 14/ 11 Colorado Site 8: 12 AM 111- 111- 1111 14 $ - $ - $ -
1/ 3/ 12 Dept 27 3: 16 PM 111- 111- 1111 (F) 93 $ - $ - $ -

11/ 12/ 12 Internal 3: 13 PM 18765 (E) 16 $ - $ - $ -
11/ 14/ 12 Internal 11: 43 AM John Doe 3 $ - $ - $ -

Month/ day/ year/ city called, STATE HH: MM APM 123- 456 7890 OptionalCode $Charge $Tax $Total

This is, minus details, directly from the file. No quotes around strings, no tabs. I tried to use text to columns, but some cities do have space and others don't.

Anyone want to point me in the right direction? RegEx maybe (Which I've heard of but never used)? Something else?

Update: Thanks for the early feedback. The line are actual data from my file, though I stripped city and phone numbers. I've updated with the city information to show variance there. To the best I can see, none of the city names have a comma, but I'm dealing with close to 120,000 lines total and, obviously, haven't checked them all.

The city won't always, obviously, have a space - Syracuse above doesnt, New York, however, does. The month and date, too, aren't always 2 digits - which also throws off checks for length. I can read to first, then second forward slash, though - those are fixed after date and month values.

And the bracketed code doesn't always appear... sometimes it's there, sometimes not, though they do appear to only ever be one letter when they arrive.

I hope this clears a few things up. This would have been far easier if it was stored correctly in the first place. Sigh.

Updates 2,3 & 4 Added a few lines from call log changes per Robin's request.

Improve this question
3
  • 1
    Any City Names have comma's in them? If not and if the date, time and phone is fixed length, you could iterate through each line breaking it into smaller chunks :P Commented Jan 26, 2013 at 0:25
  • 1
    Could you please provide several actual lines from SCV? 2-3 will be enough. Commented Jan 26, 2013 at 0:26
  • The lines themselves above are, minus city and phone number data, actual lines from my source file. I will update to clarify a few things, though. Commented Jan 26, 2013 at 2:22

2 Answers 2

Reset to default
3

I know you asked for a VBA solution, but I do my call record parsing purely in a spreadsheet with formulae.

I have uploaded a workbook solution here (version 3).

Once you have the workbook open, copy and paste the contents of your text file into cell A2. Then fill down the range B2:X2 as far as necessary.

The formulae will work with any variation in length of month, day , year, city, state, time, code, charge, tax and total.

Let me know if any lines break. You can easily check for these by using the AutoFilter dropdown in the headers to select for errors/extraneous values. Append any offending lines to your question.

Updates:

Version 2 takes care of the situation where the City field contains a location name, and the State field is blank.

Version 3 takes care of the situation where the Phone Number field contains an extension number or name.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I'm fine with a spreadsheet approach,I can spit it out into a proper CSV this way, at least, and then I CAN deal with it in code. Thank you SO much. I am updating with a single broken line format. Looks like things changed slightly in 2009 and not all city names then had a comma.
@ZacharyR - Do all the broken lines have ONLY one word between the date and the time?
No, actually - good catch. Most of them do... But a few starting in 2001 do switch it up to 2 words. None have more than two, though some have numbers. I'll add a few more broken lines.
@ZacharyR - New version uploaded. New link is in the answer.
To the best I can tell, this is flawless. Thank you. Take the rest of the day off.
|
1

Something like this might work if there are no comma's in the city name.

Sub foo()
    thisLine = "12/ 13/ 05 City Name, ST 10: 28 AM 111- 111- 1111 (F) 2 $ - $ - $ -"
    thisDate = Mid(thisLine, 1, 10)
    thisLine = Mid(thisLine, 12)
    firstComma = InStr(1, thisLine, ",")
    City = Mid(thisLine, 1, firstComma - 1)
    thisLine = Mid(thisLine, firstComma + 2)
    State = Left(thisLine, 2)
    thisLine = Mid(thisLine, 4)
    thisTime = Left(thisLine, 9)
    thisLine = Mid(thisLine, 11)
    thisPhone = Left(thisLine, 14)
    thisLine = Mid(thisLine, 16)
    tempArray = Split(thisLine, "$")
    If UBound(tempArray) = 3 Then
    optionalCode = tempArray(0)
    charge = "$" & tempArray(1)
    tax = "$" & tempArray(2)
    Total = "$" & tempArray(3)
    Else
    ' throw an error something went wrong
    End If
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.