Ugly Import Ansi text to Excel

[tahlmorrah]

Saw a couple of posts that related, but nothing that answers my unique situation...and its a situation I hope I never have to be in again...Thanks for the help in advance

Using an Ansi report to be imported and final tallies MUST be done using only Excel (licensing and system restrictions). Problem in the file is REAL ugly. It is comprised of several different sections with different widths, not tab or comma delimitated and need some information from at least two different sections. Really need a hand with ideas I can use to capture this data.

Can not manually convert the data due to the tremendous volume of data and the rate the data is collected (daily).

I am sure I am going to have to use sting commands to parse this, but not sure the best way to go about it do to the fluctuating size of the different sections between each other let alone the employee data in them.

The end users are novice Excel users let alone anything else. If I can get this data in, I can formulate it the way they need so it is nice, pretty, and user friendly.

Information needed:
Date From and To
Agent ID (5 digit ID starting in this case with the 57xxx)
Full Name (would be nice but willing to cross reference off of other tabs)
...and all the information under Taken Days

Having additional information such as:
Total Earned
Max Partial Hours
Total Taken
Total Debited
Remaining to Select
Would be ideal, but I am willing to forgo these if necessary.

Unfortunatly the report can not be modified in any way (otherwise I might have an easier time) and no the import can not set to Export to Excel. Nor can additional access be granted to the root data.

Can send the file to whomever request and have included a sample that would be best to copy/paste into notepad before you look at it. The information has been changed to protect the innocent.

If it can't be done I need to know that too... but if all possible I need to try to use VBA and Excel.

[BrianB]

Most of us will not open attachments in the forum.

I do this sort of thing quite often - getting reports from Oracle that include page number/repeated headings etc. A fiddly job requiring patience ! The reward is good. My last effort cut a users processing time for 250,000 records from a full day manually to 10 minutes.

If you make sure that the file suffix is .txt and import into Excel you get the wizard that allows setting import to "space delimited" (or "Fixed Width"). You can then put the column dividers into suitable places to separate *the data you want* into columns. The rest becomes garbage because it is separated wrongly.

Record the macro of the process because you will get very useful code which includes the column width settings, which can be tweaked if necessary.

You then have to write code to transfer data to a simple table in a clean worksheet. To do this it is necessary to find a common denominator to the layout. The simplest would be that the data starts in the next row below a heading.

Let's say I have the column heading "Name" appearing in column A. So we start looking in column A from row 1 down and, when it is found, transer the data rows below. The following code assumes that there is a blank cell after the data in column A to tell us where it ends. It also assumes that the text file has the text "Gross Total" at the end somewhere.

Sub DataFromPlainTextFile()
    Dim FromSheet As Worksheet
    Dim ToSheet As Worksheet
    Dim FromRow As Long
    Dim LastRow As Long
    Dim ToRow As Long
    Dim FoundCell As Object
    '-----------------------------
    '- import textfile
    Workbooks.OpenText FileName:="C:\TEST.TXT", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(19, 1))
    Set FromSheet = ActiveSheet
    '- first & last rows
    FromRow = 1
    Set FoundCell = _
      FromSheet.Cells.Find(What:="Gross", After:=[A1], LookAt:=xlPart)
    LastRow = FoundCell.Row
    '- add clean sheet
    Set ToSheet = Worksheets.Add
    ToRow = 2
    ToSheet.Range("A1:C1").Value = Array("Name", "Address", "Telephone")
    '---------------
    '- main loop
    '---------------
     While FromRow <= LastRow
        Application.StatusBar = " Procesing " & FromRow & " / " & LastRow
        If FromSheet.Cells(FromRow, 1).Value = "Name" Then
            FromRow = FromRow + 1
            '- copy data across
            While FromSheet.Cells(FromRow, 1).Value <> ""
                ToSheet.Cells(ToRow, 1).Value = FromSheet.Cells(FromRow, 1).Value
                FromRow = FromRow + 1
                ToRow = ToRow + 1
            Wend
        End If
        FromRow = FromRow + 1
    Wend
    '- finish
    MsgBox ("Done")
    Application.StatusBar = False
End Sub

[Ecniv]

There is one other way.

You can code a custom import via vba code...

This (of course) can and will stretch your string manipulation skills, and splitting of arrays etc, so you can cover all possibilities.

I used to have Access import text files (of anything, separated by anything...) and recently made some code up that used Excel (the text file was opened via excel, which made things... different.

If you want any pointers into reading in as text and outputing either a cleaner file or outputting to an Excel sheet, I'll be gald to help, but you'd have to read up and try some code first.

Functions to read up on :
- Open (files)
- Close (files) - look for hash (#)
- Line Input
- Print #1,<string>
- EOF(<num>)
- Instr
- Array
- Left, mid, right




Or you can use Brians example.


Vince

[tahlmorrah]

Thanks for the direction, really appriciate the help.