• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 961
  • Last Modified:

bcp utility to export tables?

Hi,

data Connection:  internal.mdf
table: locations

I've got a .mdf in an application built in VB.Net attached as a Data Connection in Visual Studio 2008

I'm trying to export a table or all the tables as a CSV files or a file that can be easily read by Access or excel.

can I use the bcp utility if so how? I'm stuck. Can I store it as a SPROC and then call it from a button click? Or can I can I just use it in VB in a button click?

Any help much appreciated,
0
davecocks
Asked:
davecocks
  • 6
  • 5
1 Solution
 
pivarCommented:
Hi,

You can use bcp to create a csv file. The param -t specifies fieldterminator.

bcp dbname.schemaname.tablename out filename -Sservername -Uusername -Ppassword -c -t,

/peter
0
 
davecocksAuthor Commented:
hi Pivar,

Thanks for your reply

So do I run this in a SPROC? Do I need to refer to anything in the Servername / username / password if the .mdf isn't on a server i.e. is a local database

Thanks


ALTER PROCEEDURE dbo.exportCSV
 
AS
 
BCP internal.mdf.locations OUT C:\\locations.csv -Sservername -Uusername -Ppassword -c -t,
 
RETURN

Open in new window

0
 
pivarCommented:
No, bcp is an application. If you want to run it in a sproc you have to use

EXEC master.dbo.xp_cmdshell 'bcp dbname.schemaname.tablename out filename -Sservername -Uusername -Ppassword -c -t,'

But there are some securityimplication with xp_cmdshell, it may be disabled by admin.

By the way, there are more formatting possibilites with bcp see http://msdn.microsoft.com/en-us/library/ms162802.aspx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
davecocksAuthor Commented:
ok, cool,

whats the schema name?

I found this on a related link:
'A schema is a container that holds tables, views, procedures, and so on'

I'm not sure if I'm using one. Where do I find it?

ALTER PROCEEDURE dbo.exportCSV
 
AS
 
EXEC master.dbo.xp_cmdshell BCP internal.?? .locations OUT C:\\locations.csv -Sservername -Uusername -Ppassword -c -t,
 
RETURN

Open in new window

0
 
pivarCommented:
Standard schemaname would be dbo, but you can omit it. The syntax should look like this:

ALTER PROCEEDURE dbo.exportCSV
AS
  EXEC master.dbo.xp_cmdshell 'bcp dbname.dbo.tablename out filename -Sservername -Uusername -Ppassword -c -t,'
RETURN

Or
  EXEC master.dbo.xp_cmdshell 'bcp dbname..tablename out filename -Sservername -Uusername -Ppassword -c -t,'

0
 
pivarCommented:
A typo

Should be

ALTER PROCEDURE dbo.exportCSV
0
 
davecocksAuthor Commented:
Hi Pivar, thanks loads for your help.

I'm executing the SPROC but not seeing a file. Where am I going wrong?


ALTER PROCEEDURE dbo.exportCSV
AS
  EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out locations.csv -Sservername -Uusername -Ppassword -c -t,'
RETURN
0
 
pivarCommented:
You probably have to specify which path you want to save the file to. I suppose you changed the server/user/pw info as well?

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -Sservername -Uusername -Ppassword -c -t,'

You can also redirect error and output

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -Sservername -Uusername -Ppassword -c -t, -ec:\temp\errors.txt
-oc:\temp\output.txt'
0
 
davecocksAuthor Commented:
Hi Pivar,

Sorry, I'm still new to Visual Studio and SQL server, so appologies for having to walk me through this.

The database is local i.e. not on a server.

What would you put there in this instance. Some of the examples on the link you posted don't make reference to -Sservername -Uusername -Ppassword.

Thanks again, your times really appreciated.

Removing them doesn't work:

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -c -t, -ec:\temp\errors.txt
-oc:\temp\output.txt'
0
 
pivarCommented:
No, you must assign values to them, not remove them. But if it's a local server you could use a dot as servername and if you use windows authentication you could use "-T" instead of  "-Uusername -Ppassword".

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -S. -T -c -t,'

0
 
davecocksAuthor Commented:
Hi Pivar thanks for your help. I think its working now but the admin issues you mentioned have come back to haunt me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now