Export CSV file from SQL Server from script.

 This is a quick way to export a SQL  View or Table to a CSV file

The file can only be exported to a folder on the local SQL Server where it has run.

First all you data must be in one view or table. I use a Temp table to do the Job.

Once you have your data in one place:-

Declare @sql as varchar(8000). @outputpath  as varchar(3000)

set @outputpath  ='c:\temp\testcsv.csv'

select @sql = 'bcp "select  * from Yourdb..Temptable " queryout ' + @outputpath + ' -c -t, -T -S '  + @@servername 

exec master..xp_cmdshell @sql

 EXECUTE AS login = 'user'

and there you have it, the @sql needs to be a large Varchar for this to run or it fails. @otputpath need to be on  the local sql server

Export Select Query To Delimited File

The following code  will output the results of the query  (select * from  logic..temp1) to a CSV file

Please be aware that the output path will be to the sql machine if you use c:\.  Use \\servername\folder\filename.extention  to output the file to another machine.




declare @outputpath varchar(800) ----------Varchar(max) is not allowed
declare @sql varchar(8000)


set @outputpath = '\\SERVER\Shared_Drive\nick.csv'   --------- Set the Path


select @sql = 'bcp "select * from  logic..temp1"  queryout ' + @outputpath + ' -c -t, -T -S'  + @@servername

print @sql

exec master..xp_cmdshell @sql



the file will then appear in the appropriate folder.
To change the delimiter change the yellow Highlighted Character. for a pipe you will have to replace it with ^|  so the line looks like this ' -c -t^| -T -S'

Truncate a field in VB Before Entry

 

If you do a lot of importing of files and do not have control over what the user puts in i.e. long names passed the field width of the DB or Textbox, This could be imports from a CSV or spreadsheet then try validating the field length.
         The following VB Function takes the string and the field length truncates the string and returns it.

 Private Function truncate(mystring As String, mylength As Int16)


        If mystring.Length >= mylength - 1 Then
            mystring = mystring.Substring(0, mylength - 1)
        End If

        Return mystring
    End Function