Up for a challenge....



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

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

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

Harlan Grove

Donal wrote...
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.

In a nutshell, your existing system has been based on ordering details
in the order in which those details were received, but now your
customer/client wants to change the ordering to some other key. You
want to avoid a full rewrite.

As long as there's some way to identify records other than text order
in the data feeds you receive, you should be able to fetch data using
lookup functions rather than copy/paste.

If you need to return schedules to your customer/client in their new
ordering, all you need to do is sort your existing schedule in your
customer's/client's ordering. You may only need to create a new
worksheet that shows your customer's/client's indentifying keys in
their specified order, then uses lookup functions to fetch information
from your existing schedule.


erm ... exactly - sorry if I stretched it a little but, yes you are

I guess I just need to knuckle down and resolve it ...

Thanks again ...

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question