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:
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',
'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.
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.