using BCP to make a csv & saving it to fliestream

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

using BCP to make a csv & saving it to fliestream

Author

Topic

spearmint_man
Starting Member

5 Posts

Posted - 2011-10-17 : 08:18:47

My 1st Post !

Hi - I am looking to create a csv file from data in the db using BCP and then injecting it back into the DB as a row using Filestream. I wanted to know if anyone had any views on this.

To give you an idea of my crazy plans ...I want an archive of csv files executed by users that the system stores for them with info like size of file, date run etc

My thought are to
1) create the sproc and export data via BCP to a location on the db e.g. c:\csv\my_extract.csv
2) import the csv backto the DB using Filestream etc..
3) delete / manage the old / remaining csv file.

However I am wondering if this is a bit .... faffy? Is there a way to pipe it via BCP straight into a row as a csv file ?

Or does it need to be done as above ? answers most welcome !

Spearmint_Man

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 08:31:35

Is there a way to pipe it via BCP straight into a row as a csv file ?
you mean directly put in csv format into a column? if yes its possible.

show some sample data and explain what you want and we will show you sample query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spearmint_man
Starting Member

5 Posts

Posted - 2011-10-17 : 08:39:27

ok .... so the csv extract would be something like ...

declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out c:\bcp\sysobjects.txt -c -t"| ^" -r"0x0D0A" -T -S'
+ @@servername
exec master..xp_cmdshell @sql

adding the file from a specified location on the server I have not got to hand .... but its only a one liner. Im interested tio know what is the correct method in doing this ..... sql is greatful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 09:09:36

so you want to drop to a file first or directly pump it to db column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spearmint_man
Starting Member

5 Posts

Posted - 2011-10-17 : 09:34:52

my approach is to drop the file onto the server and then insert it into the table.

However I wanted to know if it is either better to do it all in one go : and if it is then how via BCP do you insert it straight into the table row, rather than ...
1) create csv
2) insert csv
3) finally delete csv file held on c:\bcp\mycsv.csv etc..

Its ok to do it this way - I was just curious if it was possible to create the csv file and insert it all in one go.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:32:51

for putting it straight to table row you don't need bcp.
you can use for xml path to generate csv.
if you can post the column info we will be able to help you out with query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spearmint_man
Starting Member

5 Posts

Posted - 2011-10-17 : 10:49:58

The data for the csv file is generated via a Sproc which pivots the data dynamically. My intention is to run this sproc to generate the csv file, the sproc works fine!! The sproc is fairly huge and is not needed for this example. I have not created the table it is to go into so I am open to a generic response. But if you need this then lets pretend the table is as follows.

Table : FormDataExtract
Ref_FormDataExtract varchar(32),
ExtractName,
actual_data varbinary(max),
FileSize int,
Added_by varchar(32),
Added_dt samlldatetime,
Modified_By varchar(32),
Modified_dt samlldatetime,
Deleted_By varchar(32),
Deleted_dt samlldatetime

So now I want to run my sproc (lets call it ... Exec DataExtract_1)and generate a csv file which will sit in the col(actual_data).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:51:49

and it will be csv of all the field values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spearmint_man
Starting Member

5 Posts

Posted - 2011-10-17 : 11:06:33

im sorry i am not getting what you mean ....

1) a sproc executes and creates an unknown number of cols and rows from a data extract.
2) the sproc results get turned into a csv file.
3) the csv file gets put into a varbinary with filestream onit.

is there a better way of doing it other than create csv via BCP, then import file etc etc

End of ...ze bang, finit voila etc etc etc....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:12:06

put the resultset of sp into a temporary table. (say #FormDataExtract)
the use below query to turn it to csv and put it into table column


insert table (actual_data)
select coalesce(Ref_FormDataExtract,'') +
coalesce(ExtractName,'') +
coalesce(actual_data,'') +
coalesce(FileSize,'') +
coalesce(Added_by,'') +
coalesce(Added_dt,'') +
coalesce(Modified_By,'') +
coalesce(Modified_dt,'') +
coalesce(Deleted_By,'') +
coalesce(Deleted_dt,'')
from #FormDataExtract

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources