I attached part of Unstructured Data I have from my clients, I need to convert this into a Structured Table.
Table has to have these 8 fields:
1) Customer Number,
2) Transaction Number,
3) Transaction Date,
4) Remark,
5) Invoice Amount,
6) Pay Amount,
7) Discount Amount,
8) Customer Balance
Here is what you see in the file (I included only 2 Cutomers):
OSCER BLEGOITZ is the Customers Name
1234-88TH STREET is Customers Address
BROOKLYN NY 1121 is C/S/Z
On the same Line of the Customers Name there is: "1234567 04/14/10 718 555 1212 1"
"1234567" this is the Customer Number, this has to go into "Customer Number" Field, for each line that belongs to this Customer.
The rest on that line we don't have to import.
Now the Data:
Info from Column 1 Goes into "Transaction Number" field
Info from Column 2 Goes into "Transaction Date"
Info from Column 3 Goes into "Remark"
Info from Column 4 Goes into "Invoice Amount"
If Remark (Column 3) = "Discount" then Info from Column 4 Goes into "Discount Amount" field and Trans date is the same as the payment date above,(as you could see Discount is only with payment, so discount date is same as payment date).
Info from Column 5 you could ignore
Info from Column 6 Use ONLY if Remark (Column 3) = Payment, enter info into "Transaction Date"
Info from Column 7 Use ONLY if Remark (Column 3) = Payment, enter info into "Pay Amount"
Info from Column 8 Use ONLY If Remark (Column 3) = * F.C., enter Info into "Invoice Amount"
Info of the line "PLEASE PAY BALANCE OF...................." enter into "Cust Balance"
We will need the Program or Macro or what ever you make to convert this data into a table, so we could use it in our office for all of our customer data.
We will also need the source code so we could modify the code if we need to change any thing in the source.
We will need a demo to make sure it does the job.