.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

Excel Import Problem... Please help

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :ASP.Net
 

Hi... Experts,

I am having a problem while importing data from Excel to Oracle.

My Excel connection string is as follows

xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                               "Data Source=" & Server.MapPath("~/Uploads/New_" + Now.Date.ToString("MM_yyyy") + ".xls") & ";" & _
                               "Extended Properties=""Excel 8.0; IMEX=1;"""

 

The data in Excel is as follows.

 

2087763
2087763
2087763
2088262
2088262
2088262
2088262
201131642736.C3O
201131642736.C3O
201131642736.C3O
201131642736.C3O
201131642736.C4O
201131642736.C5O
201142065047.C1O
20113835053.C1O
20113835053.C1O
20113835053.C1O
20113835053.C1O
20113835053.C1O

The problem is that while reading the records and filling the dataset, the values with '.C10' or '.C20' or '.C30' etc is coming as null in dataset.

Please help me in solving this issue. Its urgent.

Thanks & Best Regards




View Complete Post


More Related Resource Links

data type problem in import data from excel file

  

Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)
                da.Fill(datatable)


Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1

                       &n

Excel Date Field Import Problem in Oracle SQL Developer

  

Hi All,

I have an Excel file (which has data imported from Oracle 10G Database) one of the fields is a Date Filed which has values like 28-JAN-11 03.25.11.000000000 PM ( Date field is Oracle Time Stamp(6) in Database )

When I am trying to Import the same Excel file from another Oracle 10 G database (for another database/application), I get an error because the data field is not being recognized by Oracle 10G --> Import is being done by ORACLE SQL Developer (Table (field) has TIMESTAMP(6) as the datatype)

How can I import that field ?? For time being I made the TIMESTAMP to VARCHAR2 and its working but I could not convert that to Date field again in C# CODE ( it says not a valid date type).


Excel Date Field Import Problem in Oracle SQL Developer

  

Hi All,

I have an Excel file (which has data imported from Oracle 10G Database) one of the fields is a Date Filed which has values like 28-JAN-11 03.25.11.000000000 PM ( Date field is Oracle Time Stamp(6) in Database )

When I am trying to Import that Excel file from another Oracle 10 G database (for another database/application), I get an error because the data field is not being recognized by Oracle 10G --> Import is being done by ORACLE SQL Developer (Table (field) has TIMESTAMP(6) as the datatype)

How can I import that field ?? For time being I made the TIMESTAMP to VARCHAR2 and its working but I could not convert that to Date field again in C# CODE ( it says not a valid date type).

 


Import excel data to Sql Server 2005 problem

  

Hello:

 

I have tried to import data from Excel 2003 to Sql Server 2005 using the Import Wizard to no avail.  Here is my issue:

 

Excel column has            After import, database table

these value sets:             has these corresponding value sets:

1.01                              null

1.02                              null

1.04                              1.04

1.05                              1.05

1.06                              null

While Importing Excel 2007 file to Datatable - headerrow problem

  

Hi there,

 

I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)

 

string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            conn.Open();
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            //At this point row count=2 which doesn't make sense


 

 

 


EF 4: Function Import problem with Stored Procedures

  

I have the following SP which includes a temp table:

 DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar

Excel-like experience - gridview focus problem

  

Hi.

I'm creating a page where people can order several items from a list without having to press "edit" and "save" in between like you normally would in a gridview. The user writes down the quantity, and finally presses a "submit" button that saves the order.

I want the user to have a windows form (or excel) like eperience, so once the user puts in the quantity and moves on to the next item, either by TAB or by clicking on the TextBox, i make some calculations without the user beeing bothered with a full refresh or having to press a buton to execute the event, so I place the controls inside an UpdatePanel and use TextBox.TextChanged. The event runs code that calculates the sum for the row (price * quantity) and shows that in a column called "Sum", and updates a label in a fixed position with the new order total ("Total").

Any ideas on how to achieve this?

Right now I use a GridView with a templatefield for the Quantity and set the TextBox.TextChanged event to fire to make the calculations. The problem is that the focus then changes.


By using SmartNavigation or the ScriptManager.SetFocus I manage to retain focus on the TextBox that executed the Postback, but the user might have moved on to a textbox far down the gridview (thus causing the postback by leaving the previo

reading excel file problem

  

Hi,


i have 200 rows in my excel file. im using OleDbConnection to read the excel file.

The problem is that it will read all the blank rows from row 200 onwards. Is there a configuration im missing ? or is there a way to import all rows that has data? Here's some of my code.


string excelConnectionString =
               "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filePath + ";" +
                "Extended Properties=Excel 8.0";

OleDbConnection excelConnection =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO'");


OleDbCommand cmd = new OleDbCommand("Select * from [list$]",excelConnection);







Problem with Excel

  

Hi All,

I am trying to create hyperlink in Excel using below code, But it is giving me error:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))System.Runtime.InteropServices.COMException (0x80020005): Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Code:

 Dim oXL As Excel.Application
Dim oWB As Excel._Workbook
Dim oSheet As Excel._Worksheet

oXL = New Excel.Application()
           
oWB = DirectCast(oXL.Workbooks.Add(True), Excel._Workbook)
oSheet = DirectCast(oWB.ActiveSheet, Excel._Worksheet)

oSheet.Hyperlinks.Add(oSheet.Cells("A3"), "http://www.MySite.com", Nothing, "My Site Info", "clickhere")

It is giving the error on the above line when trying to create hyperlink.

Can you please tell me where I am doing wrong.

Thanks

Yanku

 


Excel problem

  
I m operning excel file using Applicaion object of Excel,InteropNow i am closing it but it doesn't release from memoryIt's process is runnig like Excel.exeHow can i release it.

Excel Connection Manager Problem

  
I am building a package that has 2 tasks that need to use the same excel connection manager. I use a execute sql task to dynamically build a table from the headers of the excel sheet  ,then I use an excel source component to load the data from the sheet. However when I try to edit the excel source and switch the data access mode to a variable and select the variable(for the path), when I try to close the editor I get this message 'S:\myfolder'.  It is already opened exclusively by another user, or you need permission to view its data. I need this package to access the same folder each time because the files will be placed in that folder. This package also has a branch for loading a text file into sql server. It access the same folder but uses a flatfile connection manager It looks like permissions on the folder are correct

how to import the data of excel into gridview1 using asp.net/c#

  
i m having excel file a.xlsi m having gridview1,fileupload1how to import excel file from fileupload1 get the destination of a.xls and show the values in gridview1if any boby knows full code answer/mail me at ponmanivannangj@mobiusservices.in

Import Excel spreadsheet To Sharepoint List

  
Hi, I am having sharepoint 2010 RTM trial on Windows Server 2008 SP2. I want to populate csv filke data to new sharepoint list. I am using custom List Wizard to achieve this... When I Import csv on server then everything is fine but when i inport csv from client, i get the following error "The Specified is not valid Spreadsheet or contains no data to import" I have also checked to import excel workbook but it gives the same error on client and do great on server..... can anybody have any idea what is wrong ?? thanks

Problem opening excel

  
hello everyone, i m having a serious problem, and i have been searching for days and i couldn't find a solution, my application deployed on IIS 7.5 is working very good except when trying to open excel file, i am getting an error telling me that the path is wrong or page is already used or the workbook you are trying to save already exists. to open excel m using: Dim xlApp = new Excel.application Dim xlWorkbook = new Excel.workbook Dim xlWorksheet = new Excel.worksheet   xlapp = new Excel.application xlworkbook = xlapp.workbooks.open(xxx) and here is the line of error !!!!     it seems to be a problem with the security privileges of the users, i have been told to find the excel application in Component services - COM+ and change it's security, but the problem is that i can't find the excel application in COM+ so i have tried this: Component services - mycomputer - properties - COM security - Edit default(for acces, launch and activiation permissions) and grant it for Network Service, USers and IIS_IUsers and still that did not work out.   any solution?  

Problem with import using SharePoint Content Deployment and Migration API

  
Hi all, I have a requirement to develop a web part to copy folder (With sub folders and files) between document libraries of different site collection. I am using SharePoint Content Deployment and Migration API for this project and getting following error on import.   Does anyone have any idea what is the meaning of the following error?     I will appreciate if anyone can point me the direction to get rid of this error..   Thank you in advance for your valuable time and effort.   -MMRAhman    Error:   “Cannot complete this action. Please try again.”   stackTrace from log :   [8/1/2010 10:54:24 PM]: Progress: Starting content import. [8/1/2010 10:54:24 PM]: Progress: De-Serializing Objects to Database. [8/1/2010 10:54:24 PM]: Progress: Importing Folder /sites/projects/mmr/Documents/Forms/Document. [8/1/2010 10:54:24 PM]: Progress: Importing Folder /sites/projects/mmr/Documents/Forms/Folder. [8/1/2010 10:54:24 PM]: FatalError: Cannot complete this action.   Please try again.    at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)    at Microsoft.SharePoint.SPField.set_SchemaXml(String value)    at Microsoft.SharePoint.Deployment.ContentTypeSerializer.UpdateContentTypeFields(SPContentType sourceContentType, SPContentType targe

Text truncated to 255 using SSIS ACE.oledbprovider to import excel 2007 spreadsheet

  
Hi All, Trying to use SSIS SQL 2005 to import an Excel 2007 file (.xlsx). I'm using Office 12.0 Access Database Engine OLEDB Provider with extended properties Excel 12.0;HDR=Yes;IMEX=1 The problem is when I have text with more than 255 characters. It basically defaults the source output to Unicode string[DT_WSTR] 255, If I change it it errors out. I have done the registry change according to http://msdn.microsoft.com/en-us/library/ms141683.aspx but I think it only applies to Excel source not OleDb Source. (A Excel source does give me a text instead of string )   I have also tried to increasing the length of output such as 4000 instead of 255 but it doesn't allow either.   Anybody can help?I'm stuck for a day. Thank you very much for your help. Regards, Koala  

Import Excel data into SQL Server express 2008

  
I have recently installed SQL express 08 and created a table to import excel data into.  Im running on a windows 7 64 bit OS i5 processor.  I've disabled the firewall (temporarily) and will set the necessary ports once this is working. I run the following query:  (which i found from multiple sources on the net) Select   * From OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\Public\lenovoBkup\contacts\outlookFiles\noib2a.xls', 'Select * From [sheet1$]' )  and receive the following message: Msg 7308, Level 16, State 1, Line 1                                                                                  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.  (how did they know i was single and living in an apartment? lol) so after research of the following message i restarted the system and relaunched man
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend