September 26, 2008 at 7:04 am
All,
There is a new task which my team has to complete it by next week. the task is UI has to upload the .csv file into the DB table. UI has the option to choose the .CSV file,maximum files in the list box is 10. User may choose any number of file any time. This is our task.
one of my co-worker sugested to use the SP. But i said there is some problem when we use SP to do this. Say for example, if the file is having 10000 rows, we need to call our SP 10000 times. The key question is 'how we need to insert those 10000 rows into the DB ? '
what are the possible ways to do it ?
Inputs are highly appreciable !
karthik
September 26, 2008 at 7:08 am
Lookup Bulk Insert an bcp in Books On Line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 7:17 am
Jack,
Actually, in UI there is one list box. It containts the name of the file, once user choose a filename from the list box, those file has t o be uploaded into the corresponding table.
does BULK INSERT work out here ?
karthik
September 26, 2008 at 7:27 am
I'm pretty sure it can.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 7:32 am
BULK INSERT works on files that the server can see. If the file(s) being chosen by the user are on a client system, BULK INSERT probably will not work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 7:32 am
Can you tell me how it can be done ? Acutally i am not aware of .NET side, so if you give me idea, i will start my work.
karthik
September 26, 2008 at 7:32 am
rbarryyoung,
can you share your ideas here ?
karthik
September 26, 2008 at 7:35 am
will writetext work out here ? I may wrong...But just i was thinking...
karthik
September 26, 2008 at 7:37 am
I believe that SQLBulkCopy is the class that you want to use from ADO.net. I have never used it myself, but hopefully, someone else here has.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 7:45 am
The article here: (http://www.eggheadcafe.com/articles/20060318.asp) has a code example of how to use SQLBulkCopy from ADO.net to load a CSV file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 7:46 am
rbarryyoung,
Thanks for your idea ! I will ask .NET guys. Meantime i read the following URL.
http://www.sqlservercentral.com/Forums/Topic380386-61-4.aspx
But as you said, It won't work out from UI side. Am i correct ? Or shall we ?
Please correct me if a wrong.
karthik
September 26, 2008 at 7:57 am
This is exactly what I said before, Karthik. BULK INSERT will work only if the Server can see the file. If the Server can not see the file, then BULK INSERT cannot import it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 8:12 am
I visited the following URL also.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23669048.html
I have seen the below code
Insert into TableA
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Temp\;Extended properties=Text')...FileName#csv
But i asked my team to see the below code
then they said the following things.
"we can't save anything onto the web server's file system
our issue is this
when the asp:FileUpload control receives a file, for instance a .csv file, it doesn't exist as a "File"
it exists as a ByteStream
I have the option of reading it one character at a time or one line at a time "
karthik
September 26, 2008 at 8:28 am
Then they should use SQLBulkCopy, and implement their own IDataReader class around their input ByteStream to provide the input. IDataReader looks pretty easy to implement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 29, 2008 at 2:34 am
I asked UI team to try the above suggestions.
Meantime i did the below R&D.
--------------------------------------
I have written the below code.
create table bcpt
(
eno int,
ename varchar(255)
)
I have to upload the data from .CSV file.
Assume the .CSV file format is
1 karthik
2 keyan
I wrote the below sqlcode.
create proc bcp_test
as
begin
exec master..xp_cmdshell "bcp acs..bcpt in "C:\temp\Book1.csv -c -Upf -Ppf1 -Sdev"
end
While executing the above procedure, i got the below error message.
-----------------------------------------
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
Establishing connection failed.
-----------------------------------------
-------------------------------------------------------------------------------------------
Anybody help me out to sort the error message.
karthik
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply