Importing txt file with multiple headers

Droopy · Aug 25, 2003

I'm trying to import a .txt file that has 3 lines of headings on every 'page'. I don't need to import the headers and just want the actual data to line up into columns. Is it possible to bypass these header rows on import?

e.g. This is an example of the text file:

----- PFD ----- YEAR TO DATE TRIAL BALANCE: AUGUST 03 PAGE 1
ABCDEF/S 2300.1 THIS MONTH IS STILL OPEN 22/08/03 09:30
-------------------------------------------------------------------------------------
ACCOUNT TYPE DESCRIPTION DEBIT CREDIT
-------------------------------------------------------------------------------------
ACCOUNT A1

------------------------ ------------------------
.00 .00
===========================
LEDGER IN BALANCE
LEDGER IN BALANCE
----- PFD ----- YEAR TO DATE TRIAL BALANCE: AUGUST 03 PAGE 2
ABCDE/S 2300.1 THIS MONTH IS STILL OPEN 22/08/03 09:30
-------------------------------------------------------------------------------------
ACCOUNT TYPE DESCRIPTION DEBIT CREDIT
------------------------------------------------------------------------------------- ACCOUNTB AB 1234567-0 P DESCRIPTION1 ACCOUNT 1,326.48
AB 8357602-0 B DESCRIPTION2 2,762,722.86
AB 0834053-0 P DESCRIPTION3 300.00
AB 0384992-0 B DESCRIPTION5 2,617,277.64

SydneyGeek · Aug 25, 2003

Hi,

when you import data into Excel from a text file, you can select which row to start the import at.
As you go through the wizard, check carefully. There is a box which defaults to 1, but you can insert any reasonable number.

Hope that helps.

Droopy · Aug 25, 2003

Thanks Syndey Geek. Unfortunately the header rows that I mention repeat themselves every time there is a change of Account Type in the first column. I am trying to remove all instances of the header rows, leaving only the data that I need.

e.g.
-------Heading---------
------Another Heading----------
-------------------------------------------------------
Column 1 Column 2 Column 3 Column 4
-------------------------------------------------------
AT 1 xxx xxx xxx
yyy yyy yyy
zzz zzz zzz
-------Heading---------
------Another Heading----------
-------------------------------------------------------
Column 1 Column 2 Column 3 Column 4
-------------------------------------------------------
AT2 aaa aaa aaa
bbb bbb bbb
-------Heading---------
------Another Heading----------
-------------------------------------------------------
Column 1 Column 2 Column 3 Column 4
-------------------------------------------------------
etc, etc, etc

SydneyGeek · Aug 25, 2003

OK, that's a bit more tricky. You'll need a macro to run through and do a cleanup.

A couple of questions:
1. In Column A, is there any piece of text that is EXACTLY the same in each of your junk sections?
2. How big is the file -- always the same number of records, or variable?
3. When you finish, what layout do you want?

If you can provide details on these points, I may be able to get you going.

Droopy · Aug 25, 2003

<body>Thanks for the help here Sydney Geek!

In response:
1. I think it's definately safe to assume that the Column A value will never equal what's in the junk-header sections.
2. The number of records will vary each time the txt file is created
3. Basically all I'm looking for is a consolidation of the data, something like this:
<table border=1 cellspacing=0 cellpadding=0>
<tr>
<td>

Column A
</td>
<td>

Column B
</td>
<td>

Column C
</td>
<td>

Column D
</td>
</tr>
<tr>
<td>

AT1
</td>
<td>

111
</td>
<td>

111
</td>
<td>

111
</td>
</tr>
<tr>
<td>

<o

></o

>
</td>
<td>

222
</td>
<td>

222
</td>
<td>

222
</td>
</tr>
<tr>
<td>

<o

></o

>
</td>
<td>

333
</td>
<td>

333
</td>
<td>

333
</td>
</tr>
<tr>
<td>

AT2
</td>
<td>

555
</td>
<td>

555
</td>
<td>

555
</td>
</tr>
<tr>
<td>

AT3
</td>
<td>

888
</td>
<td>

888
</td>
<td>

888
</td>
</tr>
<tr>
<td>

<o

></o

>
</td>
<td>

999
</td>
<td>

999
</td>
<td>

999
</td>
</tr>
<tr>
<td>

<o

></o

>
</td>
<td>

etc
</td>
<td>

etc
</td>
<td>

etc
</td>
</tr>
</table>
</body>

Jason · Aug 25, 2003

This might be a silly response, but have you tried cleaning it up using WORDPAD and using the REPLACE command to replace all instances of those words with nothing, hence deleting them. Resave and import.

SydneyGeek · Aug 26, 2003

If you can give an example of what the imported text looks like (use the Excel HTML add-in -- link at the top of this forum) I'll have a better idea of what we're looking at. Reason is, the macro that I have in mind needs some distinctive tage to use for jumping into the junk before deleting it. I have done stuff like this before, and having a tag to use is the only way to stay sane and not lose data.

Denis

bat17 · Aug 26, 2003

I assume that this is the data part that you are after

AB 8357602-0 B DESCRIPTION2 2,762,722.86
AB 0834053-0 P DESCRIPTION3 300.00
AB 0384992-0 B DESCRIPTION5 2,617,277.64

1) Does the first part always have the same format i.e. 2 Letters, Space, 7 digits, hyphen, 1 digit?
2) Are the columns that you want all separated by spaces and are spaces only used as column separators and not as within the data itself?
3) Are we talking hundreds of rows, thousands of rows or ten's of thousands?

Peter

ckmoied · Aug 26, 2003

Run the following macro in your workbook. Change the filename as per your path and filename. This macro is for text import with , as separator. You can use different separator as per you requirement. You will be asked to enter the number of rows that you want to skip.

Lemme know if that works fine.

-------------------------------------------------------

Sub import_test()
FName = "C:\see.txt"
Sep = ","
ActiveCell = Application.Run("ImportTF", FName, Sep)
End Sub

Public Sub ImportTF(FName As String, Sep As String)
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim rtoskip As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1
rtoskip = InputBox("Enter the rows to skip")
i = 1
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
If i > rtoskip Then
RowNdx = RowNdx + 1
End If
i = i + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Droopy · Aug 26, 2003

To answer the questions:
1. Unfortunately the fields with format <2 Letters, Space, 7 digits, hyphen, 1 digit> are in the second column. The first column contains a 'grouping' field which has a number of transactions under it before it changes on to the next group.
2. The seperator used is spaces but the data also has spaces between text that needs to be held together.
3. The file will generally have around 1000 rows.

Importing txt file with multiple headers

Droopy

New Member

Excel Facts

SydneyGeek

MrExcel MVP

Droopy

New Member

SydneyGeek

MrExcel MVP

Droopy

New Member

Jason

New Member

SydneyGeek

MrExcel MVP

bat17

Well-known Member

ckmoied

Board Regular

Droopy

New Member

Forum statistics

Share this page

Importing txt file with multiple headers

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

Well-known Member

Board Regular

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock