Importing CSV data to a Dataset, then exporting to a SQL Server

Question

0.00/5 (No votes)

See more:

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?

Posted 26-Oct-10 18:34pm

Roger Wright

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2010-10-26T19:08:00

Solution 1

Yeah, Roger , there is one smarter way to do this.
did you know about SSIS (Microsoft Business Intelligence Studio) ?
IF you have a set up of SSIS ? Please let me know the version .
I will give you whole step to do these task .

Posted 26-Oct-10 19:08pm

RDBurmon

Comments

Roger Wright 27-Oct-10 1:26am

Thank you, Rahul, but while I might be able to add SSIS at home, we don't have it at work and probably won't be installing it. I'm the one handling this processing right now, but as soon as I can simplify the process I plan to pass it on to someone else to do, and that requires that I stick to commonly available tools that we all share. The way things work at my company, I get all the crap work that no one can figure out, I simplify it, then I get to pass it on to someone else to do, so long as it costs nothing.