Database in .txt file

[colddeck84]

I was thinking to make my own Soccer Database for now I´m only interested in storing FT result So that I can later look up teams for past performance later, So far this is what I got:

The DataBase is stored in sheets Soccer,

Please Login or Register  to view this content.

This code works just as I wanted, However im a bit worried about adding duplicates to the database, so that is the reason why I came here, Was thinking that nothing should be added to the database if the same Date is already in it from before, How would I do That ?

Any help is much appreciated as alway

Ive attached the txt file and sample if anyone is interested.

Frederik

[AlphaFrog]

Please Login or Register  to view this content.

[colddeck84]

Hi thanks for your reply I had come up with quite similar solution myself late last night myself.

Please Login or Register  to view this content.

But my code did not exit the sub probably even though the if condition for the date was met . Thanks for your input I think this will be quite a big task, and I hope it will be easy to retrieve all of this data later .

[colddeck84]

Anyway looks like your edit works perfectly, couple of questions if you don't mind?

Would it be possible to instead of search if date is in the .txt file , rather search each match in the sheet("soccer") and check if it is in the .txt file. and if not add matches that are not already stored. As I'm not sure how I should update this, Current setup would need to be updated after all matches for the the day is over , so basically on day behind.

Also how fast will this be when the rows of the txt file exceeds 10 000 rows ?

frederik

[AlphaFrog]

I may be more efficient to have he macro mark the matches as logged in column N (or any empty column) on the sheet each day. Then the next day log only unmarked entries.

The code below reads all the data en mas to an array then loops through the array. This is much faster than looping through each entry on the sheet. It's very expensive to access the sheet. Testing the values row by row accesses the sheet for each row. By using an array, the sheet is accessed only twice; once to read all the data and once to write the data.

The first time you run this code, all the FT entries will be written to the text file because they are not marked as Logged in column N.

Please Login or Register  to view this content.

[colddeck84]

Hi thanks for your time looking into this.

I may be more efficient to have he macro mark the matches as logged in column N (or any empty column) on the sheet each day. Then the next day log only unmarked entries.

- its definitely easier to store it in a worksheet and I like how you solved this,
but in all honestly I would really like to not have this Data stored in the worksheet.
kinda beats the purpose of the .txt file

The code below reads all the data en mas to an array then loops through the array.
This is much faster than looping through each entry on the sheet

- Yes you are right, I just made that quick code late last night much better approach adding to an array ,
but with my skills I would need much long time to write the code that way

-I was actually thinking regarding having it stored in worksheet, Maybe it could first load the .txt file
into an array and then check if its logged ?

edit: I can sea that your idea makes this process much simpler

[Kyle123]

Why not just use a database? this is what constraints are for and it feels like you're reinventing the wheel

[AlphaFrog]

-I was actually thinking regarding having it stored in worksheet, Maybe it could first load the .txt file
into an array and then check if its logged ?

The text file data is not in a format that makes it easily loaded back into excel.

Instead of checking if data is already written to the text file, it would be easier and more efficient to process all the data, both old and new, and completely overwrite the text file instead of just appending the new data to it..

[colddeck84]

Why not just use a database? this is what constraints are for and it feels like you're reinventing the wheel

Sounds like a good plan, obviously Im no expert with this stuff where would I get an database ?
,

[AliGW]

Do you have Microsoft Access in your Office suite?

[colddeck84]

Yes I have it

[AliGW]

Great. Access is database software. It comes with lots of templates, so you should be able to find one that suits your needs. It will take a bit of learning, though.

[colddeck84]

This will be a lot of information to address today. I just figured out how to write all of this data as a proper .CSV file makes the data easier to handle , something like this:

Please Login or Register  to view this content.

[AlphaFrog]

If the whole point of the text file is to backup your data, why not just save a copy of the workbook. Or is there a reason to save it as a text file.

Also, you could autofilter column E for "FT" and use SaveAs xlCSV

Please Login or Register  to view this content.

[colddeck84]

thanks alot for all the answers and even on new years eve I think im very close to a solution that might work for me now. Here is my latest edit which will organize all the data niceley into a .csv file
AlphaFrog : sorry but I wouldn't know how to make an array of this latest edit.

Please Login or Register  to view this content.

Also, you could autofilter column E for "FT" and use SaveAs xlCSV

This might be a good option, I didnt know about that.

If the whole point of the text file is to backup your data, why not just save a copy of the workbook. Or is there a reason to save it as a text file.

- At a later point I think im gonna take my current project out of excel completely. For now im currently in need of a database that the rest of my project it dependent upon. and I try as best as I can to not have to many "dummy" sheets

Here is how its working in my end:
Step 1. Load Data. All results up to NOW are loaded for current date.
Step 2. would be to load all of this into .csv file
step 3 would be to avoid duplicates
step 4 clear data in excel sheet or keep as it is.

I was thinking to load all of the csv. data into an array but not if the date is same as in COL D. then it should add data from excel into same array, then overwrite everything in the .csv file.

I think that could be doable ?

[colddeck84]

removed post.

[colddeck84]

I finally have something to work with:

Please Login or Register  to view this content.

* This code will add everything from csv file into an array as long as the date is not presented in cell(12,"D")
forget about the database2.csv (its just for testing)

* Then I can add to the arr from the sheet without no worry about duplicated dates

I kinda wished I had this stored as an multidimensional array instead of a 1d. Would be easier to work with the data that way but making this little snippet of a code have taken me several hours.

Thanks alot guys for all your inputs.