import from text file with VBA code

[sheela]

How do I automate the process of importing data from a text file into excel?

The text file contains multiple types of results including other summary
information. I would like to import the each test results into each excel
tab/sheet.
I would like to implement a VBA code with the following criteria.

1) to search for a row with a single word: “Result†( the search word
should be case sensitive)and start import the text from next row
2) Import each row of the text file into each row of the excel file.
3) The text to import is delimited by space character.
4) And stop importing once it encounters a row of “-----“ .
5) Then continue the search for next row with the text “Result†and import
the text starting from next row into another tab of the same sheet until it
hits a series of “----“
6) Continue this process until end of the text file

Is it possible to do this at all in excel? If not could you suggest another
program to do this?

My text file to import into excel looks something like this.

Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 White 2.141 Star 1.000
1 White 0.703 Star 1.000
2 Red 0.594 Star 1.000
2 White 0.734 Star 1.000
2 White 0.657 Star 1.000
3 Black 0.610 Star 1.000
------------------------------
Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 Triangle 2.141 AAA 1.000
2 Triangle 0.703 AAA 1.000
3 Round 0.594 ABC 1.000
3 Rectangle 0.734 DSA 1.000
3 Square 0.657 WQA 1.000
4 Round 0.610 RVG 1.000

 

[Benjamin]

You'll need a good understanding of the kind of text file you have first.
This program would definetly do the trick. Although you'll need to tweak the
code on these various sites. Respond if you need further help.

see:
http://www.dailydoseofexcel.com/archives/2005/02/08/import-text-in-vba/
http://newtonexcelbach.wordpress.com/2008/05/19/importing-text-files-with-vba/
http://www.cpearson.com/excel/ImpText.aspx
http://www.ozgrid.com/forum/showthread.php?t=14387
http://p2p.wrox.com/excel-vba/17360-import-data-text-file-excel-vba.html

Here's an example of one that could possibly work for you:

Sub ImportData()

'Filename = the full path to Your txt file eg."C:\MyDocuments\Mytxt.txt"
Open Filename For Input As #1
Do While (Not EOF(1))
' In this case the file is delimited by , and contains several lines
' Read the file one line at the time
Input #1, Streng

'Input streng into and array
StrArray = Split(Streng, ",")
Call WriteToExcel(StrArray)
Loop
Close #1
End Sub

Sub WriteToExcel(StrArray)
For J = LBound(StrArray) To UBound(StrArray)
'Do what you want to do with the data
Next J
End Sub

 

[Lars-Åke Aspelin]

How do I automate the process of importing data from a text file into excel?

The text file contains multiple types of results including other summary
information. I would like to import the each test results into each excel
tab/sheet.
I would like to implement a VBA code with the following criteria.

1) to search for a row with a single word: “Result” ( the search word
should be case sensitive)and start import the text from next row
2) Import each row of the text file into each row of the excel file.
3) The text to import is delimited by space character.
4) And stop importing once it encounters a row of “-----“ .
5) Then continue the search for next row with the text “Result” and import
the text starting from next row into another tab of the same sheet until it
hits a series of “----“
6) Continue this process until end of the text file

Is it possible to do this at all in excel? If not could you suggest another
program to do this?

My text file to import into excel looks something like this.

Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 White 2.141 Star 1.000
1 White 0.703 Star 1.000
2 Red 0.594 Star 1.000
2 White 0.734 Star 1.000
2 White 0.657 Star 1.000
3 Black 0.610 Star 1.000
------------------------------
Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 Triangle 2.141 AAA 1.000
2 Triangle 0.703 AAA 1.000
3 Round 0.594 ABC 1.000
3 Rectangle 0.734 DSA 1.000
3 Square 0.657 WQA 1.000
4 Round 0.610 RVG 1.000

Try this macro:

Sub import_from_text_file()
Dim myWords
Set wb = Workbooks.Add()
Set ws = wb.Worksheets.Add
wsrow = 1
Open "example.txt" For Input As #1
copying = False
While Not EOF(1)
Line Input #1, readline
If InStr(1, readline, "Result") Then
copying = True
ElseIf InStr(1, readline, "-------") Then
copying = False
wsrow = 1
Set ws = wb.Worksheets.Add
Else
If copying Then
myWords = Split(readline, " ")
For i = 1 To UBound(myWords)
ws.Cells(wsrow, i) = myWords(i)
Next i
wsrow = wsrow + 1
End If
End If
Wend
Close #1
End Sub

Hope this helps / Lars-Åke

 

[Sheila]

That's brilliant. The macro worked, with small modification.
Thank you so much.

Now I would like to name the sheets with the ID information ( "xxxxx")
written after "Some ID:". It is there in the second row of the text file.

How do I incorporate this into the code?

Thanks a lot for your help.
Sheela

 

[Sheela]

Please ignore my last question. I figured out naming the sheets.
thank you.

 

[Lars-Åke Aspelin]

I added a few lines to the macro to rename the sheets according to the
texts following "Some ID:"
Also added a few lines at the end to delete the last created sheet
(which is never used)

Hope this helps / Lars-Åke

Sub import_from_text_file()
Dim myWords
Filename = <<<<<<<< your filename goes here >>>>>>>>>>>>
Open Filename For Input As #1
copying = False
Set wb = Workbooks.Add()
Set ws = wb.Worksheets.Add
wsrow = 1
While Not EOF(1)
If InStr(readline, "Some ID:") = 1 Then
ws.Name = Mid(readline, Len("Some ID:") + 1)
End If
Line Input #1, readline
If InStr(1, readline, "Result") Then
copying = True
ElseIf InStr(1, readline, "-------") Then
copying = False
wsrow = 1
Set ws = wb.Worksheets.Add
Else
If copying Then
myWords = Split(readline, " ")
For i = 1 To UBound(myWords)
ws.Cells(wsrow, i) = myWords(i)
Next i
wsrow = wsrow + 1
Else
End If
End If
Wend
Close #1
alertsave = Application.DisplayAlerts
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = alertsave
End Sub