Before I waste too much time on this, I'd like to know whether it's possible, or desirable to attempt what I think I need to do. Advice on a better way is most welcome.
I have 1 to N .csv files - the number depends on user selections - that I need to get from a folder. My understanding is that the Jet4.0 provider will do this nicely, and differentiate between text and numeric fields. Since there may be duplicate records in a batch (damn those stupid users), I plan to use Jet4.0 to import the data into a Dataset, then use a query to remove the duplicates. Not sure how to do that exactly, but I'll figure it out. Then I'll sort the data based on a two-field primary key consisting of the date and meter identifier (these are power meter readings emailed to us daily).
Once the information is suitably massaged, I then want to move it to a SQL Server instance on the office server using the SQL Server provider. Is that even possible? Can I use one provider to populate and manipulate the dataset, then use another to move it elsewhere? I haven't seen any examples anywhere of this being done, so I have no clue, and I'd hate to waste a week trying to do what can't be done.
Is there a smarter way to approach this problem?