October 12, 2008 at 6:38 pm
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.
October 12, 2008 at 7:16 pm
Pathing is based on the server, not the client. you might need to copy the file to the server.
October 12, 2008 at 8:53 pm
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?
October 13, 2008 at 12:16 pm
"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.
October 14, 2008 at 7:32 am
Refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Failing to plan is Planning to fail
October 14, 2008 at 7:47 am
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
October 14, 2008 at 8:00 am
"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.
October 14, 2008 at 8:00 am
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?
October 14, 2008 at 8:09 am
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 ????
October 16, 2008 at 8:49 am
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
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