Read Data From Text File and Insert To databse

Question

1.00/5 (1 vote)

See more:

I want to read data from a text file line by line and insert the data in each line in database.

What I'm thinking of now read line by line and at each line insert to database. What I'm asking here is there a better idea to do so? like what can we do to insert data at once instead of looping line by line and insert the record? I have around 500+ lines in the text file that I need to store each day, so performance is my issue here.

Posted 5-Dec-13 19:57pm

Mohamad M. Mohamad

Updated 5-Dec-13 20:08pm

v2

Add a Solution

Comments

JoCodes 6-Dec-13 2:03am

Whats the code you tried?

[no name] 6-Dec-13 2:09am

I didn't try yet, what I'm asking here is this a good idea? or is there a better idea to do so. my conceren is the performance since I have 500 lines in text file and I'm worry reading and inserting line by line may be slow...

JoCodes 6-Dec-13 2:14am

Not seem to be a good idea. Each time should hit the DB for insert line by line...

JoCodes 6-Dec-13 2:18am

Should consider reading the text file at once instead of line by line.

[no name] 6-Dec-13 2:29am

ok if I read at once how to insert them in database? should I add the data are delimiter by comma so I need to split them and insert them on specific columns.

JoCodes 6-Dec-13 2:34am

Using delimiter is a good option. You can split and store in any datastructure like datatable and perform bulk insert .

[no name] 6-Dec-13 2:43am

Any sample code you can show me?

JoCodes 6-Dec-13 2:52am

http://www.dotnetspider.com/forum/322945-How-read-textfile-insert-into-SQL-table-C.aspx

JoCodes 6-Dec-13 2:54am

For bulk insert try this
http://www.dotnetcurry.com/showarticle.aspx?ID=323

JoCodes 6-Dec-13 2:12am

Need to insert each line as a row in the DB right?

[no name] 6-Dec-13 2:23am

Yes correct each line of text file will be added as a row. Any suggestions?

JoCodes 6-Dec-13 2:30am

Some hints : fetch all the lines at once using File.ReadAllLines(path); and can be stored in string array. Then use bulk insert to avoid the repeated calls to the DB. SqlBulkCopy class can be utilized for the same.

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)

CPallini · Answer 1 · 2013-12-05T21:02:00

It depends on your exact requirements.
If you need to store one line into one record (for instance, if required by later processing) then you have to insert line by line the text file (and probably it doesn't matter if you read the whole file once or not).
On the other hand if you are allowed to store the whole text file content into a database record then read the whole fine at once and just insert it into the database record.