Text to excel

[Ewright8]

I have 240 text files (dxdiag reports from 240 different computers) that I would like to put into an excel spreadsheet. I do not need all of the file but would like to have about nine different lines from the file placed in cells with one row representing each file (computer). Is this possible or does anyone have any suggestions how I may go about this without having to type all the data in manually.

 

[Ed Acosta]

It is possible. All the files could be placed in one directory. Have Excel read each file line by line, once it found the lines you are looking for place in the next available row and begin again with the next file.

 

[Ewright8]

How do I tell it which lines to place in the following row and how do I tell excel to move to the next file after it finishes with the one that it is on.

 

[Norie]

No idea.

What is it you want to extract from these files?

As far as I can see they are just normal text files, and the content is well structured into sections etc

So you should be able to open them in Excel and extract the information you want.

That's probably going to be the hard part so I would suggest you concentrate on that.

 

[Ed Acosta]

You will need some VBA code to doit. Creat a C:\Data directory and put a couple of DXDiag.txt files in it. The following code will look in that directory and pull any lines that have the word Direct in it. This is an example of how you can pull the data you need

Sub ShowFileList()
    Dim fs, f, f1, fc, s
    Dim LastRow As Long
    Dim strPath As String
    strPath = "C:\Data\"    'This is the Directory the files are located in
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(strPath)
    Set fc = f.Files
    Sheets(1).Select
    If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
            
    For Each f1 In fc
        Open strPath & f1.Name For Input As #1
        Do Until EOF(1)
            Line Input #1, INFO
            If InStr(1, INFO, "Direct") Then LastRow = LastRow + 1: Cells(LastRow, 1).Value = INFO 'This is where you would examine the line for what you are looking for
        Loop
        Close #1
    Next
    
End Sub

 

[Ewright8]

This is almost perfect except I need the output for each file to be on the same row and each file would be a new row. Is there a way I can automate which information is being extracted without having to edit every text file and enter "direct" in the front of each line. Thanks, this has been a great starting point and if this is the best we can do it is a lot better than it was to start with.

 

[Ed Acosta]

Is the information you are extracting going to be the same all the time? or could it be different? Provide an example of what you will pull out of the Dxdiag.txt file and I can tweak the code for you.

 

[Ewright8]

What I would like is to put "Machine name:" in A1 and "Operating System:" in A2 and "Processor:" in A3 and "Memory: in A4 for the first file the the next file would be in B1, B2, B3 and B4 etc. Below is an example of the first part of the file

------------------
Time of this report: 5/7/2009, 15:50:50
Machine name: HS_COUNSELOR
Operating System: Windows XP Professional (5.1, Build 2600) Service Pack 3 (2600.xpsp_sp3_gdr.090206-1234)
Language: English (Regional Setting: English)
System Manufacturer: Dell Inc.
System Model: OptiPlex GX620
BIOS: Phoenix ROM BIOS PLUS Version 1.10 A01
Processor: Intel(R) Pentium(R) 4 CPU 3.20GHz (2 CPUs)
Memory: 2038MB RAM
Page File: 468MB used, 2940MB available
Windows Dir: C:\WINDOWS
DirectX Version: DirectX 9.0c (4.09.0000.0904)
DX Setup Parameters: Not found
DxDiag Version: 5.03.2600.5512 32bit Unicode

 

[Ed Acosta]

Ok try this code, also since you are putting each file in a column you are limited to 231 columns so your 240 files wont fit


Sub ShowFileList()
Dim fs, f, f1, fc, s
Dim CL As Long
Dim strPath As String
strPath = "C:\Data\" 'This is the Directory the files are located in
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(strPath)
Set fc = f.Files
CL = 0
Sheets(1).Select

For Each f1 In fc
CL = CL + 1
Open strPath & f1.Name For Input As #1
Do Until InStr(1, INFO, "Memory:")
Line Input #1, INFO
If InStr(1, INFO, "Machine name:") Then Cells(1, CL).Value = LTrim(INFO)
If InStr(1, INFO, "Operating System:") Then Cells(2, CL).Value = LTrim(INFO)
If InStr(1, INFO, "Processor:") Then Cells(3, CL).Value = LTrim(INFO)
If InStr(1, INFO, "Memory:") Then Cells(4, CL).Value = LTrim(INFO)
Loop
Close #1
INFO = ""
Next

End Sub

 

[Norie]

Ewright8

Wouldn't it be better to have this data in rows rather than columns?

Ed

Limited to 231 columns?

Doesn't Excel have 256 columns.