PDA

View Full Version : Import Txt and Parse



francis
09-07-2007, 07:32 AM
Hi all,

I have a txt file which I export from one of the application, I have attached the txt file and I need to import this into excel and have the data parsed and end up with the result which show in the sample excel file.

Is it possible to do this via VBA. All help is appreciate.

cheers, francis

RonMcK3
09-11-2007, 08:54 PM
Hi all,

I have a txt file which I export from one of the application, I have attached the txt file and I need to import this into excel and have the data parsed and end up with the result which show in the sample excel file.

Is it possible to do this via VBA. All help is appreciate.

cheers, francis
Francis,

This is entirely do-able. I have crafted a solution (see attached file).

I let Excel Macro Recorder do most of the heavy lifting for me. After I had the basic "process", I modified it so it will (should) work with any file you feed it.

I want to thank Johnske for the File Open routine and the code for adding this VBA program to the Right-Click menu. Both of these features came from his Import notepad and wordpad text files.

So, to use your new program:
Unzip the attachment.
Copy the Excel file to wherever you want to keep it.
Open the File.
Right click on a cell (top left corner is fine).
Select Import Account Data on the drop down menu.
Excel will give you a File Open dialog.
Select the folder and file to Open.
The program will import the account data.
The program saves the file as an XLS so your original data is preserved.
Let me know if you have any questions.

francis
10-26-2007, 11:45 PM
Hi Ron,

This is excellent. preliminary test seem to have work well. Let me test on actual data and I will post back should there be any probelm.

I really appreciate your assistance in this and the time you have spent on this. I've thought that this is impossible as no one have responded.

Many Thanks to you once again.

cheers, francis

francis
11-04-2007, 09:49 AM
Hi Ron,

I have test on actual data and I am afraid to say that that it doesn't work as expected. The result shows only some of the lines which I expected them to be deleted.

I am noy sure the dummy data work very well and the actual ones don't

regards, francis

unmarkedhelicopter
11-05-2007, 01:59 AM
EDIT : Thought to be a cross post from JMT (My mistake :( )

francis
11-05-2007, 02:11 AM
Hi

It is not the same as I have asked in JMT, over there I am asking about
extracting unique entries from two workbooks and here is about importing a txt file and parse the data to the format I wanted.

But... I am not sure what is cross post.:dunno

regards, francis

unmarkedhelicopter
11-05-2007, 02:20 AM
In that case my appologies.
A cross post is where you post the same question on multiple boards without notifying the different boards, thus you may get the question solved on the first day a board A but board B continues to work on it for the next week so it can lead to wasted effort. This is considered very rude.

RonMcK
11-05-2007, 09:54 AM
Hi Ron,

I have test on actual data and I am afraid to say that that it doesn't work as expected. The result shows only some of the lines which I expected them to be deleted.

I am noy sure the dummy data work very well and the actual ones don't

regards, francis
Francis,

Would you please upload a zip file with samples of data that failed to import so I can examine? It will help us if you can include several different examples.

Thanks!

Ron
Orlando, FL

francis
11-06-2007, 08:12 AM
Hi Ron,

I have attached an actual dummy sample ( txt file ) and an excel file which show the result that I am looking for.

I want to extract those entries under UTx which have a heading under ARM in the text file, it would run from UTA to UTZ.

thanks for your assitance


regards, francis

RonMcK
11-06-2007, 08:33 AM
Francis,

Thank you. I downloaded a copy and will look at it later this afternoon.

Ron
Orlando, FL