• 2000 Text Files to Excel (Office 2000-2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » 2000 Text Files to Excel (Office 2000-2003)

    Author
    Topic
    WSsobershe
    AskWoody Lounger
    January 10, 2008 at 5:09 pm #447569

    A colleague has received 2000 text files that he needs to import to Excel. First, is there a way to automate getting all those files into one Excel file? Second, the data is laid out horribly (see the attached file). I quickly looked at the one record and determined that IF all the extraneous data was eliminated (headers such as “Survey Answers”) he would have to copy/paste special to get the line descriptions into column headings. It would be ugly! I told him I would ask the experts for some ideas.

    Thanks.

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      January 10, 2008 at 5:13 pm #1091925

      If all files are stored in the same folder, you could write a macro to read each of the files in turn, and to write the data to an Excel sheet.
      If you would like help with that, it would be nice if you could give us an idea of the desired format of the worksheet and which of the data from the text files need to go where.

      Reply | Quote
      • WSsobershe
        AskWoody Lounger
        January 10, 2008 at 5:39 pm #1091932

        The layout he is looking for is attached.

        Thanks Hans.

        Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          January 10, 2008 at 5:44 pm #1091937

          What is the layout of the text files?

          Steve

          Reply | Quote
        • WSHansV
          AskWoody Lounger
          January 10, 2008 at 6:18 pm #1091961

          The attached text file contains a brute force approach. It could be made more compact but I don’t have time for that now.
          Change the constant strPath at the beginning of the macro to the path of the folder containing the files. The path must end in a backslash .
          The target sheet must be open when you run the macro.

          Reply | Quote
          • WSsobershe
            AskWoody Lounger
            January 10, 2008 at 6:33 pm #1091967

            We will give it a try. Thanks Hans!!

            Reply | Quote
          • WSsobershe
            AskWoody Lounger
            January 10, 2008 at 8:59 pm #1091984

            We had an error on the following line:
            lngRow = Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            We made 2 copies of the file I originally attached (he hasn’t received all 2000 files yet) and put them in C:Dimensional and ran the macro. I attached the file with the macro.

            Thanks

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              January 10, 2008 at 9:24 pm #1091989

              That is because the worksheet is completely empty. I had assumed that you would have placed the column headers in row 1.
              For an empty sheet, you can replace the offending line with

              lngRow = 0

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 10, 2008 at 9:27 pm #1091991

              Duh! Sorry. We’ll try again.

              Thanks

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 11, 2008 at 2:12 pm #1092097

              Hans, we fixed the headers blush and it worked like a charm. You can’t imagine the time you have saved us. Thank you so much!!

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 11, 2008 at 4:07 pm #1092117

              Hans, we ran the macro with the real files (around 750 files) and it stopped after 150 lines. The error was Run Time Error 62 – Input past end of file And the line in the code highlighted was line input #f, strLine.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              January 11, 2008 at 4:18 pm #1092120

              That would happen if one or more of the files don’t have all the lines from your sample file.

              The attached version will display a message, but continue with the next file when you click OK.

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 11, 2008 at 4:41 pm #1092127

              Hans, you are right about the format (see my response to Steve). Thanks again! This would have been a huge job if done manually.

              Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          January 11, 2008 at 3:04 pm #1091975

          (Edited by sdckapr on 11-Jan-08 09:04. Forgot to add the Path the filename when opening the text file.)

          I took a little different approach. I presume all the text files have the same format.

          Steve

          Option Explicit
Sub ImportText()
  'Modify as appropriate
  Const sPath  As String = "C:Path"
  Dim wkbTxt As Workbook
  Dim wksTxt As Worksheet
  Dim wks As Worksheet
  Dim sFile As String
  Dim iTxtRow As Integer
  Dim iCol As Integer
  Dim lRow As Long
  
  Set wks = ActiveWorkbook.Worksheets.Add
  sFile = Dir(sPath & "*.txt")
  If sFile = "" Then
    MsgBox "No files found"
    Exit Sub
  End If
  
  lRow = 1
  iCol = 1
  Do While Not sFile = ""
    Workbooks.OpenText Filename:=sPath & sFile, _
      StartRow:=1, DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
      Other:=True, OtherChar:=":"
    Set wkbTxt = ActiveWorkbook
    Set wksTxt = wkbTxt.Worksheets(1)
    If lRow = 1 Then
      For iTxtRow = 8 To 45
        If iTxtRow = 21 Then iTxtRow = 25
        If iTxtRow = 37 Then iTxtRow = 38
        wks.Cells(lRow, iCol) = wksTxt.Cells(iTxtRow, 1)
        iCol = iCol + 1
      Next
    End If
    
    lRow = lRow + 1
    iCol = 1
    For iTxtRow = 8 To 45
      If iTxtRow = 21 Then iTxtRow = 25
      If iTxtRow = 37 Then iTxtRow = 38
      If IsNumeric(wksTxt.Cells(iTxtRow, 2)) Then
        wks.Cells(lRow, iCol) = wksTxt.Cells(iTxtRow, 2)
      Else
        wks.Cells(lRow, iCol) = Trim(wksTxt.Cells(iTxtRow, 2))
      End If
      iCol = iCol + 1
    Next
    wkbTxt.Close (False)
    sFile = Dir
  Loop
  Set wks = Nothing
  Set wksTxt = Nothing
  Set wkbTxt = Nothing
End Sub
          Reply | Quote
          • WSsobershe
            AskWoody Lounger
            January 11, 2008 at 2:10 pm #1092096

            Steve, we get an error message about not being able to find a file named *.txt (see attached).

            Reply | Quote
            • WSsdckapr
              AskWoody Lounger
              January 11, 2008 at 2:22 pm #1092099

              That suggests in the path there are no files with a TXT extension.

              Do the files you are importing have another extension or are they located in a different drive and path?

              Steve

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 11, 2008 at 2:49 pm #1092102

              That was the first thing I checked – see screen shot.

              Reply | Quote
            • WSsdckapr
              AskWoody Lounger
              January 11, 2008 at 3:03 pm #1092104

              blush My mistake in the coding. I must have changed the default path when I tested it.

              Change the from :

              Workbooks.OpenText Filename:=sFile, _

              to:
              Workbooks.OpenText Filename:=sPath & sFile, _

              I will edit the original post to correct this. Sorry for the inconvenience.

              Steve

              Reply | Quote
            • WSsobershe
              AskWoody Lounger
              January 11, 2008 at 4:40 pm #1092125

              Steve, the revised code works great and he got all of his files imported. Your code allowed the files with abnormal formats to be uploaded and they just need to do a little cleanup on those records. We did add Application.ScreenUpdating = True/False so the blinking didn’t blind us grin but they got all their data and are very grateful.

              Thanks and cheers!

              Reply | Quote
            • WSsdckapr
              AskWoody Lounger
              January 11, 2008 at 4:46 pm #1092129

              Glad I could help

              Steve

              Reply | Quote
    Viewing 0 reply threads
    Reply To: 2000 Text Files to Excel (Office 2000-2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.