Read/Write Raw Data from TXT File

**jasperhuang93** · 07-23-2012, 08:30 AM

Hi,

I have a text file with 20 'columns' or 'categories' and 5470 line items. The file contains raw data for 5470 municipal bonds. What I would like to achieve is to read the data in to VBA in order to aggregate it into its individual categories.

The end result is to ultimately be able to create a UserForm in which the user can input a maturity date of say "2 years" and have the model display all bonds with a maturity of 2 years or less, or input market price $100, and have the model display all bonds with a price of $100 or less.. etc. etc.

I've done much research on this.. but can't seem to find a way to aggregate the raw data correctly, or even read the data as I'd like correctly.. so hopefully somebody here can help me out!

This is my current code for "reading the text" into VBA, but putting a "watch" on my sText shows that every time it loops.. my data gets written over with the next line item. I also figure I need vbTab to split my data.. but don't know how/where to implement it. I have also attached a sample .txt file with 6 or 7 line items.

Please Login or Register  to view this content.

Please advise, any help would be appreciated immensely!

Sample.txt

**JosephP** · 07-23-2012, 08:36 AM

you could open the file in Excel directly or import from it as a data query?

**jasperhuang93** · 07-23-2012, 08:51 AM

Good catch on that - an issue I didn't mention. This text file will be automatically replaced every other date as the database is updated thus making Excel's text input function not an option since I will not be able to constantly update the end-users source file. 5470 line items just happened the be the amount in the system at the time I sampled the file. Will update original post. Sorry about the confusion.

**JosephP** · 07-23-2012, 08:55 AM

I don't see why that would stop you using a query to get the data-you just need to refresh the query each time you open the workbook.

**jasperhuang93** · 07-23-2012, 09:08 AM

Tried using the data query and defining the .txt file as my database.. but it does not import and and separate the information into its correct categories as it should.. Also, I am trying to find a way for the end-user not having to pull the entire set of data into the Excel workbook, as I am nervous that may cause a bottleneck as the database size increases?

**JosephP** · 07-23-2012, 09:43 AM

you can use ADO to extract the data-which allows you run any kind of sql query you want to restrict the data you extract. for instance

Please Login or Register  to view this content.

note the schema.ini file is necessary since the data is tab delimited and not comma delimited.

**Kyle123** · 07-23-2012, 09:51 AM

Good answer, you can also replace:

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

**jasperhuang93** · 07-23-2012, 09:53 AM

Thanks a lot Joe, however running the code in excel produces a "Run-time error '3706': Application-defined or object-defined error" @ the ".Open" line of code.

This is the code as is in my VBE

Please Login or Register  to view this content.

**Kyle123** · 07-23-2012, 09:57 AM

You'll need to use the Jet provider if using 2003 as below:

Please Login or Register  to view this content.

**JosephP** · 07-23-2012, 10:00 AM

oh yeah-thanks, Kyle. I forget there are still people using stuff that old. ;-)

**jasperhuang93** · 07-23-2012, 10:30 AM

Getting solved step-by-step! The code almost runs! It's really unbelievable how little I really know about VBA, so thanks for all of the help.

Problem is, I'm still getting an error, now at the line that begins with ".Range" in the below section of the code -- "Run-time error '13': Type mismatch"

Please Login or Register  to view this content.

**Kyle123** · 07-23-2012, 10:39 AM

Application.Transpose doesn't like long fields.

Try changing all that you put in the post above to:

Please Login or Register  to view this content.

**jasperhuang93** · 07-23-2012, 10:48 AM

Works a charm!

Now, a userform can definitely be made to reference the sheet that it outputs the text file to.. but is there any way to have the user for pull in data without having to physically output the data from the text file into the workbook? Because the amount of data in the raw data is only going to increase..

**Kyle123** · 07-23-2012, 10:50 AM

Yes. But I think you'd be better off using a database rather than a text file. Have you thought about going down that route?

**jasperhuang93** · 07-23-2012, 10:56 AM

As in using the built-in database query function? I've tried that.. and I can't get the data to output correctly as in the code above.. and doesn't that method also output the data to the workbook?

**Kyle123** · 07-23-2012, 10:59 AM

No, as in a real database, Access, SQL Server, MySQL etc

**JosephP** · 07-23-2012, 11:00 AM

you could output the data to a listbox on a userform for instance. assuming code in the form itself you would just change the output part to

Please Login or Register  to view this content.

with no need to populate a sheet anywhere.

Read/Write Raw Data from TXT File

LinkBack

Thread Tools

Rate This Thread

Display

Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Re: Read/Write Raw Data from TXT File

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions