2PTTechnology

I have issues uploading data from Excel into SQL when the column has both numeric and alphanumeric characters.

First I copy the excel file onto the C drive of the SQL

Than I create a link to the excel file Object Explorer -> Server Objects -> Linked Servers -> Right click and create link

1. In the left pane, select the General page, and then follow these steps:

a.       In the first text box, type any name for the linked server.

b.      Select the Other data source option.

c.       In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.

d.      In the Product name box, type Excel for the name of the OLE DB data source.

e.       In the Data source box, type the full path and file name of the Excel file.

f.        In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.

g.       Click OK to create the new linked server.

Now the issue that I have is in the first column. I have numeric and alphanumeric characters so what I did was sort all the items and moved all the alpha characters  to sheet two. I was still having issues with other (numeric) columns, cells that had a null value in other columns, instead of them coming thru as numeric, were coming in as VarChar.  The reason for that was either SQL or Excel was trying to look at the first 5 rows or so and average it out to see what is in that column type so if I had a total of 10 rows and the first three were null ant the rest had a number, it would bring in that column type as Varchar and all my data for that column would come in blank.

here is the select to get the data