Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting A Character (2000)

    I have a text file that contains last name, first name, middle initial, ssn (etc) that I want to import into Excel. The problem I'm having concerns the text file itself for the reason that not everybody has a middle initial for either one of two reasons: 1. They don't have a middle name or 2. their first and last names are too long for a middle initial to appear in the middle initial column.

    My work-around for this is to manually add a period preceeding the ssn, ensuring there is a space between the period and the ssn. I then recorded a macro in Excel to import this text file and everything works great - first names in the first names column and so on. Of course there is now a period in the middle initial space for those who did not have middle initials.

    Question: Is is possible to add code to my existing macro that will search for the periods (which will be in column C) and then remove them leaving a blank cell? If so, I would appreciate all help with this.

    BTW - this Excel file will eventually be imported into an Access database.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting A Character (2000)

    I use the following macro to clean up cells containing only spaces. It works with the selection, but it isn't hard to make it work with a specific range instead of the selection.

    Sub CleanSpaces()
    Dim aCell As Range
    On Error GoTo ExitHandler
    Application.ScreenUpdating = False
    For Each aCell In Selection
    If Trim$(aCell.Value) = "" Then aCell.ClearContents
    Next
    ExitHandler:
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    You could use something like this:

    <pre> Worksheets("Sheet1").Range("C:C").Replace What:=".", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Thanks, Hans!
    That works like a charm. All I added was:

    Columns("C:C").Select

    right before the "For Each ..." statement.

    But let me ask you this - what do the ScreenUpdating statements do? Does that prevent anything from happening on the screen while the code is running? And should I call that at the beginning of the macro as opposed to just before my select statement? (Meaning, would it be more proper to have it earlier since everything I want to do appears to do what I want it to do.)

    Thanks for your help.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deleting A Character (2000)

    Typically you would dim first, then Hans added the on error goto to create the error handler. Then the turn screen updating off, The error handler will make sure that the acreen updatiing gets turned back on if you get an error when the code runs.

    Yes it stops excel from redrawing the screen.

    Instead of selecting column C and then looping in the selection, you could just use:

    For Each acell In Columns("C:C").Cells

    and avoid selecting anything. It is a good practice to avoid selecting in a macro, it only slows down execution and is usually not required.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Hi, Steve.
    Thanks for the pointer. I made the change in the For Each ... statement but you said something else that has me wondering about something else I'm doing with this macro.

    The text file I'm importing has a whole bunch of header garbage on the first page and at the foot and head of each consecutive pages. The data I want doesn't start until line 75 so when I recorded the macro, I told it to start on line 75. (duh!) and selected which columns to skip. Then I selected the garbage at the foot and head of each page (each selection consists of 24 lines) and then deleted them. The footer of the text file has 16 lines of useless information, too. After the macro runs, I just manually delete the footer garbage before importing into Access.

    There is always a blank line both before and after the valid data. So now my question is: Is it possible to write the macro to delete the garbage and keep the valid data without doing the selecting as I did? I've attached a stripped version of the file in question so you can see the garbage I'm talking about.

    Thanks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Deleting A Character (2000)

    Several approaches depending on how much time you want to spend coding vs how often you manually do it:
    You could write a macro which reads the text file line-by-line
    It would ignore everything up til the line with all the dashes, it would ignore this and the "blank line" then start reading in the next lines, when it came to a line with an asterisk, it would stop reading and begin ignoring again, etc

    When it read a line a value you could have a routine to parse the line (though I notice that the "period" you added makes the lines not line up which could make this more challenging to parse though I guess you could just break at the spaces (and ignore multi-spaces)

    You could import as you have done and then manually remove the items. One way to do it would be to
    1) add a new column that just filled in 1 - whatever. This is the column to sort to get back the original order.
    2)Then sort by the text. all the "bad groups should be together and
    3) you could just manually delete them enmass

    If you had someting to "filter by" you could do an autofilter to delete the items or even use adv filter to copy a subset of "good data"

    You could also import as you have done and create a macro to do the other cleaning up.

    The advantage of doing it manually is that you can confirm what is good and bad incase you get a file that is setup "weird" or atypical. But if you do it frequently this can get annoying to keep redoing it.

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    If you don't put the periods into the file, the macro below will load the file into Sheet1 of the active workbook starting in cell A1. It will also convert the names to PROPER case.

    <pre>Public Sub LoadFiles()
    Dim strLine As String, strWk As String
    Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
    Dim vFileToLoad As Variant
    vFileToLoad = Application.GetOpenFilename(FileFilter:="Text Files,(*.txt),All Files,(*.*)", _
    FilterIndex:=1, Title:="File to laod")
    If vFileToLoad = False Then
    MsgBox "No file to laod."
    Exit Sub
    End If
    I = 0
    iFileNum = FreeFile
    With Worksheets("Sheet1").Range("A1")
    Open vFileToLoad For Input As #iFileNum
    Do While Not EOF(iFileNum)
    Do While (Not EOF(iFileNum) And (Left(Trim(strLine), 18) <> String(18, "-")))
    Line Input #iFileNum, strLine
    Loop
    If EOF(iFileNum) Then Exit Do
    Line Input #iFileNum, strLine
    If EOF(iFileNum) Then Exit Do
    Do While Not EOF(iFileNum)
    Line Input #iFileNum, strLine
    strLine = Trim(strLine)
    If strLine = "" Then Exit Do
    strWk = Left(strLine, 18)
    .Offset(I, 0).Value = Application.WorksheetFunction.Proper(Left(strWk, InStr(strWk, ",") - 1))
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ","))) & " "
    .Offset(I, 1) = Application.WorksheetFunction.Proper(Left(strWk, InStr(strWk, " ") - 1))
    .Offset(I, 2) = Application.WorksheetFunction.Proper(Trim(Right(st rWk, Len(strWk) - InStr(strWk, " "))))
    strLine = Right(strLine, Len(strLine) - 20)
    .Offset(I, 3) = Trim(Left(strLine, 9))
    strLine = Right(strLine, Len(strLine) - 10)
    .Offset(I, 4) = Trim(Left(strLine, 1))
    strLine = Right(strLine, Len(strLine) - 2)
    .Offset(I, 5) = Trim(Left(strLine, 5))
    strLine = Right(strLine, Len(strLine) - 6)
    .Offset(I, 6) = Trim(Left(strLine, 3))
    strLine = Right(strLine, Len(strLine) - 4)
    .Offset(I, 7) = Trim(Left(strLine, 2))
    strLine = Right(strLine, Len(strLine) - 3)
    .Offset(I, 8) = Trim(Left(strLine, 10))
    strLine = Right(strLine, Len(strLine) - 11)
    .Offset(I, 9) = Trim(Left(strLine, 10))
    strLine = Right(strLine, Len(strLine) - 11)
    .Offset(I, 10) = Trim(Left(strLine, 2))
    strLine = Right(strLine, Len(strLine) - 3)
    .Offset(I, 11) = Trim(Left(strLine, 2))
    strLine = Right(strLine, Len(strLine) - 3)
    .Offset(I, 12) = Trim(Left(strLine, 2))
    strLine = Right(strLine, Len(strLine) - 3)
    .Offset(I, 13) = Trim(Left(strLine, 1))
    I = I + 1
    Loop
    Loop
    Close #iFileNum
    End With
    End Sub
    </pre>


    One further refinement might be to convert the SSN to a text string with the dashes if that is the way you want it. As the macro is now, the SSN will end up being a number.
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Hi, Legare!
    Thanks for responding. I think I like your way better than the way I did it but I have a few problems.

    I inserted the code into a new module and when I ran it, the "browse for a file" window opened - which is good. But after I selected the text file, nothing happened. I ran it again, and this time I did not select a file and I got the "File not loaded" error message - which is good. I understand what happens up to that point but I don't see why the file is not actually making it into the sheet.

    It looks like (if I'm reading it correctly) that the .Offset() lines are bringing in each column of the file. I only need last name, first name, middle initial, ssn, gndr, and dob.

    The Navy does everything in all caps so I have to leave it in the all caps format - not proper case. Sorry.
    Also, the ssn does need to be left as (or converted to) text because leading zeros get lost when it is a number. This excel file is going to be imported into an Access database where the ssn field has an input mask with dashes.

    Again, I thank you for your help with this. This looks like it will be more "idiot-proof" than what I was trying to do.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    The only reason that I can see that the program would do nothing would be if the file you selected to load did not look exactly like the one you attached to your earlier message. The macro looks for a string of 18 dashes at the left of a line in the input file as a trigger to start loading records into the worksheet. If it does not find that it would do exactly as you describe. If that doesn't help you find the problem, then could you upload a copy of some of the actual file you are using with the names, SSN, and any other sensitive info changed. The macro works for me using your original file with the periods deleted.

    The macro below has been modified to leave the names in all upper case, and load only the fields you said you wanted.

    <pre>Public Sub LoadFiles()
    Dim strLine As String, strWk As String
    Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
    Dim vFileToLoad As Variant
    vFileToLoad = Application.GetOpenFilename(FileFilter:="Text Files,(*.txt),All Files,(*.*)", _
    FilterIndex:=1, Title:="File to laod")
    If vFileToLoad = False Then
    MsgBox "No file to laod."
    Exit Sub
    End If
    I = 0
    iFileNum = FreeFile
    With Worksheets("Sheet1").Range("A1")
    Open vFileToLoad For Input As #iFileNum
    Do While Not EOF(iFileNum)
    Do While (Not EOF(iFileNum) And (Left(Trim(strLine), 18) <> String(18, "-")))
    Line Input #iFileNum, strLine
    Loop
    If EOF(iFileNum) Then Exit Do
    Line Input #iFileNum, strLine
    If EOF(iFileNum) Then Exit Do
    Do While Not EOF(iFileNum)
    Line Input #iFileNum, strLine
    strLine = Trim(strLine)
    If strLine = "" Then Exit Do
    strWk = Left(strLine, 18)
    .Offset(I, 0).Value = Left(strWk, InStr(strWk, ",") - 1)
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ","))) & " "
    .Offset(I, 1) = Left(strWk, InStr(strWk, " ") - 1)
    .Offset(I, 2) = Trim(Right(strWk, Len(strWk) - InStr(strWk, " ")))
    strLine = Right(strLine, Len(strLine) - 20)
    .Offset(I, 3) = Trim(Left(strLine, 9))
    strLine = Right(strLine, Len(strLine) - 10)
    ' .Offset(I, 4) = Trim(Left(strLine, 1))
    strLine = Right(strLine, Len(strLine) - 2)
    ' .Offset(I, 5) = Trim(Left(strLine, 5))
    strLine = Right(strLine, Len(strLine) - 6)
    ' .Offset(I, 6) = Trim(Left(strLine, 3))
    strLine = Right(strLine, Len(strLine) - 4)
    ' .Offset(I, 7) = Trim(Left(strLine, 2))
    strLine = Right(strLine, Len(strLine) - 3)
    ' .Offset(I, 8) = Trim(Left(strLine, 10))
    strLine = Right(strLine, Len(strLine) - 11)
    .Offset(I, 4) = Trim(Left(strLine, 10))
    ' strLine = Right(strLine, Len(strLine) - 11)
    ' .Offset(I, 10) = Trim(Left(strLine, 2))
    ' strLine = Right(strLine, Len(strLine) - 3)
    ' .Offset(I, 11) = Trim(Left(strLine, 2))
    ' strLine = Right(strLine, Len(strLine) - 3)
    ' .Offset(I, 12) = Trim(Left(strLine, 2))
    ' strLine = Right(strLine, Len(strLine) - 3)
    ' .Offset(I, 13) = Trim(Left(strLine, 1))
    I = I + 1
    Loop
    Loop
    Close #iFileNum
    End With
    End Sub
    </pre>

    Legare Coleman

  11. #11
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Hey, Legare!
    This is beautiful! Works like a charm! I imported several different rosters and they all came in much better than I accomplished on my own.

    I just have one more slight request - and that is how do I tell it to make the ssn column a text field so the leading zeros don't go away?
    Thank you again for your time and effort!

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Try this:

    <pre>Public Sub LoadFiles()
    Dim strLine As String, strWk As String
    Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
    Dim vFileToLoad As Variant
    vFileToLoad = Application.GetOpenFilename(FileFilter:="Text Files,(*.txt),All Files,(*.*)", _
    FilterIndex:=1, Title:="File to laod")
    If vFileToLoad = False Then
    MsgBox "No file to laod."
    Exit Sub
    End If
    I = 0
    iFileNum = FreeFile
    With Worksheets("Sheet1").Range("A1")
    Open vFileToLoad For Input As #iFileNum
    Do While Not EOF(iFileNum)
    Do While (Not EOF(iFileNum) And (Left(Trim(strLine), 18) <> String(18, "-")))
    Line Input #iFileNum, strLine
    Loop
    If EOF(iFileNum) Then Exit Do
    Line Input #iFileNum, strLine
    If EOF(iFileNum) Then Exit Do
    Do While Not EOF(iFileNum)
    Line Input #iFileNum, strLine
    strLine = Trim(strLine)
    If strLine = "" Then Exit Do
    strWk = Left(strLine, 18)
    .Offset(I, 0).Value = Left(strWk, InStr(strWk, ",") - 1)
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ","))) & " "
    .Offset(I, 1) = Left(strWk, InStr(strWk, " ") - 1)
    .Offset(I, 2) = Trim(Right(strWk, Len(strWk) - InStr(strWk, " ")))
    strLine = Right(strLine, Len(strLine) - 20)
    strWk = Trim(Left(strLine, 9))
    .Offset(I, 3) = Left(strWk, 3) & "-" & Mid(strWk, 4, 2) & "-" & Right(strWk, 4)
    strLine = Right(strLine, Len(strLine) - 36)
    .Offset(I, 4) = Trim(Left(strLine, 10))
    I = I + 1
    Loop
    Loop
    Close #iFileNum
    End With
    End Sub
    </pre>

    Legare Coleman

  13. #13
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting A Character (2000)

    Hi, Legare!
    Works great! I appreciate your help with this since it is far more than what I had orignially asked for. This is a far more elegant procedure than what I was doing. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •