Importing from Excel to SQL via VB.NET

  • Hi there all.

    I am using the 'insert into' command to import from excel into SQL database. The function works correctly and without any problems.

    The path of the file is passed through a FileOpenDialiog in vb.net

    The problem is when I use this command on a client PC (SQL Server on another Machine), the path on the server is different from the path of the client.

    I don't want to open the file in a dataset and then write line by line to the Database since I have at least 50,000 records each time I want to import.

    Any suggestions?

    Thanks.

  • Pathing is based on the server, not the client. you might need to copy the file to the server.

  • Why not build a client-side SSIS package, which takes the file name in as a parameter? You would still be able to access all of the .NET stuff from there, and just add-in the DTS interop to fire this off.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • "The path of the file is passed through a FileOpenDialiog in vb.net" ?

    May be I don't exactly understand what you will acomplish but:

    If it is a portable solution I save the parameters which will change from instance to instance

    in separate textfiles which are loaded by vb.net. Eg you can save the path/name of the

    Excelfile in a text file and open it by VB.net save it in a string etc. This approach has saved me

    a lot of extra job.

  • Refer

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


    Madhivanan

    Failing to plan is Planning to fail

  • Instead of passing a file location, what you might want to do is have the .NET app upload the file to a standard location, and give it a unique name (perhaps a GUID), then pass that data to the import proc.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • "Instead of passing a file location, what you might want to do is have the .NET app upload the file to a standard location, and give it a unique name (perhaps a GUID), then pass that data to the import proc. "

    This is an excellent approach.

    I use it then the files (exel or text) has the same format and there are

    a bunch of them to be imported. I have a loop which copy the files to a folder on the server with one

    name (copy/replace) and one import procedure.

  • The main problem is that I have no permission to copy any file to the server since the server doesnt belong to my firm.

    So copying the file into a predifined folder on the server is out of the question.

    Do you think loading the file and using a datareader is a good idea when you have 50,000 records in the excel file?

  • This is passed from vb.net(Executble file) to the Server using this :

    SELECT * INTO table FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=" +

    OpenFileDialog1.FileName + ";Extended Properties=Excel 8.0')...[Sheet1$]

    The problem exists when the exe file is not on the server. The path changes. (OpenFileDialog1.FileName )

    You sent me another similair procedure in ur link:

    select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\testing.xls;HDR=YES',

    'SELECT * FROM [Sheet1$]')

    Is there any difference between them ????

  • msaidi2 (10/14/2008)


    This is passed from vb.net(Executble file) to the Server using this :

    SELECT * INTO table FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=" +

    OpenFileDialog1.FileName + ";Extended Properties=Excel 8.0')...[Sheet1$]

    The problem exists when the exe file is not on the server. The path changes. (OpenFileDialog1.FileName )

    You sent me another similair procedure in ur link:

    select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\testing.xls;HDR=YES',

    'SELECT * FROM [Sheet1$]')

    Is there any difference between them ????

    If your file exists in the client system, use UNC

    \\system_name\driver_name\file_path


    Madhivanan

    Failing to plan is Planning to fail

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply