Extract specific data from a text file (1 Viewer)

[JohnLee]

Good afternoon folks,

I hoping someone can help me, I want to extract specific data from a text file and place it in my table.

The following is an example of the data:

Display Author="All Saints" Title="On & On" Genre="Pop" Color="7693971" Tag="2"

I want to extract the following:

Where the word Author= appears to extract the data between the double quotes, so in the above case I want to extract "All Saints" excluding the double quotes.

And then where Title= appears extract "On & On", again excluding the double quotes.

So I would end up with data in my table looking something like this

strArtist [Author] strSong [Title] strGenre [Genre] strYear [Year]
All Saints On & On Pop
America Venture Highway Rock 1972
Amillionsons Misty Blue Pop 2002

my table is name tblMusicList, the fields are:

strArtist
strSong
strGenre
strYear

Any Assistance would be most appreciated.

Regards

John

 

[plog]

I would make it a comma delimited file and import it using the tools Access has for that file type. First, make a copy of that file.

You have rows of data that look like this:

Display Author="All Saints" Title="On & On" Genre="Pop" Color="7693971" Tag="2"

You need to get to this:

"All Saints", "On & On", "Pop", "7693971", "2"

You should search and replace out all the unnecessary text to get it to the format you need.

1. Search "Display Author=" and Replace with nothing
2. Search " Title=" and Replace with ", "
3. Search " Genre=" and Replace with ", "
4. ...
5 ...

Once your file looks like a comma delimited file you can easily import it.

 

[JohnLee]

Hi,

Thanks for your response, I've been doing what you have suggested for a long time now and I really wanted to be able to do away with that manual process and come up with some coding that would do all the work for me.

Perhaps it's not doable.

Thanks anyway.

Regards

John

 

[plog]

2 suggestions:

Text editor with a macro. Open the file, run the macro and your file is formatted correctly.

Queries. Import the data into a temporary table in Access then use Access string functions (http://www.techonthenet.com/access/functions/) to parse out your data correctly in a query, then use that query. Once that queries built, you just need to load the new data to the temporary table and run the query.

 

[JohnLee]

Hi Plog,

Thanks for your suggestion.

Regards

John