Parse String from textbox to an excel spreadsheet

Question

0.00/5 (No votes)

See more:

Hello,

I am fairly new to VB.NET. I have had some past experience but it was with VB 6.0 and it was not very expansive in nature. I am trying to learn as I go and seem to have found a coding issue that seems easy on the surface but has me at a loss.

Objective: My company receives an email regarding ACH transactions. The info needs to be entered into an Excel spreadsheet, but there is garbage info inside the email as well as repetition. Here is a sample of the email: Underlined are column values, Italics are column Headers.

*Some headers will need to be created as they are not in the text.

Email:
We transmitted to our bank on 09/17/2013 a deposit of $22,663.02 to be transferred to your bank, account number: 40113****

Payment#: 788534

The following invoices have been paid:

~~Invoice~~# ~~Inv Date~~ Invoice Amount Discount Amt ~~Payment Amt~~

~~13100875754~~ ~~08/15/2013~~ $12.30 $.00 ~~$12.30~~

Description: 448143

~~13100877235~~ ~~08/16/2013~~ $27.68 $.00 ~~$27.68~~

Description: 485186---end email

Currently I have created a window form that a user can copy the text from the email and place it into a text box. They would then click a button called "Send to Excel" that will take the text and parse out the useful information and place it in a new Excel spreadsheet.

Is this possible? Is this more complicated than I think?
What do I do now?

Thanks to anyone that can help tutor me on this.

Posted 17-Jan-14 8:27am

Geo @ My Blue Buddha

Updated 17-Jan-14 8:28am

v2

Add a Solution

Comments

SMerrill88 20-Jan-14 14:58pm

Geo:

First, you need to think about architecture. I have three ideas:
(1) Host in Excel VBA. For example, paste the stuff into a blank sheet and run a parsing macro on it. The problem you may encounter is that you have multiple users for the same piece of VBA, which can be a maintenance pain, unless it is distributed as an Excel Add-In.

(2) Host in a standalone exe. If you just want the learning experience of using VB.NET, then consider where you are going to host your VB Windows Form into which the user pastes his stuff. If it is a standalone program (e.g. Parser.exe), then Parser will need to use Excel Interop to talk to Excel. It's not hard, but it is very similar to just writing it in VBA in the first place.

(3) Host it in an Excel Add-In, written in VB.NET. This is harder, but cool.

Let us know how you want to do this.

Geo @ My Blue Buddha 22-Jan-14 9:39am

I believe Option #2 was more along the lines of what I was planning. The exe could be stored in a network folder so multiple users could get to it as needed. (We already use a lot of Excel VBA which started prior to my arrival here, and as you say, it is a major pain trying to keep all these spreadsheets operating smoothly.) I see the proposed application more or less just re-parsing the text and then inserting it into a spreadsheet, however, to simplify, couldn't the "Parser" just write the parsed text to a .csv file? The user could choose the save location and then open it themselves in Excel?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)