PDA

View Full Version : Parsing a text file



ChandlerW
12-20-2012, 07:28 PM
Greeting all,
I am working with a software program that was written in 1995. The program generates a report and formats the report on 3 lines.
I do not have an option of changing the report output.
I am able to email the report and it is attached as a text file.

What I would like to do is import the text file into excel and then generate a pivot table based on the data in the report.

The pivot table is not working for me since I need columns with specific names. I'm thinking if I am able to clean up the output and stick it on one line, then import the data it will be cleaner.

My thoughts are to read one line, stick it in a string, read the next line, stick that in another string, read the third line, and put all three strings in a string variable and write it out to a text file.
Then I can import that text file into excel, insert my pivot table and go from there.

What do you all think? Is there an easier way to do what I need?

snb
12-21-2012, 01:25 AM
post the file here, so we can have a look.
Change the file's extension into csv and open it in excel; see what the result looks like.

ChandlerW
12-21-2012, 07:03 AM
Thanks for the replies. I tried the .csv thing and it didn't work. It came out just like the .txt version.
I shortened the text file but retained the text structure and have attached it.

The file is a little cryptic. The data is a little scrambled but the postions are correct. There is sensitive information in the actual output such as last names and first names. I replaced those with FNAME and LNAME. and made a few up too.
The rows of data I am interested in start with the string ZXZXA and end at the next instance of ZXZXA.
If the format is "ZXZXA N1234 ZXZXA" the second ZXZXA doesn't count. Its the third instance of ZXZXA that is around 300+ characters later in the string.

It may be possible to grab the string data based on number of characters?
Maybe find the string "ZXZXA" and then grab everything that is x amount of characters?

Edit: I used Wordpad to open the document with no Wordwrap.

ChandlerW
12-27-2012, 05:37 PM
Still having trouble with this.
Tried Instr()

Kenneth Hobs
12-28-2012, 08:48 AM
What you need to do is to parse it at fixed locations as I did in this: http://www.vbaexpress.com/forum/showthread.php?t=43367

The DOCX file should be saved as a TXT file to see the locations for the fixed width. Fixed width is the old style of formatting used in mainframe programs of the past and some are still used today. Most people like something more useful ergo your request I suspect.

ChandlerW
12-31-2012, 09:35 AM
Thank you for the helpful link.
I'm having a problem with the data.
Sometimes the data I want it on 3 lines. The lines look like this:
ZXZXA N1234 ZXZXA 155...etc.
That is the start of a line.

Then there are lines that are in the following format
ZXZXA 155.... etc.
These lines are only 2 lines of data.

Maybe use Instr() to search for ZXZXA in the positions 1-6 and 16-19?

The second line would show ZXZXA in positions 16-19?

GTO
12-31-2012, 12:45 PM
Greetings Chandler,

Presuming the data is non-sensitive as shown, could you zip a couple of the actual text files and attach the zip?

If there is sensitive information, substitute fake info of the same type/length, trying not to 'erase' any non visible characters.

Also include what the output would be, preferably in .xls format.

I'm zero at pivots, but I think this would help any 'helper'.

Mark

ChandlerW
12-31-2012, 03:28 PM
I'd like to say thanks to everyone that helped out. I managed to put together some ugly code that works.

Great forum. I'm sure I'll be here often.