Export data from Microsoft SQL server to CSV file using stored procedure

Write following procedure and execute:-

But before that you need to enable xp_cmdshell
So you need to execute these for enable xp_cmdshell
/****************************/

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

/****************************/
 Then write procedure

Create PROC spExportToCSV

@FilePath varchar(100)

AS

BEGIN

DECLARE @SQL VARCHAR(500)

SET @SQL='SELECT * FROM [DB name].dbo.[Table Name]'

SET @SQL='SQLCMD -E -Q "SET NOCOUNT ON; '+@SQL+'" -o "'+@FilePath+'" -h-1 -s "," -W'

-- -h-1 for no header

-- if remove -h-1 then 1 header

--  -h1 then after every row there is header and so on

EXEC master..xp_cmdshell @SQL

END

Import Data from CSV file to Microsoft SQL Server 2008 using stored procedure

Write the following procedure in Microsoft SQL server and Enjoy....

--Exec spImportToCSV 'e:\input.csv'

CREATE PROC spImportToCSV

@FilePath varchar(100)

AS

BEGIN

CREATE TABLE #tmpCSV

(

      ID INT,

      FirstName VARCHAR(100)

)

DECLARE @Sql varchar(max);

SET @Sql= 'BULK INSERT #tmpCSV FROM '''+@FilePath+''' WITH(

FIRSTROW = 2,FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

print @Sql

EXEC (@Sql)

--FIRSTROW SET starting  ROW

--FIELDTERMINATOR SET field terminator

--ROWTERMINATOR SET row terminator

--Check the content of the table.

SELECT * FROM #tmpCSV

--Drop the table to clean up database.

DROP TABLE #tmpCSV

END

Populate Excel sheet from Dataset using VB.net

Two Functions for populating Excel sheet from a Dataset:

Public Sub populatesheetforRunners(ByVal dt As DataTable, ByVal worksheet As String, ByVal range1 As String, ByVal range2 As String, ByVal i As Integer)

        'Here dt is a datatable from the dataset, worksheet is the name of Active Work Sheet, range1 and range two is the range of Excel sheet like from A2 to Z65000, i is for starting column position

        Dim xlApp As Microsoft.Office.Interop.Excel.Application = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)

        Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook = CType(xlApp.ActiveWorkbook, Microsoft.Office.Interop.Excel.Workbook)

        Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim oRng As Microsoft.Office.Interop.Excel.Range

        Dim File As String = txtFilepath.Text

        'txtFilepath.Text is the Excel File path where the file is located

        xlWorkbook = xlApp.Workbooks.Open(File)

        xlWorksheet = xlWorkbook.Worksheets(worksheet)

        xlWorksheet.Activate()

        xlApp.Visible = True

        oRng = xlWorksheet.Range(range1, range2)

        oRng.Value = ""

        'Clearing the range of sheet. All the previous value will be removed

        Dim dc As New DataColumn

        Dim dr As DataRow

        Dim colIndex As Integer = 0

        Dim rowIndex As Integer = 0

        Try

            For Each dr In dt.Rows

                rowIndex = rowIndex + 1

                colIndex = 0 + (i * 2)

                For Each dc In dt.Columns

                    colIndex = colIndex + 1

                    xlApp.Cells(rowIndex + 3, colIndex) = dr(dc.ColumnName)

                Next

            Next

        Catch ex As Exception

        End Try

        'Data is writing by the loop to the sheet

        xlWorksheet.Cells.Select()

        xlWorksheet.Columns.AutoFit()

        xlWorksheet.Rows.AutoFit()

        Try

            xlApp.DisplayAlerts = False

            xlWorkbook.Save()

            xlApp.ActiveWorkbook.Close()

            xlApp.DisplayAlerts = True

            xlApp.Quit()

        Catch ex As Exception

        Finally

            ExcelCleanUp(xlApp, xlWorkbook, xlWorksheet)

            xlWorkbook = Nothing

            xlApp = Nothing

            dr = Nothing

            oRng = Nothing

            Dim process As System.Diagnostics.Process

            For Each process In System.Diagnostics.Process.GetProcessesByName("EXCEL")

                If (process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE")) Then

                    process.Kill()

                End If

            Next

        End Try

    End Sub

    Private Shared Sub ExcelCleanUp( _

    ByVal oXL As Microsoft.Office.Interop.Excel.Application, _

    ByVal oWB As Microsoft.Office.Interop.Excel.Workbook, _

    ByVal oSheet As Microsoft.Office.Interop.Excel.Worksheet)

        GC.Collect()

        GC.WaitForPendingFinalizers()

        Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL)

        Runtime.InteropServices.Marshal.FinalReleaseComObject(oSheet)

        Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB)

        oSheet = Nothing

        oWB = Nothing

        oXL = Nothing

    End Sub