Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Text File Parser

  Asked By: Clariee    Date: Nov 20    Category: MS Office    Views: 1023
  

I need to write a code for parsing a text file and then processing it
in excel.I don't know much programming and have been reading
fundamentals of VB since last 1 week.

I will really appreciate if some one can precisely guide me and help
me in writing the code for the same.

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Komal Mohammad     Answered On: Nov 20

If you've never programmed VBA, this is not a good task to start your
programming with.

Think about putting comma separators in the file  instead, and then just
opening it straight into excel  as a CSV file.

 
Answer #2    Answered By: Chau Tran     Answered On: Nov 20

Can you give us an idea of what is in the text  file and how you want to
parse the information(break the information down)?



Reading from a text file  is reasonably standard and is easy to do, parsing
may be the thing that would be difficult.

 
Answer #3    Answered By: Viheke Fischer     Answered On: Nov 20

Here is some simple code  to read from a text  file until it is empty. The
parsing of the information is simple, the record read is broken into three
fields of a ten characters.



Public Sub Read_Text()



Dim TextLine As String, Field1 As String, Field2 As String, Field3 As String



Open "C:\My Documents\Testfields.txt" For Input As #1



Do While Not EOF(1) ' Loop until end of file.



Line Input #1, TextLine ' Read line into variable.



Field1 = Trim(Mid(TextLine, 1, 10))

Field2 = Trim(Mid(TextLine, 11, 20))

Field3 = Trim(Mid(TextLine, 21, 30))



Loop



Close #1



End Sub

 
Answer #4    Answered By: Jeanette Greene     Answered On: Nov 20

Also... I find using the SPLIT command great for some parsing  tasks.

 
Answer #5    Answered By: Isabella Campbell     Answered On: Nov 20

Split is good for delimited strings and Mid is good for fixed width fields.
It all depends on how the information is formatted.

 
Answer #6    Answered By: Logan Bouchard     Answered On: Nov 20

Thank for the code. I understand this code  and I was
doing some thing in the same manner but have
following doubts:

1) The test file  from I am reading has lot of
data.When we mention Line Input # 1 does this means
that we are reading only 1 line at 1 time or we are
reading the whole file.


2) What if I have space in between 2 lines, how should
I take that into account.

3) Can i use counters here somehow so that if data is
in same form it just read till 50 lines and then put
it in the excel  sheet and counter got updated.


This how i data want in Excel sheet:
BSC |Cell Name| BTS Id| MA|F1|F2||F3||F4|

This is how data is in text  file:

BSC SEBSC002 2006-12-24 17:13:06

MOBILE ALLOCATION FREQUENCY LIST - 1 BAND ...
1900
FREQUENCIES:
701 711 764 770
ATTACHED AS MOBILE ALLOCATION FREQUENCY LIST TO BTS:
1 FIRDALE1

MOBILE ALLOCATION FREQUENCY LIST - 2 BAND ...
1900
FREQUENCIES:
698 704 773 778
ATTACHED AS MOBILE ALLOCATION FREQUENCY LIST TO BTS:
2 FIRDALE2

MOBILE ALLOCATION FREQUENCY LIST - 3 BAND ...
1900
FREQUENCIES:
694 775 782 785
ATTACHED AS MOBILE ALLOCATION FREQUENCY LIST TO BTS:
3 FIRDALE3

As you can see my whole data is like this in text
file.There is around 200 lines of data that i need to
put into excel.

 
Answer #7    Answered By: Aidyn Smith     Answered On: Nov 20

What I generally do in such situations is just use EXCEL functions to
parse the data. For example, if you import the data into column A,
you could use a formula like this to keep a running value of the last
time BSC was found:

=IF(LEFT(A2,3)="BSC",A2,A1)

Then do a similar thing for each value of the group you want to keep.
You also need to have something that will trigger the generation of
each group. Then just pick up all of the "carried forward" values for
your observation. Copy and paste values to another range and sort,
then delete everything you don't want to keep.

To me, this method is a little easier than the VBA because I can see
the exceptions to the rule as I'm developing each rule when I work on
the formulas. But it can be 6 of one and a half dozen of the other.

 
Answer #8    Answered By: Abbad Akhtar     Answered On: Nov 20

The Line Input reads a single record. The Loop command a few lines down
sends control back to the Do While command. This continues until and End of
File (EOF) is detected.



You can do a trim on the line and use its' length to determine if the line
is empty and then skip processing.



Through each loop, you can set a flag indicating what you are expecting and
process each line differently. For some of the rows, you can check the first
few characters for MOBILE or ATTACH to make sure you are on track.



Or you could read each set into an array and then process the array when you
read the start of a new set or EOF.

 
Didn't find what you were looking for? Find more on Text File Parser Or get search suggestion and latest updates.




Tagged: