Data Sort and Manipulation

[timitfc]

Hi All,

I'm using a third party database software which handles jobs, orders, etc but also does invoicing.

Now this is where I would like some help, attached below is a sample of the invoice is stored.

Currently the invoice is printed out in courier font and looks exactly like it does in the InvoiceText field. Now the problem that I want to produce my own invoices from this, but the only place where the Sch No, with the description, quantity and price is stored is in this InvoiceText Field.

To make matters more annoying Schedule number doesn't always start on the same line, sometimes the description runs over two lines and the gap between line numbers doesn't always increase by 1.

What is the best way of getting this data? Once I have got to the data then I need to split it up, probably using Instr (unless anyone else knows of a better way) to then store this in a more manageable & usable way.

I would appreciate any help you may be able to give me!

 

[Norie]

Where is this data coming from and in what form?

What exactly do you need to extract and how will you want to store it?

What you've posted doesn't actually look to complicated.

Assuming the main part of the data you want to get is the scheule no, quantity and price that is.

Though when I read the rest of your post I think I must be missing smething.

In the example data there is no space between each 'record' but you seem to be saying otherwise and that there could be more than one space.

Can you post some data that shows some of the possible variations?

 

[timitfc]

Hi Norie,

Thanks for your response.

Ok attached is a .csv which has the data in it, it has more than in the screenshot as I thought showing less would be less confusing!

http://www.megaupload.com/?d=CYIYOYM9

The third party software stores the information in tables, this is a query of that information (as it's not stored in the same table).

What I would like to extract is schedule no, qty & price and then store this in a new table so that we can make our own invoice- currently there is a lady in accounts who spends 1.5 days a week transferring from this format to one that we can actually send to our client!

The upload shows when the description is split over two lines.

Many Thanks!

 

[Norie]

Do you have access to these tables?

If you do you could just import the data to Access and create your invoices there using reports.

Right I've downloaded the file - it's not a csv.

Is it a csv or an Excel file you actually receive?

 

[timitfc]

Sorry i really am confusing you!

Ok, so I have access to the tables via Access- i just used the excel file so you can see what the data looks like, and how the InvoiceText field is laid out.

I don't think i mentioned this before, i basically want to loop through the data spliting it up and putting it into another table. So i want to be able to run this on more than just one invoice.

 

[Norie]

Is this data from some sort of legacy database?

Is INVOICE_TEXT a field with the first record being TEST JOB, then a blank record, then COMPLETION TEXT and so on?

I know that data's probably not important, just trying to establish what you actually have here.

Is this one invoice?

<TABLE style="WIDTH: 439pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=585><COLGROUP><COL style="WIDTH: 439pt; mso-width-source: userset; mso-width-alt: 21394" width=585><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 439pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=585>SCHED.No. DESCRIPTION QTY PRICE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>________________________________________________________________________________</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>TEST 2 TEST SCHEDULE 2 1.00 25.00 25.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>TEST 3 TEST SCHEDULE 3 1.00 36.00 36.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>TEST 4 TEST SCHEDULE 4 1.00 7.00 7.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>TEST1 TEST SCHEDULE 1 1.00 10.00 10.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17> NETT TOTAL # 78.00</TD></TR></TBODY></TABLE>

And you want something like this?

<TABLE style="WIDTH: 280pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=372><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=78>SCHED.No.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=131>DESCRIPTION</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=78>QTY PRICE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=38>QTY </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=47>PRICE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>TEST 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TEST SCHEDULE 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>TEST 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TEST SCHEDULE 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>36</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>TEST 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TEST SCHEDULE 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>TEST1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TEST SCHEDULE 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10</TD></TR></TBODY></TABLE>

Perhaps without the PRICE column.

 

[timitfc]

Yea I think you have got it!

Inv_text is a field in which all that data is stored and I want that in a table like the one you have shown below, with the invoice number added to it as well.

I really appreciate your help!

Tim

 

[Norie]

Tim

So what is the invoice number?

In the file are there multiple invoices or is it all one invoice?

Sorry for all the questions and no real answers, not had much time to have a proper look today.

 

[timitfc]

The invoice number can be found in the first column of the table, eg, so for the invoice that you have shown 126955 is the invoice number.

In the table there are multiple invoices and the table currently holds near on 400k records..... The invoices from here have all been re typed by hand up until this point so obviously if we can help automate this then we can free up some time!

Any other questions do just ask, if any sort of messaging client is better for you just send me a PM.

And obviously I understand if you didn't have time yesterday, or today for that matter!

 

[timitfc]

This is route I'm trying to take with the coding.

Run a function which finds '__*' in the invoice_text field, this appears to be constant throughout the majority of the table (there are few "freetype" invoices where there are no schedule numbers, no order to how they are put in- these are also going to be a pain BUT I'm planning on ignoring them for now and just got part of it working!!)

When the function finds '__*' then we know that the next record is going to have schedule no's in it. (This is where i'm not 100% sure how to split it).

I'm trying to use a rst to search through the records and currently just print the KEY_LINE, it doesn't appear to be finding the records, just running through the rst printing the line number, not just those where INVOICE_TEXT = '__*'

Public Function InvoiceSort()
Dim rst As DAO.Recordset
Dim strsql As String

Set rst = CurrentDb.OpenRecordset("qryInvText")

While Not rst.EOF
rst.FindNext "Invoice_text='__*'"
Debug.Print rst.Fields("KEY_LINE")
rst.MoveNext
Wend
Set rst = Nothing
End Function