Excel data extraction

anksta · 23 Mar 2011 at 19:27

Alright, I'm workin freelance at the minute and have to invoice my work, I'm copying their data from their website and it comes in just a single line and I want to separate the data, spent 3 hours today coming up with VB code to separate it in batches but it was proper dodgy and only just worked. Was way too over complicated for it to just work line by line but now I've got it all sorted I want a function so that when I put them in one by one I need a routine that will just separate them, can anyone help me out?

It comes like this

12272 PSP34376 Peter Alan Rhiwbina Available Coronation Road Birchgrove Dominic Ladden Awaiting Approval 08/03/2011 23/03/2011

and I need it separating into number, reference, agent, office, status, address, writer, date created and date modified.

I could do it everything was the same length easily but with the references, offices and address all being different lengths I'm proper struggling with it and I just thought I'd ask the seasoned programmers if they could just whip me up a bit of code to sort it out instantly.

Cheers

SimonCHere · 23 Mar 2011 at 19:33

If it is coming from their website it would be easier to copy it from the source of the page rather than just what is displayed. Then you should get at least <TD> separators to work with or something.

sist_si · 24 Mar 2011 at 18:17

I presume the data has to go into a new database?
if that's in an excel spreadsheet and you have SQL Server, you can do a data import directly from the excel file to a table in the database.

Pr0t0c0L · 28 Mar 2011 at 16:04

have you heard of groovyscript? if you attach the data file, il write quick script

Souleh · 30 Mar 2011 at 10:48

Do you still need a script for this? Can write one for you if you clarify a few things

'Separate single string into "number, reference, agent, office, status, address, writer, date created and date modified"

'12272 PSP34376 Peter Alan Rhiwbina Available Coronation Road Birchgrove Dominic Ladden Awaiting Approval 08/03/2011 23/03/2011

Invoice Number = [12272]
Invoice Reference = [PSP34376]
Agent = [Peter] [Alan] [Rhiwbina] ?
?? = [Available] Is this needed?
Office = [Coronation Road Birchgrove] Do you have a list of available offices?
?? = [Dominic Ladden] Is this needed?
Status = [Awaiting Approval] Do you have a list of available status'?
Creation Date = [08/03/2011]
Modified Date = [23/03/2011]

Once I have this info its easy enough in VBA

SimonCHere · 30 Mar 2011 at 11:37

Souleh said:
Do you still need a script for this? Can write one for you if you clarify a few things

'Separate single string into "number, reference, agent, office, status, address, writer, date created and date modified"

'12272 PSP34376 Peter Alan Rhiwbina Available Coronation Road Birchgrove Dominic Ladden Awaiting Approval 08/03/2011 23/03/2011

Invoice Number = [12272]
Invoice Reference = [PSP34376]
Agent = [Peter] [Alan] [Rhiwbina] ?
?? = [Available] Is this needed?
Office = [Coronation Road Birchgrove] Do you have a list of available offices?
?? = [Dominic Ladden] Is this needed?
Status = [Awaiting Approval] Do you have a list of available status'?
Creation Date = [08/03/2011]
Modified Date = [23/03/2011]

Once I have this info its easy enough in VBA

See that is where the problem is you can't tell where a field starts and ends.

Invoice Number = [12272]
Invoice Reference = [PSP34376]
Agent = [Peter] [Alan]
Office = [Rhiwbina] ?
Status = [Available] Is this needed?
address = [Coronation Road Birchgrove] Do you have a list of available offices?
writer = [Dominic Ladden] Is this needed?
Status = [Awaiting Approval] Do you have a list of available status'?
Creation Date = [08/03/2011]
Modified Date = [23/03/2011]

Souleh · 30 Mar 2011 at 14:12

ah yes ofcourse.

Well if I had mapped values for all possible values except the number references/dates then I could do it easily enough. Otherwise you could just make it so it will prompt the user "Add next de-limited tab" to get the right length etc.