Problems with values when importing XLS into SQL

[ninelg]

I have an xls file that needs to be imported into SQL Server directly from .net.
I figured out how to do this, but now my problem is the data. The following data is in the xls file: Name, Address, City, State, Zip, Phone

The phone number can come in in various values(with dashes and without). I have no way of controlling this data because it is an export out of some third party application. My problem is that the numbers with the dashes are being ignored by the Excel import.

I am using the OPENROWSET to import.

Can someone please help me?

Thanks,
Ninel

[BrianS]

What is the field type in MSSQL for the phone number?

[Tom Sawyer]

It's been a while, so my memory of this may be sketchy, but I think that when you're doing an Excel import, it checks the first few rows and if the majority of values in a column are numbers, then Excel defaults to importing it as a number and ignores any values that are strings. So if your phone number column has:

4169991212
416-999-2323
4169993434

Excel will assume that it's a numeric column and ignore the second value and just put a null there. You need to add something called an IMEX flag to your OPENROWSET to let it know to import mixed types

SELECT * INTO Table FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\importfile.xls';IMEX=1, [Sheet1$])

That should do it.

[ninelg]

Thanks Tom. We're halfway there now.

The number with the dashes is being imported, but now the number without the dashes are being imported like this: 8.13243e+009. How do I fix this?

Thanks,
Ninel

[ninelg]

It actually worked..I don't know what I was doing.

Thank you so much Tom. You're a life saver.

[ninelg]

One more question....

I also connect to the Excel Spreadsheet and use a SQL Select command to retrieve the data from the Excel Spreadsheet and populate a datagrid.

How do I incorporate the IMEX piece? In the datagrid the same data with the dashes is ignored.

Here's my code:

        sFileName = Request.Cookies("cExcelFile").Value

        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & sFileName & ";" & _
                    "Extended Properties=Excel 8.0"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()

        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        Dim objCommand As New OleDbCommand("SELECT * FROM [CustomReportRun$]", objXConn)
        objXConn.Close()
        objXConn = Nothing

 ' Create a DataSet
        Dim ds As New DataSet
        ' Populate the DataSet with the spreadsheet worksheet data
        da.Fill(ds)

        ' Bind the data to the Grid
        grdResults.DataSource = ds
        grdResults.DataBind()
        grdResults.Visible = True