I would like to read some data from excel file, create txt file and write into that txt with a format like below; sample txt file
...
D00101001name1 20110125000000000007474
D00101005name2 20110125000000000127026
D00101007name3 20110125000000000000759
...
in my application i have to read from an excel files and check the value of every cell i'm using an OleDb connection to open it and reading but i have this issue if some of the cells in the excel sheet is having this warning when i try to read it returns DBNull ??could any one give me a clue ....knowing that i can not force the user for not having this warning ...i have to solve this within my code .
View 5 RepliesI need to read an Excel work book with 2 sheet which have more than 60,000 records. The application is an ASP.Net application so the performance matters.
Which approach should I take? Sohuld I do it using Microsoft.Office.Interop.Excel or should I do it using OLEDB in ADO.Net?
I have a record created date stored as" 2010-05-16 15:44:18.700" in sql server database. When I read this date it gets converted to "5/16/2010 3:44:18 PM" . How should I read it so that I don't loose the ending '700' value so that it gets written exactly to another database.Code for reading the record:
String myConn = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
SqlConnection con = new SqlConnection(myConn);
SqlCommand cmd = new SqlCommand();[code].....
I have written the following ocde to save the data in to textfile.
using(TextWriter tw=new StreamWriter(file))
{
string refcode = txtReftypecode.Text;
tw.Write(refcode.PadLeft(1,'0'));
string priorcode = txtPrioritycode.Text;
tw.Write(priorcode.PadLeft(2, '0'));
tw.Write(txtImmediateddest.Text.PadLeft(2,'0'));
tw.Write(txtImmediateorg.Text.PadLeft(10,'0'));
string date=txtFilecreatdate.Text.Replace("/","");
tw.Write(date.PadLeft(6,'0'));
string time1=txtFilecreattime.Text.Replace(":","");
tw.Write(time1.PadLeft(4,'0'));
tw.Write(txtFileIDmodifier.Text);
tw.Write(txtRecsize.Text.PadLeft(3,'0'));
tw.Write(txtBlockingfac.Text.PadLeft(2,'0'));
tw.Write(txtFormatcode.Text.PadLeft(1,'0'));
tw.Write(txtImmeddestname.Text.PadRight(23,' '));
tw.Write(txtImmedorgname.Text.PadRight(23,' '));
tw.Write(txtRefcode.Text.PadRight(8,' '));
tw.WriteLine();
}
Now i would like to represent the data in to the corresponding to text boxes when i open that text file.
I have an input type, so a text box which has a browse button next to it. When it is clicked, the file that is selected has it's full path placed into the text box.
<input type="file" runat="server" id="txtFilename" style="width:400px" />
Where is the event!
Now that I have the path to the file in the textbox of the input type, I want to load each line of this file into a textbox that is able to handle multiline text.
I don't want the file to be uploaded to the website. I just want to contents of the file read and placed in a textbox all with one click on the browse button.
I'm trying to read the contents of a text file into a text box on my form. When I run the following code, nothing happens.
VB.NET Code:
Protected Sub lbLogs_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles lbLogs.SelectedIndexChanged
For Each li As ListItem In lbLogs.Items
If li.Selected Then Using sw As New StreamReader(li.Value)
txtLog.Text = sw.ReadToEnd sw.Close() End Using End If Next End Sub
I populate the ListBox with ListItems and each ListItem's Value property holds the full path to the file. But, I removed all of that and just put txtLog.Text = "test" inside of the "is selected" block.
know the way to read the first row(header) of a excel sheet using vb.net?
My intention is to check whether the header of the excel sheet is in the correct order(as supplied by me to the end user).
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.
View 8 RepliesI have a scenario where I have to read an excel file to dump records in a mysql database. The code generates an exception Sheet1 not found. I have pasted the code below. Please let me know the solution to the exception. I have given the correct read/write/modify settings to my excel file.
if (fileUpload1.PostedFile.FileName.EndsWith(".xls") || fileUpload1.PostedFile.FileName.EndsWith(".XLS"))
{
string fileName = Server.MapPath(fileUpload1.PostedFile.FileName);
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(connString);
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]" , oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
DataSet ds = new DataSet();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "RestaurantExcelData");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Response.Write("Data: " + ds.Tables[0].Rows[i].ItemArray[0].ToString() + "");
}
}
catch(Exception e1)
{
}
finally
{
// Close connection
oledbConn.Close();
}
}
else
{
Response.Write("File Not Excel (xls||XLS)");
}
I am trying to read data from excel file in asp.net. I have added the connection string to webConfig file:
<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=HPM_DB.xls;Extended Properties=Excel 8.0"/>
But it shows me an errormessage when I run this query:
string query = "Select * from [IO_Definition$]";
IO_Definition is the name of the spreadsheet in my excel file. I also added the excel file to the App_Data folder of the website.
The error is:
The Microsoft Jet database engine could not find the object 'IO_Definition$'. Make sure the object exists and that you spell its name and the path name correctly.
The thing is, when I write the absolute path of the excel file in the connectionString it does work. Is there anyway I can make it work without writing the absolute path?
I want to read data from an excel sheet which is in non-standard format. I have tried many things but could not find a solution,
View 1 RepliesUsing C# to develop an application.I need to upload data ( which is in excel) to a sql table.
[Code]....
I am reading a CSV file created by Excel. the string I am reading needs to be encoded (it shows small boxes instead of hebrew characters) so I wrote a lil routine to convert the string -
Function encodeName(ByVal name As String) As String
Dim src As Encoding = Encoding.UTF8
Dim dest As Encoding = Encoding.Default
Dim srcBytes() As Byte
srcBytes = src.GetBytes(name)
Dim asciiBytes() As Byte = Encoding.Convert(src, dest, srcBytes)
Dim mychars(dest.GetCharCount(asciiBytes) - 1) As Char
dest.GetChars(asciiBytes, 0, asciiBytes.Length, mychars, 0)
Dim asciiString As New String(mychars)
End Function
From what I read FileStream.ReadLine reads the text with encoding UTF8 so I try to convert from UTF8 to Encoding.Default buy instead of little boxes I get question marks.
I am reading an excel file in C#.NET. The data is being read successfully but there is problem with some hyperlinks stored in the excel file. I can read their text but i dont know how to get the underlying link/url of the column. I couldnt find much help on google as well. if someone has worked in a similar situation please let me know what can be done.edit::: i am using OleDb namespace for establishing a connection with the Excel file so if someone can post a solution which applies to this situation
View 1 RepliesI am attempting to read data from an uploaded spreadsheet using ACE.OLEDB. The code, which is running fine on dev and test machines for XL2003/2007 reports "Exernal table is not in the expected format" error on connecting on the production server for XL2007 only.The code is
Dim connectionString As String = "provider=Microsoft.Ace.OLEDB.12.0;" _ & "Data Source='" & ImportData.FullName & "';Extended Properties=Excel 12.0;" LogWebActivity.LogThis("Entering POPSUKD, ConStr=" & connectionString, LogWebActivity.LogDetailLevel.DetailAndData) Dim con As New System.Data.OleDb.OleDbConnection(connectionString) LogWebActivity.LogThis("Dimmed con", LogWebActivity.LogDetailLevel.Debugging) Try Dim cmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & WorksheetName & "$]", con) Dim adapter As New System.Data.OleDb.OleDbDataAdapter(cmdSelect) Dim dS As New Data.DataSet LogWebActivity.LogThis(cmdSelect.CommandText, LogWebActivity.LogDetailLevel.Debugging) con.Open() LogWebActivity.LogThis("Opened Connection", LogWebActivity.LogDetailLevel.Debugging) adapter.Fill(dS, WorksheetName) LogWebActivity.LogThis("Filled DataAdapter", LogWebActivity.LogDetailLevel.Debugging) _SKUS = dS.Tables(WorksheetName) LogWebActivity.LogThis("Set SKUS", LogWebActivity.LogDetailLevel.Debugging)
I have made an application to read data from an excel file and save to database "Access". It's working perfect except one thing that..There are 4 columns in excel file as :
Shop no
owner address
alternate member address
permanent member address
For all fields i have kept same datatype in database as "text". As in above image In Column A "shop no" in some rows there are same shop no with A,B,C classes. Now when I am uploading this excel file it saving all data, but in Column A cells which having only numbers would be saving to database eg: 185, 187, 188. And the cells which has both number and class eg: 185 A, 185 B would be reading as null values.
I am trying to import the excel file whose format is not in row column order in asp.net 3.5
I tried to read the file using OleDBDataAdapter but it given me the error "External file is not in proper format" something like that..
I have some code that reads records from an excel file and then imports that data into an sql database. The issue only happens when the excel file has more then 350 rows in it. In the middle of the import it just stops executing and I get directed to the 'Internet Explorer cannot display the webpage' page or just a blank page when using firefox.
I dont get any time out errors or any error at all but still I tried messing around with the script timeout settings but still does the same thing at about the 2 min 30 sec mark.
[Code]....
I need to be able to read an excel file from a file upload control but I can not save the file to disk first, it must be done in memory.
[Code]....
Above is my code for reading the data file if it IS saved to disk, but again, I have to be able to do this without saving the file to disk, it must be done in memory. I have not been able to find any sample code anywhere on how to do this from memory, everything seems to force the file be uploaded, saved to disk, and then read in the connection string, which again I can not do.
I understand how to use a connection string object to read excel sheets. What I don't know is how to follow a hyperlink inside excel, grab matching data from the linked page and return it to my database. Essentially, the spreadsheet has two columns:
Title | Class#
Class# has the hyperlink which links to an internal web site. The connected page has a course description and any pre-required classes that must be taken before you can take this class. This is where I'm rather stumped as I've not had to do this type of thing before.
Want to create a web based application for reading and writing excel files.
Issue :
1. Want to upload an excel file and store it some location.
2. Read data from excel file, performing some sorting and filtering
3. Write filtered data to new excel file.
I have a code already to read from the excelsheet but to use this code i need to pass the path where the excel file is located on the server.
I don't want to upload this file excel file to server but just want read from this excel file.
I have a fileupload control for the user to locate the file, is there way to read the excel file without uploading it to the server?
I'm able to upload and read any excel file to my server prior 2007, when I try an 07 Excel file, i'm getting the following error message:
'microsoft.ace.oledb.12.0' provider is not registered on the local machine'
do I need anything specail on the web server to allow for Excel 07 files? I'm running IIS6 and a Win 2003 server
iam Working on Asp.net Web apliation using c#.net, in system folder (Ex: E://Santhu) i have few Flat files and Excels Files .So My Application should read those files automatically and should store the data into my database..
View 1 Replies
