Import Text File VBA Script

If I were you, I would take the simplistic approach ... import the raw data as it stands and then see what you need to do to manipulate it. The first part should be very straightforward ... just record a macro as you open or import the file.

Regards, TMS

If you want to read the text file, you would have to read the line, test it and do something with it. If you open it in Excel it will all come in in one go. You could use fixed width columns to split the data or use a delimiter ... space, maybe ... to split it up. Then you can decide what to do with it. You could use AutoFilter to select specific types of data ...

Lots you can do in Excel but you'd need a bespoke VBA application to read and process the text file.

Regards, TMS

Just so that you know I haven't forgotten about you ...

The following code assumes that the raw data has been imported into the Data sheet.

It will create a Criteria sheet and an Output sheet, if they do not exist, populate the Criteria sheet with Advanced filter selection criteria, filter the raw data to the Output sheet and then removes duplicate repId entries.

The final stage would be to process the filtered data in the Output sheet and format it in the results sheet. And maybe delete the Criteria and Output sheets. Maybe tomorrow

As it stands, the code has reduced the raw data from 265160 rows to 1280 rows; less than 5%.

The only quirk is that there are some repId entries where "successful" was False. These can be ignored/eliminated in the final stage of processing.

Please Login or Register  to view this content.

Final code. Try this:

Please Login or Register  to view this content.

A better version ... this ignores success=false records

Please Login or Register  to view this content.

My code assumes that you have opened the text file and it is in a sheet named Data. It then creates a a Criteria sheet, an Output Sheet and a Results sheet. It uses the Criteria sheet to drive an Advanced filter, copying the filtered data to the Output sheet. It then processes the Output sheet to create the Results sheet in the format that you have indicated. I note that you have added some formulae to indicate the rows of the success tags. Finally, it tidies up after itself so you are left with the source data and the Results summary.

Watersev's code processes the text file directly.

It is interesting to note that the original text file (processed in Watersev's code) is some 300 lines. The data in the later Excel spreadsheet is around 26500+ lines.

It all depends on what you need to achieve.

I haven't done a time comparison between the two solutions but it might be worth doing that.

Regards, TMS

Forget it. Done the time trial ... that is quite impressive!

aharb: the file I was using is attached.

See if this works for you.

Regards, TMS