SQL Server :: Reading Data From .CSV File Into Sql Database
Jul 15, 2010I urgently need to find a way of reading data from a .CSV or .xls(Excel) file into an sql database so that I can use it in my asp.net 2.0 application.
View 4 Replies
Similar Messages:
Reading A .csv File And Outputting Data To Database?
Jan 25, 2011I have a csv file with 5 columns. I need to read this file using c# and have to write the contents to the table in database which has 5 columns to it.
if anyone know the code or steps to do this.
Reading - Parsing And Posting Data From A .csv File Into A SQL Database
Jul 13, 2010I 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
SQL Server :: Reading And Checking .CSV File And Inserting Data To SQL Server
Sep 30, 2010I have got a page in which there is a file upload option where I have to upload/import the csv file. What I want to do is to check if the correct format of file is uploaded for instance if any other than csv file is uploaded, the system should give an error message. Also what I need to do is to check certain fields of the csv file for instance there are some mandatory fields in the csv file which should be there like name , postcode, How can I check that these fields are not empty . After performing these task, the system should automatically upload the csv file onto the sql sever 2008.
View 3 RepliesSQL Server :: Reading Xml File And Inserting Data Into A Tsql Table (no Duplicates)?
Feb 25, 2011I am writing code to insert values from an xml file. I check for a duplicate before inserting and delete if there is a match. Is there a more effecient way to prevent duplicates before insert a record? my code: private void LoadFData() { SqlConnection oConn = new SqlConnection(ConnectionString); SqlCommand oCommand = new SqlCommand(); oCommand.Connection = oConn; oCommand.CommandType = CommandType.Text; oConn.Open(); // Load committee master files string[] strFiles = System.IO.Directory.GetFiles("fff", "ffff.dta", System.IO.SearchOption.AllDirectories); for (int i=0; i
View 2 RepliesDatabases :: Upload Ms Word File And Store It Into Database Then Reading The File?
Jan 12, 2011i like to create a web application,in that i need to get the resumes from the user,then i need to publish that resume to the manager in date wise..
View 5 RepliesWeb Forms :: Reading Data From Excel File And Inserting Into Sql Server - Initialize HasFile?
May 24, 2010I am using reading data from excel file and inserting into sql server using following article, it seems to be working correctly except when I do page refresh normally, it again execute the the btnUpload_click. I want that once the record imported and if user refresh the page again it should ask for enter file name again. let me know how do I resolve this? [URL]
View 8 RepliesReading File And Store In Database?
Mar 9, 2010I m reading a text file. After reading all the lines, I want to insert those records into respective fields in database.
So how to store the 1 or 2 or 3 or 4 or 5 lines of records until I read the last record.?
[code]....
first 9 number is emp id, next is name and next is salary.
While I m reading, I can store only last record. How to store all the records in a array to store in the database after reading all the lines.
Web Forms :: Reading And Posting A .csv File Into A SQL Database
Jul 13, 2010I 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:
[Code]....
Web Forms :: Create Text File While Reading From Database?
Oct 1, 2010How to create Text file while reading from database?
DataBase Table has 10 records in it
ID Name Age
1 Jon 18
2 Martin 20
..
..
10 Andy 30
I want create a text file like this
ID: 1
Name: Jon
Age: 18
-----------------
ID: 2
Name: Martin
Age: 20
-----------------
ID: 10
Name: Andy
Age: 30
SQL Server :: Reading Text File Using Sql Server 2000 - 2008
Sep 22, 2010how to read the data's in the text file using sql server 2000 and 2008. i have a text file in a server and it contains one column name with the multiple column values i need to read that informations and i need to update in a table in the database.
View 2 RepliesVS 2010 - Reading A File From Server
Nov 22, 2013I am trying to read a text file from the root of the server. This is what I tried
Code:
Dim sr As StreamReader
Try
sr = File.OpenText("~ est.txt")
Label1.Text = sr.ReadToEnd
Catch ex As Exception
Label1.Text = ex.Message
End Try
If I test this locally, I get the error: "Could not find a part of the path 'C:Program Files (x86)Common FilesMicrosoft SharedDevServer10.est.txt'."If I upload it and run the code on the server, nothing happens (Lable1.text does not change).
Configuration :: Reading Problem In XLS File In 64 Bit Server?
Sep 9, 2010Reading XLS file in 64 bit server , following errror "microsoft.jet.oledb.4.0 not registered on local Is any solution for this problem?
View 1 RepliesReading A File Into Memory And Then Reading It One Line At A Time?
Mar 7, 2011I 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?
View 8 RepliesDataSource Controls :: Reading From SQL Server And Saving As Physical File
Mar 26, 2010I have to create PDF Files reading data from SQL Server where the data is in Binary format as Image data type.
View 2 RepliesSQL Server :: Reading File Csv And Inserting Into SqlCE Performance Improvement?
Dec 15, 2010NOTE: Code and names simplified for post. Elimination of try catch and other best practices also limited for post.
I have a simple SqlCE database one table consisting of 4 columns (specialvalue, name, state)
I am using a streamreader from a file.txt that contains 3000 lines of csv data, looping the below.
s[] = {id,name,specialvalue,state) //(SAMPLE DATA-displaying names)
while(!sr.EndOfStream)
{
[Code]....
This code works as expected but I would like to increase the performance since it takes over 30 sec to complete. I will be moving to a stored procedure later but would like to find out how to optimize the current code. This is more for experience than anything. It is a project that I am working on for codeplex.
Reading An Embedded File From A Custom Server Control An Rendering It?
Mar 31, 2010I currently have a file "abc.htm" in my Custom Server Control Project and it's Build Action is set to Embedded Resource.Now in the RenderContents(HtmlTextWriter output) method, I need to read that file and render it on the website.I am trying the following but it's to no avail:
protected override void RenderContents(HtmlTextWriter output)
{
var providersURL = Page.ClientScript.GetWebResourceUrl(typeof (OpenIDSel), "OpenIDSelector.Providers.htm");
var fi = new FileInfo(providersURL); // <- exception here
//the remaining code is to possibly render the file
}
This is an example of how the providersURL is:
/WebResource.axd?d=kyU2OiYu6lwshLH4pRUCUmG-pzI4xDC1ii9u032IPWwUzMsFzFHzL3veInwslz8Y0&t=634056587753507131
FileInfo is throwing System.ArgumentException: Illegal characters in path.
WCF / ASMX :: Reading Data From Database Using Web Service
Apr 25, 2010I have a requirement in which i have a database table which has 4 columns Id,Name,position,city.....now i have a form in which the user will be filling in the id and then the other three columns should auto populate ....now i want a web service which will make a data connection and get the Id from the form and then query the database and get the other three columns..i am pretty new to web services ..can somebody give me some heads up on how to achieve this...let me know if i have to provide more details.
View 8 RepliesDatabases :: Reading Excel Data To SQL Database
Sep 23, 2010Using C# to develop an application.I need to upload data ( which is in excel) to a sql table.
[Code]....
Textfiles - Reading Data From Flat File
Jan 27, 2011i have flat files in some direcotry. each flat file contains around 60,000 rows of data. earlier we were using excel sheets to upload data. but now the challenge is it is taking more time to upload data into DB tables. which is the best way to upload such a huge data into db tables. can we use SSIS for uploading data. if so, how can we achieve that.
View 1 RepliesSQL Server :: Add Data To Database From CSV File?
Nov 12, 2010i have an csv file, using Logparser im reading the data and add into the table, now if the csv file has 100 records then im hitting the DB 100 times, how can i push all the records to the DB at one time so, that i can improve the performance.
View 9 RepliesWeb Forms :: Automatic File Reading / Check For New File Everyday And If The New File Is In The Folder Read It
Sep 20, 2010I 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.
View 2 RepliesDatabases :: Reading CSV File From The Fileupload And Then Retrieve The Data?
Mar 4, 20101) I need to make reading CSV file from the fileupload and then retrieve the data.
2) I need to load CSV and the 3 columns rise two functions.
Load CSV data and then stored in a database, but I need to save storage space vary according to 3 column.
CSV Example:
1 column - Cat, 2 column - Black, 3 column - 1
1 column - Dog, 2 column - Brown, 3 column - 2
1 column - Cat, 2 column - Black, 3 column - 1
1 column - Dog, 2 column - Brown, 3 column - 2
Now I need to load the CSV in C # and column 3 to distinguish what the operation is carried out.When the load line in column 3 contain the value 1, so will save the table 'Cats' when the value 2 and is saved in the table "Dogs".(Note: Data are separated ";")
Reading Data From .XSLT File Populate Dropdown?
Feb 8, 2010I would like to read some data from a .XSLT file to populate my dropdown, the outline is as follows
file name StaffDirectory.xslt
<xsl:stylesheet version="1.0" extension-element-prefixes="msxsl" exclude-result-prefixes="msxsl js dl" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:js="urn:custom-javascript" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:dl="urn:datalist">
[Code].....
So id like to retrieve all values for ="IDAKRS0J" and populate a dropdown with this, is this possible ?
C# - Can Use HashTable For Temporary Data Storage After Reading Data From Database
Feb 5, 2010I am looking for opinions and best coding practices.I need to get info from a database with a query such as SELECT this, that FROM MyDB (returning 2 fields). Would it be wrong to use a hashtable for temporary storage?Is there a better way to accomplish the same thing? What about if I am returning more than 2 fields, would it be best to step up to a DataSet or something?
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
GetData();
[code]...