D
Donal
My first post and well I'm hoping someone will be able to assist me
with this ...
Customer supplies xls file containing referencIes, addresses, pallet
quantities, pallet spaces and job specific freeform text - this file is
supplied upto 4 times daily with each transmission bringing either
additional lines or confirmed pallet quanities not present on previous
transmission.
I have created a function which converts this data into true csv as a
complex data map which takes this information and maps it onto a 48
field csv file (the other data items are either duplicates of supplied
data, customer collection information (which is constant) or
concatenated data used for comments.
I created a worksheet within the utility which as well as mapping the
information into a format our Operations staff can use for planning
purposes but also complete one of the columns with details of the
vehicle which will collect the goods from the customers site. This
sheet is returned by email to the customer so that they are advised
which pallets/consignments are to be loaded on which vehicle.
All this works perfectly at the moment and the data is received and
exported out as a csv file (which has to be a specific file name to
facilitate its imporation onto our traffic system). This is the only
method of getting this data onto the system other than manual entry.
(It is written in COBOL and runs in DOS but, is an industry standard
application ... RoadRunner.)
I hope you're with me so far ...
The customer has now opted to have their system generate the data
automatically and transmit it on a scheduled basis. No biggy ... but
.... this file will, when generated be sorted on the customers reference
and will always be in ascending order. Therefore should an order be
released to distribution with a number falling within the range of
order numbers already received will push the data down by one row.
When this data is pasted back into the import tool it means that the
vehicle data does not align with the original line - I need to think
about copying the original sheet and looking up the reference to see if
it's been used before. If it has copy the contents of the original
sheet and paste that vehicle data into the new sheet. If it hasn't
leave a blank space.
I guess I've figured out a way of doing it already but, I just wanted
to check my logic.
Current import tool contains ...
1- Paste data here tab (which is where customers data is placed)
2- Conversion section (begins the mapping process)
3 - Manifest (which is the sheet used by Ops for viewing and completing
collection vehicles)
4 - Return Document - formatted copy of manifest with vehicle data
which is mailed to customer.
5 - Warehouse copy - used internally for warehouse to check off goods
inbound from customer.
6- Export from here - literally the final 48 fields (in columns) from
which a true csv file is written on to a hard-wired location for pickup
by Traffic System import module.
7- CSV conversion utility.
Oh ... when the data is imported it can only be imported once - the
data imported must be unique each time and therefore it cannot be
utilised to update any modifications. It can only be used to put new
data onto the system (obviously we hold off on an import until either
the data set is complete or operational restrictions dictate that we
must.
So other than dumping the traffic system, the spreadsheet and the
customer I have to work with what I have ...
Now I'm not expecting a miracle answer but, opening up the challenge -
I've probably omitted lots but, I'm hoping with some time I can paint a
picture ... if anyone is interested I can supply sample data and the
spreadsheet itself ... you only have to ask and I'll create a file
share somewhere.
Let the challenge begin ... or not ...
Phew - and thanks for reading this far
with this ...
Customer supplies xls file containing referencIes, addresses, pallet
quantities, pallet spaces and job specific freeform text - this file is
supplied upto 4 times daily with each transmission bringing either
additional lines or confirmed pallet quanities not present on previous
transmission.
I have created a function which converts this data into true csv as a
complex data map which takes this information and maps it onto a 48
field csv file (the other data items are either duplicates of supplied
data, customer collection information (which is constant) or
concatenated data used for comments.
I created a worksheet within the utility which as well as mapping the
information into a format our Operations staff can use for planning
purposes but also complete one of the columns with details of the
vehicle which will collect the goods from the customers site. This
sheet is returned by email to the customer so that they are advised
which pallets/consignments are to be loaded on which vehicle.
All this works perfectly at the moment and the data is received and
exported out as a csv file (which has to be a specific file name to
facilitate its imporation onto our traffic system). This is the only
method of getting this data onto the system other than manual entry.
(It is written in COBOL and runs in DOS but, is an industry standard
application ... RoadRunner.)
I hope you're with me so far ...
The customer has now opted to have their system generate the data
automatically and transmit it on a scheduled basis. No biggy ... but
.... this file will, when generated be sorted on the customers reference
and will always be in ascending order. Therefore should an order be
released to distribution with a number falling within the range of
order numbers already received will push the data down by one row.
When this data is pasted back into the import tool it means that the
vehicle data does not align with the original line - I need to think
about copying the original sheet and looking up the reference to see if
it's been used before. If it has copy the contents of the original
sheet and paste that vehicle data into the new sheet. If it hasn't
leave a blank space.
I guess I've figured out a way of doing it already but, I just wanted
to check my logic.
Current import tool contains ...
1- Paste data here tab (which is where customers data is placed)
2- Conversion section (begins the mapping process)
3 - Manifest (which is the sheet used by Ops for viewing and completing
collection vehicles)
4 - Return Document - formatted copy of manifest with vehicle data
which is mailed to customer.
5 - Warehouse copy - used internally for warehouse to check off goods
inbound from customer.
6- Export from here - literally the final 48 fields (in columns) from
which a true csv file is written on to a hard-wired location for pickup
by Traffic System import module.
7- CSV conversion utility.
Oh ... when the data is imported it can only be imported once - the
data imported must be unique each time and therefore it cannot be
utilised to update any modifications. It can only be used to put new
data onto the system (obviously we hold off on an import until either
the data set is complete or operational restrictions dictate that we
must.
So other than dumping the traffic system, the spreadsheet and the
customer I have to work with what I have ...
Now I'm not expecting a miracle answer but, opening up the challenge -
I've probably omitted lots but, I'm hoping with some time I can paint a
picture ... if anyone is interested I can supply sample data and the
spreadsheet itself ... you only have to ask and I'll create a file
share somewhere.
Let the challenge begin ... or not ...
Phew - and thanks for reading this far