Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Port Data From EXCEL to SQL Server

aarunlalaarunlal Posts: 95Member
[b][red]This message was edited by aarunlal at 2005-9-26 3:29:4[/red][/b][hr]

How can we port Data from EXEL to SQL Server(SQL server is another mechine which is in LAN) ???

i used the query as

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database="C:X.EXEL";HDR=YES','SELECT * FROM ["Sheet1$"]')"

but system is providing an error like this :

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.

IF iam running the application in the SQL server mechine with Excel file ..it is working properly!!!

can any one help me

regards
Arunlal
«1

Comments

  • rlcrlc Posts: 448Member
    : [b][red]This message was edited by aarunlal at 2005-9-26 3:29:4[/red][/b][hr]
    :
    : How can we port Data from EXEL to SQL Server(SQL server is another mechine which is in LAN) ???
    :
    : i used the query as
    :
    : select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database="C:X.EXEL";HDR=YES','SELECT * FROM ["Sheet1$"]')"
    :
    : but system is providing an error like this :
    :
    : OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
    :
    : IF iam running the application in the SQL server mechine with Excel file ..it is working properly!!!
    :
    : can any one help me
    :
    : regards
    : Arunlal
    :


    Your path to the excel file needs to include the server it is on... and be in a location where your sql server has permission to it.

    ~rlc
  • aarunlalaarunlal Posts: 95Member
    Thanks ...

    but still i can't clear what u are saying ...
    how can i include path in the server....
    can u help me with source code ?????

    this is my exact source code

    ************************************************************
    Private Sub cmdView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdView.Click
    Try

    If txtWorkSheet.Text = "" Then
    strWorksheet = "Sheet1$"
    Else
    strWorksheet = txtWorkSheet.Text & "$"
    End If

    cn = New SqlConnection

    strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & txtFilePath.Text & ";HDR=YES','SELECT * FROM [" & strWorksheet & "]')"

    cn.ConnectionString = strConnectionstring
    cn.Open()

    ds.Clear()
    Dim da1 As New SqlDataAdapter(strSQL, cn)
    da1.Fill(ds, "ViewAGSPSchools")
    DataGrid1.DataSource = ds.Tables("ViewSchools")

    cn.Close()
    cn = Nothing
    Catch
    MsgBox(Err.Description)
    cn.Close()
    cn = Nothing
    End Try
    End Sub
    ************************************************************

    Hope u repaly sooon...

    reagrds
    Arunlal


    : : [b][red]This message was edited by aarunlal at 2005-9-26 3:29:4[/red][/b][hr]
    : :
    : : How can we port Data from EXEL to SQL Server(SQL server is another mechine which is in LAN) ???
    : :
    : : i used the query as
    : :
    : : select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database="C:X.EXEL";HDR=YES','SELECT * FROM ["Sheet1$"]')"
    : :
    : : but system is providing an error like this :
    : :
    : : OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
    : :
    : : IF iam running the application in the SQL server mechine with Excel file ..it is working properly!!!
    : :
    : : can any one help me
    : :
    : : regards
    : : Arunlal
    : :
    :
    :
    : Your path to the excel file needs to include the server it is on... and be in a location where your sql server has permission to it.
    :
    : ~rlc
    :

  • rlcrlc Posts: 448Member
    So when you are on a computer other then SQL Server what does your 'txtFilePath.Text' and are the permissions set for that folder?

    example: txtFilePath.Text = "\SomeComputerSomeSharedFolderSomeExcelFile.xls"

    : strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & txtFilePath.Text & ";HDR=YES','SELECT * FROM [" & strWorksheet & "]')"

    ~rlc
  • aarunlalaarunlal Posts: 95Member
    only SQL server is in different mechine...
    application is running in my mechine , from there iam taking the Excel file.. only database is staying on different mechine...

    regards
    Aunlal



    : So when you are on a computer other then SQL Server what does your 'txtFilePath.Text' and are the permissions set for that folder?
    :
    : example: txtFilePath.Text = "\SomeComputerSomeSharedFolderSomeExcelFile.xls"
    :
    : : strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & txtFilePath.Text & ";HDR=YES','SELECT * FROM [" & strWorksheet & "]')"
    :
    : ~rlc
    :

  • rlcrlc Posts: 448Member
    Right...

    So when that SQL code runs the SQL machine it is seeing 'C:' and looking at the 'C' drive on the SQL machine not on the calling machine. You need that path to be machine specific so that when it looks for that file it looks on the right machine.

    ~rlc
  • aarunlalaarunlal Posts: 95Member
    ok right..
    Thank you very much Mr.rlc

    then is there any way to do the relavant process in different mechines.
    i thought that it is possible and can u help me for that ...



    regards
    Arunlal

    : Right...
    :
    : So when that SQL code runs the SQL machine it is seeing 'C:' and looking at the 'C' drive on the SQL machine not on the calling machine. You need that path to be machine specific so that when it looks for that file it looks on the right machine.
    :
    : ~rlc
    :

  • rlcrlc Posts: 448Member
    You can get the specific machine name from System.Environment.MachineName.

    ~rlc
  • aarunlalaarunlal Posts: 95Member

    Mr.rlc

    actually i did't get what you saying ? How can we implement this ?
    Will you please send me the source code .

    hope you respond soon

    Thanks and Regards
    Arunlal

    : You can get the specific machine name from System.Environment.MachineName.
    :
    : ~rlc
    :

  • rlcrlc Posts: 448Member
    You need to put the machine in your path so the SQL PC knows where the file is, I edited your code below to do that. However, you will need to make sure the SQL PC has permissions to access this file. I suggest you create a shared folder to store these files in.

    [code]
    ************************************************************
    Private Sub cmdView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdView.Click
    Try

    Dim l_FilePath as string

    If txtWorkSheet.Text = "" Then
    strWorksheet = "Sheet1$"
    Else
    strWorksheet = txtWorkSheet.Text & "$"
    End If

    cn = New SqlConnection

    l_FilePath = "\" & System.Environment.MachineName & "" & txtPath.text

    strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & l_FilePath & ";HDR=YES','SELECT * FROM [" & strWorksheet & "]')"

    cn.ConnectionString = strConnectionstring
    cn.Open()

    ds.Clear()
    Dim da1 As New SqlDataAdapter(strSQL, cn)
    da1.Fill(ds, "ViewAGSPSchools")
    DataGrid1.DataSource = ds.Tables("ViewSchools")

    cn.Close()
    cn = Nothing
    Catch
    MsgBox(Err.Description)
    cn.Close()
    cn = Nothing
    End Try
    End Sub
    ************************************************************
    [/code]
  • aarunlalaarunlal Posts: 95Member
    [b][red]This message was edited by aarunlal at 2005-10-3 21:52:22[/red][/b][hr]
    Still when iam trying to export data using the code that had given ,the given error message is comming .(My DB server is in different mechine and the rest of the things are in my mechine).

    Error message:
    "OLE DB provider "microsoft .jet .OLEDB.4.0 reported an error.The provider did not give any information about the error"

    reagrds
    Arunlal





    : You need to put the machine in your path so the SQL PC knows where the file is, I edited your code below to do that. However, you will need to make sure the SQL PC has permissions to access this file. I suggest you create a shared folder to store these files in.
    :
    : [code]
    : ************************************************************
    : Private Sub cmdView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdView.Click
    : Try
    :
    : Dim l_FilePath as string
    :
    : If txtWorkSheet.Text = "" Then
    : strWorksheet = "Sheet1$"
    : Else
    : strWorksheet = txtWorkSheet.Text & "$"
    : End If
    :
    : cn = New SqlConnection
    :
    : l_FilePath = "\" & System.Environment.MachineName & "" & txtPath.text
    :
    : strSQL = "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & l_FilePath & ";HDR=YES','SELECT * FROM [" & strWorksheet & "]')"
    :
    : cn.ConnectionString = strConnectionstring
    : cn.Open()
    :
    : ds.Clear()
    : Dim da1 As New SqlDataAdapter(strSQL, cn)
    : da1.Fill(ds, "ViewAGSPSchools")
    : DataGrid1.DataSource = ds.Tables("ViewSchools")
    :
    : cn.Close()
    : cn = Nothing
    : Catch
    : MsgBox(Err.Description)
    : cn.Close()
    : cn = Nothing
    : End Try
    : End Sub
    : ************************************************************
    : [/code]
    :



«1
Sign In or Register to comment.