Loop Through Input File that does not contain field names and data is in different rows within a single column; Field1 (1 Viewer)

[chip0105]

I have a Report file exported to text that contains numerous rows of information that will not be used. When the data is imported, it all gets dropped into Field1 in my table as the exported data does not have any reliable delimiters. In the middle of a record line I have a field I can collect my ID data information using the mid([Field1], 50,9) and the criteria; Like "* ID: *". I then need to collect data in subsequent rows until another instance of the ID: data is found. The last field before the next ID data will be a Total Field where I again can use the mid function to collect several pieces of information needed from that line.

I know this is quite simple for other Access Developers, but I haven't used the Loop function before and am struggling to create my VBA code.

Any assistance would be greatly appreciated.


With Regards,

Chip

 

[bastanu]

The easiest way to get help on this is to post a sample, remove any sensitive info. You can probably use the Split(), Instr() And Replace() functions to parse your report but can't give you more details without seeing it.

Cheers,

 

[gemma-the-husky]

How can it have no reliable delimiters. Is it like a .prn, print output?

If so, it's worth investigate a product called monarch. You can map print files to generate a usable csv.
Excellent when I used it years ago.

I think it's this now.
Self-service Data Preparation Solution | Altair Monarch

 

[chip0105]

My apologies. Please see a modified copy of my sample data that I am working with.

 

[chip0105]

How can it have no reliable delimiters. Is it like a .prn, print output?

If so, it's worth investigate a product called monarch. You can map print files to generate a usable csv.
Excellent when I used it years ago.

I think it's this now.
Self-service Data Preparation Solution | Altair Monarch

We currently have Monarch (a way old version; v7). However, our future state will no longer have Monarch to use. This would have taken me less than 5 minutes to set it up using Monarch. However, that will not be an option moving forward.

 

[bastanu]

Have a look at my free utility that has a parser that would work: http://forestbyte.com/ms-access-utilities/fba-outlook-email-parser-importer/

You need to define the keywords and extract the data between to consecutive keywords. For example ID value is found between the "ID:" and "Currency:" keywords.

Cheers,
Vlad

 

[gemma-the-husky]

I can see your problem. Monarch solved a tricky problem, that will be hard to replace.

 

[jdraw]

It appears you are working from pre-processed and formatted data.
Can you get the source data in some format??

If not, then Vlad's utility may be your best option.

 

[bastanu]

Hi Chip,
Please have a look at the attached sample, needs some check and error coding but should get you closer to your desired result.
Cheers,
Vlad

 

[bastanu]

Here is a slightly revised version that captures the tabular data and the total line in a couple new memo fields.

Cheers,
Vlad

 

[chip0105]

Vlad,

Sorry it has take so long to get back to you. After discussing with my User, I checked/unchecked some of the field selections very easily using the template process in your design and it works exactly as I needed it to.

Thank you VERY much for all your assistance. It is GREATLY appreciated!

Chip

 

[bastanu]

You're very welcome Chip!

Good luck with your project and stay safe!

Vlad