SQL Server Performance

Excel file using OLEDB

Discussion in 'General Developer Questions' started by rcm29, Nov 22, 2005.

  1. rcm29 New Member

    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
  2. Madhivanan Moderator

  3. rcm29 New Member

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

    Regards,
    Resh
  4. Madhivanan Moderator

  5. rcm29 New Member

    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
  6. Madhivanan Moderator

    Cant you use like this?

    m_objOledbCommand = New OLEDB.OleDbCommand("INSERT INTO [ExcelAdaptorErrors$(A1, A2, A3] values('" + l_strErrorMessage + "',value2','value3')", GetOleDBConnection(FileName))


    Madhivanan

    Failing to plan is Planning to fail
  7. rcm29 New Member

    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
  8. Madhivanan Moderator

    In the first link I specified you can use

    Select * from
    OpenRowset(...........
    'Select col1,col2,col3 from sheetName'..)

    Madhivanan

    Failing to plan is Planning to fail
  9. rcm29 New Member

    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
  10. Madhivanan Moderator

  11. rcm29 New Member

    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
  12. rcm29 New Member

    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
  13. Madhivanan Moderator

    Cant you use SQL Server table?
    If you dont know the number of columns and column names, then the best way is to export the data to SQL Server table. Using the link I provided you can get the data from first column though you dont know the column name.

    If you are not using SQL Server, then in VB.NET use File System Object or its equivalent ang get the column name and data and insert them into other Excel File. Search for sample Codes in Google

    Madhivanan

    Failing to plan is Planning to fail
  14. rcm29 New Member

    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
  15. Madhivanan Moderator

    quote:Originally posted by rcm29

    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
    Well. You said column names are not known then how did you create table with the column names? Did you use col1,col2,.... or apply Select * into from...?

    Madhivanan

    Failing to plan is Planning to fail
  16. rcm29 New Member

    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
  17. Madhivanan Moderator

    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
  18. rcm29 New Member

    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
  19. Madhivanan Moderator

    Your EXCEL file should have valid column names. If there is space in the column names make it as [sofware engg]

    Madhivanan

    Failing to plan is Planning to fail
  20. rcm29 New Member

    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
  21. Madhivanan Moderator

    Then you should always have an extra column to store error message or in the table have that column and import them by using Error message as the last column of your query

    Madhivanan

    Failing to plan is Planning to fail
  22. rcm29 New Member

    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
  23. Madhivanan Moderator

    Why cant you add one more column at the EXCEL file using VB.NET filesystem object before you querying to that EXCEL file?

    Madhivanan

    Failing to plan is Planning to fail
  24. rcm29 New Member

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

    Regards,
    Resh
  25. rcm29 New Member

    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
  26. Madhivanan Moderator

    Yes I meant that

    Madhivanan

    Failing to plan is Planning to fail
  27. rcm29 New Member

    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
  28. Madhivanan Moderator

    I think you cant add column to the EXCEL file using SQL Server. Instead of adding a column to EXCEL, why cant you add the Error Message as a new column in the select query as I specified earlier?

    Madhivanan

    Failing to plan is Planning to fail
  29. rcm29 New Member

    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
  30. Madhivanan Moderator

    &gt;&gt;And just got news that this requirement of having a new column is canceled <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thats good<br /><br />What have you decided to do now?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  31. rcm29 New Member

    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
  32. Madhivanan Moderator

    You are welcome [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page