Excel file using OLEDB

Hi,
I have an excel file say ExcelAdaptor.xls having sheet name as ExcelAdaptorErrors and having columns say 'ID','Message'.
Please let me know any way to insert data into this excel file using oledb without the use of column names.

This is what i have been doing
m_objOledbCommand = New OLEDB.OleDbCommand("INSERT INTO ExcelAdaptorErrors (Message) values('" + l_strErrorMessage + "')", GetOleDBConnection(FileName))
**GetOleDBConnection is the method used to get connection


In this case specifying the column name becomes a necessity. Is there any way i can specify the column index from excel sheet like A1, B10 etc.







Regards,
Resh

I had already checked that post. It doesnt specify usage of column indexes in excel tho

Regards,
Resh

I cannot use range in my case since this will be used in a generic case. i.e i can have any excel file as input.
So i cannot specify a fixed range

Im now using the column index
eg.
m_objOledbCommand = New OLEDB.OleDbCommand("INSERT INTO [ExcelAdaptorErrors$A1:A1] values('" + l_strErrorMessage + "')", GetOleDBConnection(FileName))

but this way i will be able to enter into only one cell at a time.

I now have 2 questions
1. Is it possible to put this index value into a variable and then increment it accordingly so that while in a loop i can enter data into different columns eg A1,A2,A3 etc.
I tried increment an integer variable and then using [ExcelAdaptorErrors$var:var]but this gives an error.

2. Once if i enter into one row how can i make sure that next time the same row is not overwritten but it enters into the next row??

Regards,
Resh

Ok ill tell the exact thing

i have a input excel file which can have any no. of columns. (Each time a file is given as input it can have varied no. of columns)

When a particular row index in that file is specified i must get the row data ( from all the columns) and put it into another excel file in a diff location and append an error message to it).
eg. get the data from a1,b1,c1 from file eg1.xls and put it into say a2,b2,c2 of eg2.xls

I cannot use the query as specified ("INSERT INTO [ExcelAdaptorErrors$(A1, B1, C1] ....
since the number of columns is different for every file.


Is there any way to implement this.

Regards,
Resh

In this case we are coming back to specifying the columns explicity col1,col2,col3...
This is all fine if all my excel files will have the same number of cols always
but will not work when its dynamic

anyways thanks a looooooooooooot for your inputs
im not able to fig out a way to build the query dynamically!

Regards,
Resh

I think im still not being too clear with my request so here its again...

I have a request where i have an excel file A.xls which is given as input. This file will have one sheet and can have any number of columns. (i.e every time a new xls file is given as input the number of columns may vary )

Now i have a method Message which takes the row index from this excel file as input. Depending upon the row index i must fetch the row from A.xls and insert it into another file B.xls.
This method can be called multiple times and so each time the data from the input row index must be inserted into the next row in file B.xls.

I am using OLEDB to read the excel file.
I tried using the following technique where

m_objOledbCommand = New OLEDB.OleDbCommand("INSERT INTO [ExcelAdaptor$A1:G1] values(value1,value2,value3.....)", GetOleDBConnection(m_strErrorFileName))



the problem here is hardcoding of A1:A10 coz the no. of columns can be different everytime. also next time i must insert into A2:G2 etc....



Can you pls let me know a way to go about with this without creating a procedure and handle only in .net code


Regards,
Resh

im really sorry abt this if the prev request is not very possible
is there a way to read a specific row from one excel file and insert it directly into another excel file.

can i use this SELECT * INTO [Excel 8.0;Database=C:Book1.xls].[Sheet1] FROM [MyTable]


i mean insert into Excel file 2 from a particular row in Excel file 1


Regards,
Resh

Thanks a lot... i was able to implement my request sucessfully by building the create table query first and then the insert query...


Regards,
Resh

Since column names were not known...<br />i first got the data from the excel sheet 1 into a dataset ( here column names will be in the first row of the dataset)..<br /><br />1. To write the required data into the new excel sheet<br />I first had to create a table in the new sheet<br />i obtained the total no. of columns <br />Looping through each column in the dataset i obtained the column name from the first row and datatype of the column from the second row which had data <br />i concatenated the column name and the datatype ....in this way i built my query string...<br /><br />l_strCreateQuery = "CREATE TABLE ExcelAdaptorErrors ("<br /><br />For l_intLoopCount = mc_One To l_intColumnCount<br /> If l_intLoopCount &gt; 1 Then<br /> l_strCreateQuery = l_strCreateQuery & ","<br /> End If<br /> l_strDataType = m_ExcelDataSet.Tables(0).Rows(mc_One).Item(l_intLoopCount - mc_One).GetType.ToString<br /> l_strDataType = l_strDataType.Replace("System.", "")<br /><br /> l_strColName = Trim(m_ExcelDataSet.Tables(0).Columns(l_intLoopCount - mc_One).ColumnName)<br /> l_strCreateQuery = l_strCreateQuery & l_strColName & " " & l_strDataType<br />Next<br /><br />l_strCreateQuery = l_strCreateQuery & ")"<br /><br />2. To insert the data from a required row into the new excel created.<br />i fetched the data from the required row from the dataset.<br />again looped through the columns built the query string and inserted the data<br /><br /> l_strInsertQuery = "INSERT INTO [" & l_strSpreadSheetName & "] VALUES ("<br /> <br /> For l_intLoopCount = mc_One To l_intColumnCount<br /><br /> If l_intLoopCount &gt; 1 Then<br /> l_strInsertQuery = l_strInsertQuery & ","<br /> End If<br /><br /> l_strColValue = Trim(m_ExcelDataSet.Tables(0).Rows(RowIndex - mc_Two).Item(l_intLoopCount - mc_One))<br /> <br /> If IsNumeric(l_strColValue) Then<br /> l_strInsertQuery = l_strInsertQuery & l_strColValue<br /> Else<br /> l_strInsertQuery = l_strInsertQuery & "'" & l_strColValue & "'"<br /> End If<br /> Next<br /><br /> l_strInsertQuery = l_strInsertQuery & ")"<br /><br /><br />I do not know if this is the optimal way of doing it. but it serves my purpose <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />3. In addition i found a way of obtaining the spreadsheetname too by using <br /> l_SchemaTable = m_objOledbConnection.GetOleDbSchemaTable(OLEDB.OleDbSchemaGuid.Tables, Nothing)<br /><br /> l_strSpreadSheetName = l_SchemaTable.Rows(0).Item(2)<br /><br />4. My new problem...<br />I have a excel file say with 5 columns when an error occurs i should insert data from the row at a given index and a error message into this file.<br />Getting data from the row and inserting into this file is fine since it will follow the same apporach used earlier.... but inserting a new column into the existing file is the problem..!!<br />any idea how to go abt it....<br /><br />Regards,<br />Resh

One point ...<br />The create query will not work properly and will give error if the input excel file does not have column names and if any data in a column has a space between words <br />eg. consider data in column 2 as 'software engg'. <br />For the first column it will give the column name as F1 but for other columns it will take the data in the column as the column name... this becomes the problem for data like given in the eg. <br /><br />Yet to find a way out of this... any suggestion ?? <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br />Regards,<br />Resh

quote:Originally posted by Madhivanan

Good to know you completed it
To insert data to new column you need to have new column in the EXCEL file and use query

Insert into TargetTable(newCol) Select NewCol from SourceTable

Madhivanan

Failing to plan is Planning to fail

MyCase:
I have an excel file Example1.xls which has file columns say A,B,C,D,E.

Now when some error occurs i have to retrieve data from the input row index (this im doing by retrieving from the dataset) and insert the same data along with a new error message into the same file Example1.xls. So as a result my file should now have 6 columns A,B,C,D,E and ErrorMessage.

The suggested method will fail since the 6th column doesnt exist in my file. So i will get error.
I tried using ALTER table query to add a new column. But it doesnt work...!! It gives error " Invalid Operation"..

Regards,
Resh

My file wil not have the 6th column. i will have to add it at run time just before inserting the error message.
adding the column using alter table fails.
using the insert statement like this insert into table (a,b,c,d,e,ErrorMessage) values (....)
gives Message"The INSERT INTO statement contains the following unknown field name: 'ErrorMessage'. since its actually not there in the file.

any help to add a column to an existing file.
why cant we use alter table query

Regards,
Resh

I dint get that. How doi add another column to the excel file usign file system object ?

Regards,
Resh

Do you mean to say something like ExcelObject.columns.add
then i cant use it since we dont have the license for it as mentioned earlier.

Regards,
Resh

So me wont be able to add columns to the excel using file system object.
I checked out the net... but found no solution to adding columns to excel sheet at runtime...
Really sorry...
But any idea how this can be achieved...

Regards,
Resh

I cant add it as a new column in my insert query since the column doesnt exist in the excel...<br /><br />I have to insert data into the existing columns + new column in my excel sheet.<br /><br />And just got news that this requirement of having a new column is canceled <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br />Regards,<br />Resh

So there will be need to Insert error message into the existing file (i.e add a new col and enter error message).<br />It will be only the 1st req. i.e create new file add the data from a given row in the input excel file along with an extra column to hold error message.<br />Here i will use the create table query as specified earlier to create my new file and then insert.<br /><br />Thanks a looooooooooooot for your constant help.<br />Ive learnt a lot through the links mentioned and suggestions given.<br />Thanks a lot once again <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Regards,<br />Resh