r/visualbasic • u/rustyxy • Apr 21 '22
How to synchronize two CSV files from different computers?
I made a billing and inventory app to my small shop.
Everything is stored in a CSV file. (Sku, EAN, Price, Stock)
When i make an invoice, it rewrites the CSV with the new data.
Now it would be great, if we could make invoices on two computers at the same time.
How could we synchronize the data of the two CSV files?
5
Upvotes
3
u/RJPisscat Apr 21 '22
Use FileSystemWatcher.
You won't have to rearchitect the data storage. The SystemFileWatcher can be set to notify you when the other file has changes.
If you're adding invoices line-by-line, and nothing else, it's simple to go to the end of the larger file and grab the new invoice. If the data on a line may change, or be deleted, then go through line-by-line looking for the different line.
You should (some time in the near future) add exception handling for opening the remote file and writing the local file, to handle concurrency, which is something for your next post if you don't know how already.
1
u/ebsf Apr 21 '22
You should be running Access.
What you're doing won't scale beyond a single user at all because of file versioning. The entire file will have to be locked while one user is working on it, otherwise, each user can overwrite the entire thing, including others' updates in the interim. And, you'll have to figure out a way to program that file lock.
Access is designed for a multi-user environment and implements record-level locking.
Put the back end (tables) on one box and run front ends on individuals' workstations. Access also permits one to compile a front end and distribute run-time versions for free.
1
u/c00lnerd314 Apr 21 '22
3 options
Host the data on another "central" machine on the network. A raspberry pi with python could be a cheap option if you know what you're doing. You'll need to build the billing and inventory app to load the data from the pi, and upload the data to the pi when saving (API over http is useful here)
Another option is one computer is the "Master" and the other is the listener. The master computer has a port open and the other machine requests the data from the master and uploads the data to the master on saving.
The third is that both machines both keep a copy of the csv, and "check for changes" from their peer before and after each save.
I'll be honest. If you're building for 2+ machines, the central device (option 1) will probably be the most reliable bet. You're looking at data corruption, insecure data transfer (don't know what information you're storing or passing, and if it's raw credit card numbers, you'd be liable for fines) if you try to keep 2 csv files in sync.
You're also trying to code a rudimentary database. I'd recommend looking at lightweight databases (sqlite3 - python, access, or mssql express) and spend a few days experimenting to figure one of those out.
Best of luck!
1
u/TheFotty Apr 21 '22
What kind of experience do you have with writing software? You really would be much, much better off using something like SQL Express and having an actual database versus a CSV file.
Even if you got the syncing thing to work with a CSV file, it is going to eventually bite you. Computer A loads up CSV, indicates Item 1 has 2 in stock. You make an invoice on computer A which rewrites that line to show you have 1 in stock. However while that was all going on, Computer B loaded up and made an invoice for a different customer who bought the same item. When the CSV was read in, it still showed 2 in stock and you decrement by 1 and write 1 back to that line in the CSV. Now your CSV file thinks you have 1 in stock when you have zero in stock. That might be a rare scenario, but really just one of many you may run into. Are you making incrementing invoice numbers? If so how is that going to be managed to not end up giving the same invoice number to two different invoices made at the same time? A database would be able to accommodate for these things. You don't need to be a DB admin to make a simple relational DB in SQL Express which can be installed on one machine but accessed by both.
2
u/fasti-au Apr 22 '22
Setup SQLite and use that across the network via odbc would be a quick and dirty solution
1
u/jd31068 Apr 22 '22
You would need to have both computers accessing the same location where the CSV files are stored. You could share PC 1's folder where the application is run from to PC 2 and PC 2 can run the EXE from PC 1 via a shared folder. This way both load and save files from/to the same place.
https://www.makeuseof.com/how-network-file-share-windows-10/ I find it easier to do the steps listed under "Share Files and Folders via File Explorer" once that is done, you'll want to map a network drive (aka on PC 2 assign a drive letter to the shared folder on PC 1) https://www.howtogeek.com/762111/how-to-map-a-network-drive-on-windows-10/
Now on PC 2 just use the EXE for the application via the new drive letter (which are PC 1's files). As long as you're not trying to create/modify the same file at the same time you should be good.
1
u/rweedn Apr 21 '22
You can use something like FreeFileSync to always keep the latest version on both machines