Export BF CSV data into ACCESS

[thunderfoot]

I have Betfairs CSV datafiles in Zipped formats.

I want to load certain information into ACCESS, say, Footballs Correct Score data where the odds are less than 1.50 AND In-Play only.

I have been Unzipping the datafiles exporting these into ACCESS, 7 or 8 weeks at a time (because ACCESS starts to 'creak' when the database starts getting a very large number of records - about 4 million!!!), then running an Append query in ACCESS to create a smaller database with the info I specifically want. Then deleting the records in the '4 Million' database and starting again with the next 7 or 8 weeks.

All this Unzipping, Exporting into ACCESS, appending, deleting takes a lot of time.

Is there any way that a query can be run in ACCESS to Import the info I'm particularly interested in, directly from the CSV files (if it can be done without having to unzip them, even better)? Understand that MySQL or Java might be the way to go, but having no experience of either, don't have the time to take a couple of months to learn 'em!!!

If a query can be written with some flexibility so that other data can be Imported i.e. the query asks you for the info you want e.g. 1-1 scores only on the Correct Score Football markets, brilliant!!

Bit of a Novice at ACCESS, so programing tips gratefully accepted.

[Bid]

You won't be able to link directly to them in the zip but you can link to the csv file itself..

File | Get External Data | Link Tables...

Change the files of type box to Text Files and browse to where you've saved the csv.

You can then use that linked csv file in the same way you would a table.

Be aware though, when working with Access, the database will get bigger and bigger but never smaller by itself when you delete data - you'll need to run Compact and Repair every now and again to ensure it doesn't get too big. If it hits 2gb then it's not always possible to get it back...

[thunderfoot]

Thanks 'Bid',

Will try the linking method, using 3 or 4 weeks first; if that works then it should work on higher numbers.

Was aware of the Compact & Repair requirement every now and then, but not the 2Gb limit ..... that must really be slow; mines only 116 Mb (4 Million + records) and that creaks!!!

[thunderfoot]

Hi 'Bid'

Need some help.

I have 4 test CSV files linked to the Database; Week 1, Week 2, Week 3 & Week 4.

I want to run 1 Append Query on all 4 CSV files at the same time, the results being 'deposited' into a Master Database called 'Football Data". Is this possible or do I need to run the query 1 CSV file at a time?

[Bid]

I've been away so didn't see your post..

You've probably already solved this but...

You can do it at the same time if you want. If for example yout tables are called Week1, Week2 etc Then create a new query and don't add ant tables to it then change the view to SQL. Then type:

SELECT * FROM Week1
UNION ALL
SELECT * FROM Week2
UNION ALL
SELECT * From Week3 etc

Save the query and you can then use this new query as if it was a table.