Web Forms :: Reading And Posting A .csv File Into A SQL Database
Jul 13, 2010
I have the following data that basically I only need a few bits of information from: The only things I need from this are:
Patients Name
Drs Name
Patients Phone Number
Appt Time
Appt Date
and the rest of the information I can discard. A customer uploads this as a .csv file (even though it really isn't as you can see) and I'd like to parse the needed information and post that to my SQL database and discard the rest. I think I can do this with a dataset but I've never built that before. The fields from the customer will always be the same and the fields I will need will always be the same. Also, the date time has to be in the format of yyyy/mm/dd:hhmm and the phone number always has to have 512 as a prefix. Here is the code I currently have for my site:
I have the following data that basically I only need a few bits of information from:
Resource:X - Y;Z - Å;Type:(all) From Date: 07/12/2010 - To Date 07/12/2010 Sort by:Time Include Referring source/physician:No Footer:Default Criteria:None ","Appointments","X, Y","ZAssociates","Monday, July 12, 2010","Time","Patient Name","Patient ID","Appt. Type","Ref. Source/ Physician","Phone","Type","DOB ","Z, X","Y","7/12/2010 12:00:00AM","Time","Patient Name","Patient ID","Appt. Type","Phone","Type","DOB "," 7:30 [snip]
The only things I need from this are:
Patients Name Drs Name Patients Phone Number Appt Time Appt Date
and the rest of the information I can discard. A customer uploads this as a .csv file (even though it really isn't as you can see) and I'd like to parse the needed information and post that to my SQL database and discard the rest. I think I can do this with a dataset but I've never built that before. The fields from the customer will always be the same and the fields I will need will always be the same. Also, the date time has to be in the format of yyyy/mm/dd:hhmm and the phone number always has to have 512 as a prefix. Here is the code I currently have for my site:
Imports System.IO Imports System.Data Imports System.Data.SqlClient Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim SaveLocation = "\xxxWEB3wwwrootWebfile1RemindersDoug_Ancildoug.csv" If UploadFile(SaveLocation) Then 'the file was uploaded: now try saving it to the database SaveToDatabase(SaveLocation) End If End Sub Private Function UploadFile(ByVal SavePath As String) As Boolean Dim fileWasUploaded As Boolean = False 'indicates whether or not the file was uploaded 'Checking if the file upload control contains a file If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then Try 'checking if it was .txt file BEFORE UPLOADING IT! 'You used to upload it first...but the file could be a virus If File1.FileName = ("doug.csv") = False Then 'The file is not the expected type...do not upload it 'just post the validation message message.Text = "Sorry, thats not the correct file." message2.Text = "Please locate and upload 'doug.csv'" Else 'The file is a .txt file 'checking to see if the file exists already 'If it does exist Deleting the existing one so that the new one can be created If IO.File.Exists(SavePath) Then IO.File.Delete(SavePath) End If 'Now upload the file (save it to your server) File1.PostedFile.SaveAs(SavePath) 'After saving it check to see if it exists If File.Exists(SavePath) Then 'Upload was sucessful message.Text = "Thank you for your submission." fileWasUploaded = True Else 'the file was not saved message.Text = "Unable to save the file." End If End If Catch Exc As Exception 'We encountered a problem message.Text = "Your file was not in the correct format. Please contact Customer Service at xxxx-xxxx-xxxx." End Try Else 'No file was selected for uploading message.Text = "Please select a file to upload." End If Return fileWasUploaded End Function Private Sub SaveToDatabase(ByVal SavePath As String) Try Dim sqlQueryText As String = _ "BULK INSERT dialerresults " + _ "FROM '" & SavePath & "' " + _ "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = ' ' )" ' and bulk import the data: 'If ConfigurationManager.ConnectionStrings("Dialerresults") IsNot Nothing Then 'Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString Dim connection As String = "data source=10.2.1.40;initial catalog=IVRDialer;uid=xxxx;password=xxxxx;" Using con As New SqlConnection(connection) con.Open() ' execute the bulk import Using cmd As New SqlCommand(sqlQueryText, con) cmd.ExecuteNonQuery() End Using End Using 'Else 'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!" 'End If Catch ex As Exception message.Text = "Your file was not in the correct format. Please contact Customer Service at xxxxxxx." End Try End Sub End Class
I know this is probably a pretty easy thing to do and it is if I can upload the file and store it onto the hard drive of the server. What I need to do is read the text file into memory and then parse through it one line at a time. Anyone have any code that demonstrates that?
I would like ask you for some ideas how can I write mechanism that will be automatically read files from folder. I don't know how to schedule the write task. I want to check for new file everyday and if the new file is in the folder read it.
I have deveopmed i web site in ASP.NET and C# using Visual Studio 2008. I have a problem with performing button events twice. A user has a web page open. He click on a button (Save) and data is added to the database. He then presses F5 to update the page, and the button event is fired again adding more data to the database that should no be added. My question is how can I avoid this scenario?
but when I tried to add the service reference to the silverlight app, the Add Service Reference window would show me that there was a service file, but the node wouldn't expand to show the rest of the files that contain the services and what not. I got this error instead:
"An error occurred during the processing of a configuration file required to service this request. review the specific error details below and modify your configuration file appropriately."
"Parser Error Message:There is no service behavior named 'AdventureWorks_WebServer.Service1Behavior'."
"Metadata contains a reference that cannot be resolved: 'http://localhost:55579/Service1.svc'.
The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.
If the service is defined in the current solution, try building the solution and adding the service reference again."
I need to read a rtf file and then display the same in div tag. and a normal html text box after reading the content i get to see rtf formating special characters Is there any way that i can remove these special formating characters?
I want to read from database without using templated control like gridview, listview ...etc I am being able to read from db, but shows only the first row;
In my application, i have one fileupload control which will take excel file. I want to read this excel file and want to store in datatable. How will i do this. I am using visual studio 2008.
we are working on some utilities to provide the webmaster, one of which needs to search the properties of the webparts on a page for certain string values. So I want to desterilize the blob into the object graph and iterate through the properties.
I am using more than one files to read and write to the server. Suppose during writing or reading any of files gets error the already stored files need to be rollback or removed from storing.So that the user agin try to write without error. Simply the all of the files need to be stored into the server without error, of error occurs to any of file none of files need to be read/write
im currently trying to create a blog using c# and xml. ive created the form and code that allows me to write the data inputted to an xml form and now i want to display it in another page. can anyone tell me what to do?
I have web form, wich has to post its data to different server, if data i post is valid, target server creates xml file. How can i read the created file from my original site?
Only way i can think off so far is to create iframe in wich postback is made, and somehow after postback page wich contains iframe would read the xml..
In my application i want to share one .txt for read and write among no. of users. in Button click i m writing data in that file and in tick event of Timer i m reading that file, In tick event i m reading that file based on file based cachin means if file have been changed then only i m reading that file but when i have put m application i IIS then i m not able to get updated file.
But some times the file that is read, reports that there is more rows than there actually are. i.e. I only have 300 lines of data but the read reads in 500. My question is, and I am sure I have seen this somewhere but I cannot find it now, is how do I get the reader to stop reading at the first blank line?
I have a third party CMS system that can create forms but can't manage true File upload capabities. I am able to get users brows for thier file and have the value submited to my custom .net code behind page for processing. So I end up with like "C:somefilefoldersomefile.doc".
But on my processing page I want to read this file (like the file uploader) and save it to a location. I tried using the file uploader control (but have it not visible) but realized I can't preset it as I was planning to set it and do the normal processing from there. Is there another way to do this?
