Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing data from several text files at once (2000)

    Is it possible to import data from several text files at a time into Excel rather than one at a time.
    If you see the attachment l would like to import data from all the text files in one go rather than one at a time, is this possible?

    Any assistance would be appreciated.

    Justin.

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

    Re: Importing data from several text files at once (2000)

    Not directly, you would need a macro. We could help writing a macro would need a lot more information, and a copy of one of the files that could be used for testing (with any sensitive data changed). We would also need to know if you want the files imported all to the same workbook or separate workbooks? One after the other one the same sheet or on separate sheets? Are all the files exactly the same format? Do you want to import all files in the directory or how do we recoginze which files?
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    All the files would be needed to be imported into the same workbook,then would need to be imported one after the after.
    One beneath the other in a different row.
    see attachment for one of the files.

    Justin.

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

    Re: Importing data from several text files at once

    How is the line in the text file to be divided into columns? What you want can probably be done in a macro, but you will need to tell us explicitly how exactly the line is to be split up. A macro will only work if ALL files are to be treated exactly the same way.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    Attached is a layout of how l would like the data to be displayed in the spreadsheet when it is imported from each file.

    Justin.

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

    Re: Importing data from several text files at once

    Thanks. Do you want to insert a blank row below each imported row, as indicated in your screenshot?

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    yes, if that can be done.

    Justin.

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

    Re: Importing data from several text files at once

    Sorry, more questions. The text file you attached contains more data than your screenshot:
    <pre> WD0481-10210 AIRCRAFT026 1 0101K0058A0 E 0
    </pre>

    Can you describe exactly, precisely and with details how you want this to be imported? Don't leave it to us to guess.

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    The first field is the drawing no, the second field is the title (this should be a character i.e aircraft).
    The number after it is sheet number and is sometimes can be on the end of the title field i.e airraft10. This is not a problem as l can cut and paste this number into the field sheet no., which in this case would be 10. The third field is the issue numer which is 1. The rest of the information is not needed (i.e the last 3 fields).

    Is that any clearer?

    Justin.

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

    Re: Importing data from several text files at once

    Copy the following code into a module:

    Sub ImportLotsOfFiles()
    ' Modify as needed but keep trailing backslash
    Const strPath = "F:Excel"
    Dim strFile As String
    Dim lngRow As Long
    lngRow = 2
    strFile = Dir(strPath & "*.idx")
    Do While Not strFile = ""
    ImportOneFile strPath, strFile, lngRow
    strFile = Dir
    lngRow = lngRow + 2
    Loop
    End Sub

    Sub ImportOneFile(strPath As String, strFile As String, lngRow As Long)
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & strPath & strFile, Destination:=Range("A" & lngRow))
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePlatform = 850
    .TextFileParseType = xlFixedWidth
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 9)
    .TextFileFixedColumnWidths = Array(19, 12, 4, 4, 41)
    .Refresh BackgroundQuery:=False
    .Delete
    End With
    Range("E" & lngRow) = Left(strFile, Len(strFile) - 4)
    End Sub

    The first Sub is the macro to be run, the second one is an auxiliary procedure. Substitute the correct path for F:Excel and make sure to include a trailing backslash.

  11. #11
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    I have run this but nothing appears to happen.
    See attachment.H:/test.xls is the location of the excel file.
    Any ideas what l doing wrong.
    Also how do you save the VB code so you can call it up to run it again?

    Justin.

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

    Re: Importing data from several text files at once

    strPath should be the complete path of the folder (directory) containing the .idx files, not the name of an Excel file. So if your .idx files are in C:TestDrawings and Index Files, use
    <code>
    strPath = "C:TestDrawings and Index Files"
    </code>
    Adjust as needed. Once you have put the code in a module and tested it, you should save the workbook containing the module. If you want to run the code again, open the workbook and select Tools | Macro | Macros...

  13. #13
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    I done this but appear to get an error, see attachment.

    Justin.

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

    Re: Importing data from several text files at once

    It would have been useful if you had told us what the error message was, instead of letting us guess. Does it work if you change it to

    .TextFilePlatform = xlMsDos

    If not, delete the entire line with TextFilePlatform.

  15. #15
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from several text files at once

    That is brillant Hans, it works perfectly.

    Thank very very much once again.

    Justin.

Posting Permissions

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