Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    6

    Unanswered: Updating a Datatable Using an Excel Spreadsheet

    Hello all. I am new to Microsoft SQL Databases and I've been trying to update an existing Table on my Database from an Excel spreadsheet. From my research it seems as though this is possible and I believe I'm very close to achieving the goal, however I'm stuck with an error that reads "SQL Server Database Error: Error converting data type varchar to float."

    I'm not sure why this error is occurring.

    The following is the code that I am using:


    Code:
    UPDATE Name_Address
    SET Name_Address.ADDRESS_1 = T.Street_Address_1
    FROM TESTIMIS_2.dbo.Name_Address Name_Address
    	INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    		'Excel 8.0;Database=C:\Ed\AlumniChapterReport_2.xls;', 
    		'SELECT ID, Street_Address_1
    	FROM [AlumniChapterReport$]') T
    		ON Name_Address.ID = T.ID
    For reference, the Excel sheet is a 2x2 sample sheet. The file name is 'AlumniChapterReport_2.xls' and worksheet name is 'AlumniChapterReport'.

    The columns in the Excel sheet are 'ID' and 'Street_Address_1'.

    Any suggestions or help would be greatly appreciated.
    Last edited by tetros; 03-22-11 at 16:20.

  2. #2
    Join Date
    Sep 2010
    Posts
    153

    check out the format.

    As far as i know this kind of error happens due to incompability format.

    1)right click on the column of the excel cell. click on "format cell" option and check whether all the columns of excel sheet in "text or all" format.


    2)sometime excel sheet takes string datatype with varchar(255) size which you cannot reduce in excel sheet. All you need to do is increase the size of your datatype in database.


    Anyways i think option 1 could be the reason.

  3. #3
    Join Date
    Mar 2011
    Posts
    6

    Format Change Not Working

    Sunny thank you for your response.

    I changed the format and it did not work out. I tried both 'General' and 'Text' format but regardless of format, I would still get the error "SQL Server Database Error: Error converting data type varchar to float."

    I even tried importing to the database where the column had varchar(255) but had no luck there. It seems like Microsoft Access would be the best solution for me. I will inform you all if Access does the trick.

  4. #4
    Join Date
    Mar 2011
    Posts
    6

    Successful in Access

    Update:

    I was successfully able to update the database once I imported the file to Access. I believe Sunny was right and I was not able to change the datatype in the Excel file from varchar(255).

    However, the same logic in my code I posted earlier worked.

    Thanks again to Sunny for helping me solve my issue!

  5. #5
    Join Date
    Sep 2010
    Posts
    153
    congrats brother

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •