Convert text report to table/database

[dssxpert]

Step 1 of 2
I would like to convert an imported text report into an Excel table or database. I can manually label the columns of my data but perferably the code would at least strip out the multiple report headers from the body of the data. Many reports do not repeat data in the sorted column(s). I would like the code to populate blank cells in the table as the following example shows.

Report:
State.City....... Client Amount
CA.... Glendale 11111 100.00
......................22222 200.00
.........Redlands 33333 300.00
MS.... Biloxi..... 44444 400.00

Table:
State.City....... Client Amount
CA.... Glendale 11111 100.00
CA.... Glendale 22222 200.00
CA.... Redlands 33333 300.00
MS.... Biloxi......44444 400.00

Step 2 of 2
On some of the reports I have, what would be considered a field in the final table is actually part of the page heading. For simplicity's sake, let's say that the page heading contains "County name: xxxxxxxxxxxxx" for the above report. I guess I would want the code to always look for "Country name:" and put the subsequent value in a field for the resulting table. You can assume that there is always a page break when a change in Country name occurs.

I've seen this done before at a former employer but I've Googled over 200 websites and was not able to find any code. Thank you in advance.

George

[lucas]

Is the text file comma delimited?
could you upload a sample of the text file?

[lucas]

email replied to George

[fixo]

Hi George
This a snip from my project
I have slightly edited it see how this will work for you


Option Explicit
Sub ReadReport()

Dim oFSO As New FileSystemObject
Dim oTStream As TextStream
Dim lineStr, expStr As String
Dim WSht As Worksheet
Dim strVar() As String
Dim oFile As File
Dim iRow, iCol, iCnt As Long
''-------------------------------------------------''
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Change full path of text file to suit
Set oFile = oFSO.GetFile("C:\MyVBA\Report.txt")
Set oTStream = oFile.OpenAsTextStream(ForReading, TristateUseDefault)
iRow = 1
' Change worksheet index
Set WSht = ThisWorkbook.Worksheets(2)
WSht.Activate
With WSht
.Cells.ClearContents
' Loop while not at the end of the file
Do While Not oTStream.AtEndOfStream
lineStr = oTStream.ReadLine
' If you have delimiter another than space, change here:
strVar = Split(lineStr, " ", , vbTextCompare)
For iCnt = 0 To UBound(strVar)
iCol = iCnt + 1
.Cells(iRow, iCol).Value = strVar(iCnt)
Next iCnt
iRow = iRow + 1
Loop
' Set header font bold
.Range("1:1").Font.Bold = True
iCol = .UsedRange.Columns.Count
' Summ accounts
expStr = Replace(.Cells(iRow - 1, iCol).Address(), "$", "", 1) & _
":" & Replace(.Cells(2, iCol).Address(), "$", "", 1)
' Translate sum function in the formula (here is russian version)
' Not sure about maybe here need to write "SUMM" instead
.Cells(iRow, iCol).FormulaLocal = "=????(" & expStr & ")"
.Cells(iRow, iCol).Font.Bold = True
.Cells(iRow, iCol).Font.Color = vbRed
End With
' Close report file
oTStream.Close
' Release objects
Set oTStream = Nothing
Set oFile = Nothing
Set oFSO = Nothing

End Sub


HTH,

Fatty

~'J'~

[dssxpert]

Fatty,

Thanks for posting. I pasted your code into a module, but when I tried to run it I got an error message for the very first DIM statement:
Compile error:
User-defined type not defined

I don't know how to fix this.

George

[mdmackillop]

Add a reference to "Microsoft Scripting Runtime"