loading .CSV file

Question

loading .CSV file

Viewing 4 posts - 16 through 19 (of 19 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

karthikeyan (9/29/2008)
Assume the .CSV file format is
1 karthik
2 keyan

I am not sure how accurate you intend this test to be, however, you should be aware the "CSV" stand for "Comma-Separated Values", so you should use commas rahter than spaces, like so:

1,karthik

2,keyan

This command:

exec master..xp_cmdshell "bcp acs..bcpt in "C:\temp\Book1.csv -c -Upf -Ppf1 -Sdev"

indicates that you want to connect to a SQL Server/Instance named "dev". Make sure that that is the correct name for you SQL Server Instance or that you have an alias of that name correctly setup.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

create proc bcp_test

as

begin

exec master..xp_cmdshell "bcp acs..bcpt in "C:\temp\Book1.csv -c -Upf -Ppf1 -Sdev"

end

Actually, the use of quotes does not look right here either. That command line should look more like this:

exec master..xp_cmdshell 'bcp acs..bcpt in "C:\temp\Book1.csv" -c -Upf -Ppf1 -Sdev'

And you should only need the inner quotes if your file name & path have any spaces in them.

[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]

karthik M One Orange Chip Points: 29139 More actions · Answer 3

rbarryyoung ,

Thanks for pointing the errors.

But i had a discussion with my manager today.He said that the application will be executed via webserver. so we may not sure that has sqlserver installed. If not installed, it is a very big process to install sqlserver on webserver. Because management wont allow to install anything on webserver.

So our BCP should fail, if they execute the application from the webserver. Am i correct ?

Also i asked the number of rows that CSV file contains. It may be in hundreds or thousands.

I suggested one method.

" Generating INSERT statement dynamically". Will it work out ? But i also added we need to look the performance.

Suggestions are welcome !

Also What could be be standard and generic solution to this kind of problem ?

karthik

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Are you saying that they may not have SQL Server on the Web Server where the client code is executing, but you need to load the CSV file into a SQL Server somewhere else?

If so then,

A) You can use BCP.exe remotely, however. I do not know if the license allows you to use it on a server without SQL Server installed.

and

B) I have already given you the solution to this problem: use SQLBulkCopy from client code. This is easy for your programmers to write and there are already half a dozen examples out there. Here is a another one that you can look at: (it is Open Source License, I am not sure what this means for your customer distribution issues): http://www.codeproject.com/KB/database/TransferUsingSQLBulkCopy.aspx

[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]