Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Importing from Excel to SQL via VB.NET Expand / Collapse
Author
Message
Posted Sunday, October 12, 2008 6:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 18, 2008 5:16 PM
Points: 3, Visits: 10
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.

Post #584617
Posted Sunday, October 12, 2008 7:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
Pathing is based on the server, not the client. you might need to copy the file to the server.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #584620
Posted Sunday, October 12, 2008 8:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:32 PM
Points: 7,152, Visits: 15,633
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?
Post #584636
Posted Monday, October 13, 2008 12:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:30 PM
Points: 205, Visits: 1,344
"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.
Post #585052
Posted Tuesday, October 14, 2008 7:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926




Madhivanan

Failing to plan is Planning to fail
Post #585445
Posted Tuesday, October 14, 2008 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #585468
Posted Tuesday, October 14, 2008 8:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:30 PM
Points: 205, Visits: 1,344
"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.



Post #585479
Posted Tuesday, October 14, 2008 8:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 18, 2008 5:16 PM
Points: 3, Visits: 10
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?

Post #585480
Posted Tuesday, October 14, 2008 8:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 18, 2008 5:16 PM
Points: 3, Visits: 10
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 ????


Post #585489
Posted Thursday, October 16, 2008 8:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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
Post #587047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse