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
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