Reading text file and insert/update to DB

Hi All,

Normally, I am using SSIS to get a file from a text file and then do some lookup before I insert/update to database.

Is there any sample code of how to do this in C# or Vb.NET?

Basically, I would like to read a daily text file from a directory. Check each row by id then compare it with the database table.
If there is already an existed ID, then updated, otherwise insert a new row.

Thanks in advance.

10/8/2008 7:10:40 AM 16182 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 26


Your question really falls under two parts.

1)reading the text file and parsing the values.

reading a text file is fairly simple

Once you've done that assuming each record is on a new line in the text file I'd split it into an array using string.Split("\n\r");

That gives you each row as an object in an array. I'd then create an object with each field and set them based on their position in the object. Depending on the file format this might be another .Split operation. That should give you an object ready to insert/update into the db which I'd then add to an Ilist or some other collection.

2) upserting the db

I'd then iterated over all the objects I had and I'd use one of these methods to insert/update the db



 Colin Robertson
10/8/2008 8:46:03 AM

Similar Artilces:

inserting data to text file from database and inserting data back to database from text file
Hello friends.... I am looking for 2 things(using or and sql svr 2000) 1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space) 2.Insert the data from text file back to database. Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.Gurjeet,England As for detailed code, I won't give you :P however I can tell you one way of doing this (there are multiple ways).The way I'd do it would be simple:Retrieve the information fr...

text/image files
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> &nbsp; <br>Greetings, <p>I'm running SYBASE ASE 11.9, Power Dynamo 3.5 - I want to manage some text files &amp; images. Users are allowed to submit/delete, and the web site serves the text &amp; images up in various places. <p>Is there an advantage to keeping them as files &amp; just keeping the location (path/filename) in the DB? <p>What is the best way of accepting the files and inserting them into the DB or writing to the fileserver - the users ar...

Reading only updated portion of a text file
Hi,I am trying to read a log file that has following content.1234I simply read the file. Now after a few seconds the text file gets updated and looks like123456Now the next time I read the file, I only want to read the newly added portion, which is 56How canI do this ?? My file reading code runs after a speficied period of timeI am doing this right now but that does'nt seem to work. string temp; long newoffset = 0; Byte[] myByteArray = new Byte[200];while (true){FileStream fs = new FileStream(@"C:test.txt",FileMode.Open,FileAccess.Read,FileShare.ReadWrite); fs.Seek(newoffset,SeekOrigin...

Parsing Text File and Inserting Into DB
Hello all, I have a question regarding importing text file data into SQL Server.  I'm hoping someone can point me in the right direction, as my searches haven't turned up anything specific enough. I'm trying to parse a large (24MB) text file.  It's a fixed-width file, with multiple columns.  I need to parse this file, check if a record already exists, and then import the data into the database.  But I don't need to insert every column.  There's only a few columns from the file I need to insert.  This parsing also needs to occur at regul...

Can Pipeline Objects INSERT into Text Files
I am attempting to use a Pipeline object to output to a text file from SYB SQL Server v4.x. Everything has been setup using ODBC and the PB Intersolve 2.11 driver. The text file database has been created with individual text files as required. When I attempt to run my pipe, I get this error: "Destination database is read only, Updates are not allowed." The documentation (pbdrv050.hlp) for the driver (PBTXT07.DLL), states that: "The Text driver executes SQL statements directly on the text files. The driver supports Insert statements, and inserts the record at the...

Read .csv file and insert in oracle DB
 Hiiii have to read .csv file and insert data into database.I have read the file but my problem is my data is in format like :-Tickername,EPS1,EPS1Year,EPS2,EPS2 Year,EPS3,EPS3 Year,BV1,BV1Year,BV2,BV2 Year,BV3,BV3 Year,CashFlow1,Cashflow this way where year with eachfield specify that value in particular year.Now data has to be inserted in Database Yearwise . Fields in Database :-Ticker, Year,EPS,BV,CashFlow.Example :-for ticker IBM in file -IBM,10.2,200912,11,201012,9.98,2008,12,200812,10,200712,12.02,200612,7, would be in database  IBM ,2010 ,11,to be cal...

trying to insert data to the database by reading it from a text file?
Hello everyone, I have a text file that have on each line a record and I already did the reading from this file and assigned it to a value this is a part of my code : so now i'm trying to insert these values to the database ...please any hint or example of code to help through this   private void AssignValues(string PrtRrd) { if (PrtRrd.Length != 400) { Initialize(); } else { _TESTFILL = PrtRrd.Substring(0, 6); _Zip05 = PrtRrd.Substring(6, 5); _Zip04 = PrtRrd.Substring(11...

Update XML file when DB Update
I want to make a XML file witch update when database update. The DB is SQL Server 2005 Express. How can I do it? Thank's...Dornellas.Net Depends on how you want your application to behave....If you are updating the database through your code, then simply update your xml afterwards.... If you want triggering types of stuff to automatically, mabye someone else can chip in on it's a bit harder for me to explain. Liming XuJumptree ASP.NET 2.0 Project Management - With Source Code, Free to Non Profit Org and Effectively Manage Projects/Tasks/Milestones...

How do I read a specified line from a text file (using VB.Net)?
In a VB.Net code loop in my ASP.NET 3.5 app I want to select a specific row from a text file and save it in a database record. I would be incrementing the row number with each successive pass through the loop. My searches to find suitable code have failed. Can anyone please help me out on the VB code required to do this? If it makes the coding any easier, I could save the text file in a variety of different formats (probably via Save As in Excel). Any assistance would be greatly appreciated. David The following will read a text file line by line:- Public Sub Test() Dim sBuffer As Stri...

Read text file into text field
Hi,How may I read the contents of a text file into a text field?The text file is located at server-side and the text field is in a web page (i.e., client side).The reason is, suppose the text file contains a list of 10 entities, the user may be able to edit the list in the text field and not type everything out himself. (e.g. for searching).Thanks for reading! You can read your Text File into a String or StringBuilder object server side, and use this String for theText Property of your TextBox:    Dim file As New System.IO.StreamReader("c:\test.txt")  &nb...

multiple update panel and object datasource insert parameters problem
Hi Guys Newbie using vs2--5, .net2.0 and ajax with SQL express Ok have a page, the page works fine, accept i added some ajax update panels onto it, this normally isnt a problem but i am also using a freetext box control and when some of my code and validation was firing it was causing an issue if it my controls were all held inside an update panel so i had to create several.  My validation now works fine but when i am trying to use my objectdatasource to add my insert parameters it cannot see the controls as they are now sat in several different update panels, this normally isn...

Cannot insert duplicate key row in object @ DB Update command
I am trying to perform an update on a table in my database, updating columns that are not primary keys or the like. However, when i commit the update I am getting the error message: Cannot insert duplicate key row in object 'customer' with unique index 'idx_customer_1'.   What is this error referring to? All I am trying to do is a simple update (which was working but seems to now be broken!), and dont understand the meaning/cause of this error.    Thanks seems you have created custered index for your table, and which you won't let you insert d...

Problem reading excel file in .net to update an access database..
here is the scoop.. have a form that user uploads an excel file (very basic data.. 9 columns, multiple rows)after uploaded.. I want to read that data into an access databaseEasy enough, and its working.. however .NET doesn't seem to know when to quit reading the excel file and it always blows up. Here is my sample code. How can I tell .NET or Excel when to quit reading ?Dim myConnectionString As String Dim myConnection As Data.OleDb.OleDbConnection Dim myCommand As Data.OleDb.OleDbCommand Dim myDataReader As Data.OleDb.OleDbDataReader ' Set our connection string. Data file is "...

Help in Using ADO.NET to read from text files(CSV delimited)
 Help in Using ADO.NET to read from text files(CSV delimited)thax Hi kamalsran, Based on my understanding, you just follow the steps when operating with a .CSV file: 1. You can read the new row into a string variable, every row ends with '\n' 2. Every row is a string and you can split the value with ',' 3. You can simply loop the step1 and step2   Hope this helps!David QianMicrosoft Online Community SupportPlease remember to mark the replies as answers if they help and unmark them if they provide no help...

Web resources about - Reading text file and insert/update to DB -

Economy of Reading, Berkshire - Wikipedia, the free encyclopedia
Whilst located close enough to London to be sometimes regarded as part of the London commuter belt , Reading is a net inward destination for ...

Reading Recovery program used in 960 NSW public schools does not work
... not work. A key $55 million-a-year program to teach struggling NSW students to read does not work, with the state's first major review of Reading ...

10 of the best books from 2015 to add to your summer reading pile
It's been a good year for reading. Here are 10 of the books that have tickled Jason Steger's fancy.

Bad Lip Reading hilariously skewers the original ‘Star Wars’ trilogy
... wait, but there's plenty of Star Wars content on the Internet to tide you over. In fact, on Wednesday, the incredibly popular Bad Lip Reading ...

Weekend Reading: Brad DeLong (2013): Noise Trading, Bubbles, and Excess Volatility in the Aggregate Stock ...
Noah Smith has a nice post this morning [i.e., back in December 2013]: **Noah Smith:** Risk premia or behavioral craziness?: >John Cochrane is ...

Recommended Reading: The harsh realities of internet fame
Recommended Reading highlights the best long-form writing on technology and more in print and on the web. Some weeks, you'll also find short ...

The 'Home Alone' Kid Is All Grown-Up And Crazy, And 'Star Wars' Gets A Bad Lip Reading
Macaulay Culkin reprises his role as Kevin McCallister from 'Home Alone' and the Bad Lip Reading crew take on 'Star Wars.'

Finally: Star Wars Gets The Bad Lip Reading Treatment
"Stay nasty." Just in time for the new movie's release, these are three bad lip reading videos from the original Star Wars trilogy. They're ...

These New Minimalist Stephen King Book Covers Will Remind You Why Reading is Scary
If you were already freaked out by Stephen King’s horrible psyche before, then this could make things a tad worse. Scribner is releasing a ton ...

Reading Smart Cards from a PLC
If you’ve spent any time on a factory or plant floor, it is a good bet you’ve run into PLCs (Programmable Logic Controllers). These are rugged ...

Resources last updated: 12/26/2015 11:50:17 PM