Macro, VBA to import dat file and sort rows based on Criteria

[mattberro]

Hi there everyone,

I've had to resort to asking this question after some failed attempts at copying, pasting, manipulating vba/macro/formulas, creating my own formulas etc.

I have been asked by my line manager to create a spread sheet from data that is outputted from a third party software system. This system outputs the data into a DAT file, although looking at this file it is comma delimited with ' as text wrappers; you can use MS Excell/Access import utility to bring the data in. There are no headers to the comma separated fields, but there are three distinct and constants that begin the data rows. An example is:-

TASK-DESPATCHED,data,data,data,data,data,data etc
TASK-DESPATCHED-RESOURCE,data,data,data,data,data,data etc
TASK-DESPATCHD-SCHEDULED-ITEM,data,data,data,data,data,data,data,data etc

These lines repeat throughout the file, the TASK-DESPATCED-SCHEDULED-ITEM line may repeat before the line TASK-DESPATCHED restarts.
The file does start with a HEADER statement, but this just gives the file name, date, time etc. This line can be ignored.

Here's a link to an uploaded dat file with the actual data and format and an example of the output Excel file that I am looking for:-

https://office.1and1.co.uk/guest?pa...4hGVc9QSu6rIPezpvTmg&loginName=info@epb.me.uk

The final solution that I am looking for is a macro, vba or formula to import the dat file (the user will have to select the file manually as the name changes on each output). The application will then look at each line and populate a series of three worksheets in the format of the uploaded Excel file (see above link).

Ideally to save confusion the workbook should purge itself before opening or closing to save user confusion when the spread sheet is re-opened the following day.


I then intend to link an Access report to these worksheets to create operative worksheets, that shouldn't be an issue as i'm not too bad at Access (says he hopefully not blowing his trumpet too early )...

I have managed to get a spread sheet to pull the lines apart by using IF statements and populating three seperate worksheets. This leaves blank rows and for some reason Access is having kittens trying to populate the report correctly. I've used a few examples (both vba and formulas) from this site to delete blanks rows, copy rows with conditions, import only set row data from the raw dat file etc. All of these with varying, but not complete results.

As i'm now at my whits end, can anybody come up with a viable solution.

Your help would be greatly appreciated by both myself and my wife, who has tirelessly listened to me drone on about this and watched whilst I toil away until stupid hours of the night.

Many thanks to any kind of response

Matt

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you Autofilter the data from the DAT file for each of the three descriptions in column A and copy the visible records to the relevant worksheet? Before you can filter you will have to replace the existing headings in row 1 with something like Col1, Col2 etc.

 

[mattberro]

Hi there Andrew,

Thanks for the quick reply. That is an option and one that I have tried. The main issue is that this process is being done by other users and not just myself. These users are shall I say, not computer literate. They are also rebelling against pay cuts and so are not helping or helpful in anyway that you can imagine.
I was hoping for a simple two - three step procedure that is automated as much as possible.
If the Autofilter could be made into a macro, that would work. But I am unfamiliar with recording macros and to be honest have now extinguished too much time on this project.

Cheers

Matt

 

[Andrew Poulsom]

You would need a macro. If you are unable to write one you should pass the project on to someone who can. What you are asking for is a bit too much for a free help forum in my opinion. If you are able to create a worksheet with the DAT file data and amended headings and blank target sheets with headings, I can help you with the AutoFilter code.

 

[mattberro]

That's a fair comment Andrew.

I will look to see if there is anybody with that skillset around my workplace.

Many thanks

Matt