Parse(?) data from text file

[BDT]

I have a large text file that has hundresds (or thousands) of compiled
questionairres that I want to convert to a more usable format (excel?). Here
is what it looks like:

========================
Name: Joe Jones
Address: 123 main
comment: varies from blank to 20+ lines may have muliple paragraphs
others: 8 or 10 other fields
===============================
next questionnaire
======================

A couple of details: the separator line of ====s vary in length, the
individual questionnaires are not always identical. Some have a couple of
extra 'fields'.

I envision a separate line for each questionnaire. I just can't cut and
paste 10 or 12 separate fields for several hundred questionnaires. I doubt
there is a perfect way to do this given the variables involved, but perhaps
there is a way to get close so I don't have too much manipulation to fix it
up.

Any help is greatly appreciated.

thx, BDT

 

[Daniel.C]

Each line of a questionnaire will be put on the same row, in a separate
cell :

Sub TextFileInput()
Dim myLine As Long, Enrgt As String, Col As Integer
Close #1
Open "e:\donnees\daniel\mpfe\textfile.txt" For Input As #1
Workbooks.Add 1
Do While Not EOF(1)
Line Input #1, Enrgt
If Left(Enrgt, 1) = "=" Then
myLine = myLine + 1
Col = 1
Else
Cells(myLine, Col) = Enrgt
Col = Col + 1
End If
Loop
Close #1
End Sub

Regards.
Daniel

 

[BDT]

Hi Daniel,

I'm in over my head here, I haven't written a macro in years. I used your
macro but replaced line 4 with:

Open "e:\RepMon.txt" For Input As #1

and I put the file RepMon.txt in the root of e:. I ran the macro and got an
error that said ... Run time error 1004, Aplication-Defined or Object-Defined
error. When I clicked the debug button, it highlighted the line:

Cells(myLine, Col) = Enrgt

am I doing something wrong or is there an obvious problem??

thanks, BDT

 

[IanC]

Hi BDT

Open "e:\RepMon.txt" For Input As #1
and I put the file RepMon.txt in the root of e:.

No problem so far.

I ran the macro and got an
error that said ... Run time error 1004, Aplication-Defined or
Object-Defined
error. When I clicked the debug button, it highlighted the line:

Cells(myLine, Col) = Enrgt

am I doing something wrong or is there an obvious problem??

The problem lies in the code you were given. It works perfectly if the first
line of the text file is a separator line (ie begins with "="). In this
case, the first part of the If condition is satisfied and Col is set to 1.
If this is not the case, it goes straight to the 2nd part of the condition
and tries to write data to a cell in column 0, which doesn't exist.

The cure is to add the line Col=1 somewhere before

If Left(Enrgt, 1) = "=" Then

There's another oddity in the code.

Workbooks.Add 1 creates a new workbook, but I don't see the point of it as
the data is written to the workbook holding the macro anyway. You can safely
delete this line.

Hope this helps.

 

[Daniel.C]

Hi BDT

No problem so far.


The problem lies in the code you were given. It works perfectly if the first
line of the text file is a separator line (ie begins with "="). In this case,
the first part of the If condition is satisfied and Col is set to 1. If this
is not the case, it goes straight to the 2nd part of the condition and tries
to write data to a cell in column 0, which doesn't exist.

The cure is to add the line Col=1 somewhere before

If Left(Enrgt, 1) = "=" Then

There's another oddity in the code.

Workbooks.Add 1 creates a new workbook, but I don't see the point of it as
the data is written to the workbook holding the macro anyway. You can safely
delete this line.

Just to say that adding a workbook or a new sheet ensures that you are
not overwriting any existing data.
Daniel

 

[IanC]

Just to say that adding a workbook or a new sheet ensures that you are not
overwriting any existing data.
Daniel

That may be the intention, but the data isn't written to the new workbook

 

[Daniel.C]

Just to say that adding a workbook or a new sheet ensures that you are not

That may be the intention, but the data isn't written to the new workbook

Well, they did when I test it.
Daniel

 

[Dave Peterson]

Did you put the code behind a worksheet or put it in a General module?

It belongs under a general module.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[IanC]

Well, they did when I test it.
Daniel

I've just tried a couple of things and discovered that if the code is pasted
in at sheet level, the data is copied into that sheet, but if it's pasted at
workbook level, it copies the data to the new workbook.

I've just learnt something new

 

[BDT]

Hi Danial, Ian and Dave,

Awesome help,all of you. Instead of fooling with the macro, I just added
the line of ======s that I left off of the first set of records and the macro
ran fine. I only need to run this a couple of times so I can easily do it
that way and it will save me many hours or work.

thanks again, BDT

Did you put the code behind a worksheet or put it in a General module?

It belongs under a general module.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)