Text to Excel Conversion using VBA or Macro

Hi,

I have a text file which I would like to convert it into excel sheet using VBA or Macro. The issue is that the data I get on daily basis is in text file and is too huge as it is a report of some software application. I am sending you a sample report and how it will look after conversion for a better overview of the problem. As I mentioned above I need to convert the text file into excel worksheet.. I need a VBA that will eliminate all the irrelevant data from the text file and pick only the required data and paste it in the relevant Excel spreadsheet column.. Ps assist.. Please help me with a VBA code for the same.

Thanks in Advance..

Your checking.xlsx file doesnt really provide any help though, how do you want the data formatting?

If it was me needing this then Id put all the possible codes and headings along the top (I assume theres more possible fields than just those in the example file).

Then read the file in line by line checking if the 4th character is a :, if so assume its a header line and cycle through the headers until you find the matching one.
The keep pulling in lines from the file until you hit another 4th char of : When you get that output the data to the relevant column

If you get the words "BLOCK 4" increment your output row and continue on to start a new record

If you do a search on "Line Input #" you will find ways to read through the text file. You will also see some examples where the line is parsed and stored on a spreadsheet.

Hope this gets you started.

You will find all you need in this post: http://www.excelforum.com/excel-gene...using-vba.html

This post has a post about using the open file. I particularly like the one from tony h http://www.excelforum.com/excel-gene...le-issues.html

First you have to decide how to determine which lines to keep:
Maybe they are in a regular format and you need to keep every 2nd or 4th line. Maybe all lines where the 3rd,4th and 5th characters are zero.

Then apply the test after each line input to either save the value to the spreadsheet or ignore it and do the next line input

Can you please provide the code for some values retrieving from the text document. That would be great help sir.
Also I have updated the Sample Text and Excel File. Please look into it then you will exactly know what I'm trying to tell you sir. Thanks.

See attachment as well.

Allows multiple file selection.

Not much error checking since you only provided one SHORT text file.

Please Login or Register  to view this content.

but the Button is not working.

On the sample file I uploaded? Make sure you enable any yellow security button that comes up.

If you have the code stored in a different location than Module1, you have to go into design mode, double click button and point to wherever the code is.

Button is working now. But the problem is its showing the Sender's Reference in Exponential form. Let's say if the value is "2016010800145698" its showing "2.01601E+15". Secondly, its showing only Date from the 32A and the amount value of 33B.

Can you please look in the sample excel file please? That would be great help. I tried editing your code but I'm getting multiple errors whenever I try to edit.

ts showing "2.01601E+15"

Select the column, Ctrl-1, select General.

Please Login or Register  to view this content.

Great work sir. Thank you so much.


Last thing, sir please look at the sample_text_file_final.txt‎ and make code compatible to it, it has the same data but some data is stopping the script from moving ahead the receiver option.. you'll have to do a little editing and it will be done. One last favour, if you're available.

Please Login or Register  to view this content.

Sir the code not going further the sender and receiver field and its only retreiving the first sender and receiver. Sorry wrote it twice.

it's working on the sample file.

That's the one I was referring to.

In the future, attach files to the most recent message, not the first one.

Please Login or Register  to view this content.

Cross-posted at: http://www.mrexcel.com/forum/excel-q...ons-macro.html

Please read the Cross-Posting policy in rule #8: http://www.excelforum.com/forum-rule...rum-rules.html

Mr. Tinbendr A little Question.. I'm trying to add the Transaction Ref. column also and the code I did to add that column is this,

Please Login or Register  to view this content.

Its fetching the Transaction Ref correctly but the problem is that its fetching like this "Transaction ref. = XXXXXXXXXXXXXXX1" and I only want "XXXXXXXXXXXXXXX1" part.. what should I add to remove the Transaction ref. part and just get the real reference value sir..

Since this is a fixed/defined length line, I think the mid function will serve better.

The format will always be 'Transaction ref. = (a 17 digit number)'.

So, we just need to define the parameters of the mid function.

Please Login or Register  to view this content.

I guess I was more tired than I thought last night.

I dropped the 'line' off of 'Line Input #FF'. This made the variable read UP TO a comma. Hence this is why the amount wasn't returned correctly.

I also went back to using the Split on the records that have a colon/hash make in them. It's a little more robust.

Since I had to back up a little with the code, be sure to check previous 'fixes' to ensure the output is correct.

Please Login or Register  to view this content.

Everything is working fine now. Thank u so much.