SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing from Excel to SQL via VB.NET


Importing from Excel to SQL via VB.NET

Author
Message
msaidi2
msaidi2
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61453 Visits: 19097
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
My Blog: www.voiceofthedba.com
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12099 Visits: 18567
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?
Gosta Munktell
Gosta Munktell
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 2161
"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.
Madhivanan-208264
Madhivanan-208264
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 476
Refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926



Madhivanan

Failing to plan is Planning to fail
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23261 Visits: 9730
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
Gosta Munktell
Gosta Munktell
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 2161
"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.
msaidi2
msaidi2
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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?
msaidi2
msaidi2
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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 ????
Madhivanan-208264
Madhivanan-208264
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 476
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search