Creating CSV file from Table

Question

User1210767569 posted

Issue:

Code makes, what in theory is a compact way to get all the column names from a table, (it's a temp table created by stored procedure) and then push the data into a CSV. However it's not liking "dsNames.Columns" or "dsNames.Rows" and I'm not sure what I should use instead. I've tried Datatow and ds and sb just encase!

Code:

 Public Sub PrcCreateCSV(vDateString As String)

        Dim sConnString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("SQL").ConnectionString
        Dim dsNames As SqlDataSource
        dsNames = New SqlDataSource
        dsNames.ConnectionString = sConnString
        Dim sSQL As String

        sSQL = "SELECT * FROM '" & vDateString & "" '<-- Don't worry it will be amended to the columns!
        dsNames.SelectCommand = sSQL

        Dim sb As New StringBuilder()

        Dim columnNames As IEnumerable(Of String) = dsNames.Columns.Cast(Of DataColumn)().[Select](Function(column) column.ColumnName)
        sb.AppendLine(String.Join(",", columnNames))

        For Each row As DataRow In dsNames.Rows
            Dim fields As IEnumerable(Of String) = row.ItemArray.[Select](Function(field) field.ToString())
            sb.AppendLine(String.Join(",", fields))
        Next

        File.WriteAllText("C:\Sites\Website\www\Documents\test.csv", sb.ToString())

    End Sub

Answer 1

User1428246847 posted

Below is C# code (I don't do VB, sorry)

// dataset to hold result
DataSet ds = new DataSet();

// connectionstring
string connectionstring = "Data Source=WimS-LP;Initial Catalog=DemoDatabase;User ID=DemoUser;Password=DemoUser";
// execute SQL query
using (SqlConnection conn = new SqlConnection(connectionstring))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType= CommandType.Text;
        cmd.CommandText = "select * from Table1";

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
    }
}

The dataset ds contains datatables returned by the query. Next you can analyse the column names of the datatable(s).

I hope that it (although C#) helps you a little.

// Edit: you can research all from the above on the web; e.g. msdn VB SqlCommand

Answer 2

User1210767569 posted

Thanks, when I ran it I only got 1 line of data, but there should be 4,000 :-(

Answer 3

User1508394307 posted

However it's not liking "dsNames.Columns" or "dsNames.Rows"

IEnumerable(Of String)... etc is a linq syntax, do you have all required namespaces imported?

Imports System.Linq

Answer 4

User1428246847 posted

Thanks, when I ran it I only got 1 line of data, but there should be 4,000 :-(

If I run above query, it returns one datatable in the dataset and 3 rows in that datatable; obviously based on the data that I have in the sql table. I see two possible issues

1. I did not explain properly and you're looking at the dataset instead of at the datatable.
2. Your query is wrong.

Answer 5

User503812343 posted

Below is your datatable

DataTable dtProducts = new DataTable();
    dtProducts.Columns.Add("ProductID", typeof(int));
    dtProducts.Columns.Add("ProductName", typeof(string));
    dtProducts.Columns.Add("SupplierID", typeof(int));
    dtProducts.Columns.Add("CategoryID", typeof(int));
    dtProducts.Columns.Add("QuantityPerUnit", typeof(string));
    dtProducts.Columns.Add("UnitPrice", typeof(decimal));
    dtProducts.Columns.Add("UnitsInStock", typeof(int));
    dtProducts.Columns.Add("UnitsOnOrder", typeof(int));
    dtProducts.Columns.Add("ReorderLevel", typeof(Int16));
    dtProducts.Columns.Add("Discontinued", typeof(bool));

    dtProducts.Rows.Add(1, "Chai", 1, 1, 
            "10 boxes x 20 bags", 18.00, 39, 0, 10, 0);
    dtProducts.Rows.Add(2, "Chang", 1, 1, 
            "24 - 12 oz bottles", 19.00, 17, 40, 25, 0);
    dtProducts.Rows.Add(3, "Aniseed Syrup", 1, 2, 
            "12 - 550 ml bottles", 10.00, 13, 70, 25, 0);
    dtProducts.Rows.Add(4, "Chef Anton's Cajun Seasoning", 2, 2, 
            "48 - 6 oz jars", 22.00, 53, 0, 0, 0);
    dtProducts.Rows.Add(5, "Chef Anton's Gumbo Mix", 2, 2, 
            "36 boxes", 21.35, 0, 0, 0, 1);
    dtProducts.Rows.Add(6, "Grandma's Boysenberry Spread", 3, 2, 
            "12 - 8 oz jars", 25.00, 120, 0, 25, 0);
    dtProducts.Rows.Add(7,"Uncle Bob's Organic Dried Pears",3,7, 
            "12 - 1 lb pkgs.", 30.00, 15, 0, 10, 0);
    dtProducts.Rows.Add(8, "Northwoods Cranberry Sauce", 3, 2, 
            "12 - 12 oz jars", 40.00, 6, 0, 0, 0);
    dtProducts.Rows.Add(9, "Mishi Kobe Niku", 4, 6, 
            "18 - 500 g pkgs.", 97.00, 29, 0, 0, 1);
    dtProducts.Rows.Add(10, "Ikura", 4, 8, 
            "12 - 200 ml jars", 31.00, 31, 0, 0, 0);

and below code is used to convert it to CSV

public static void ToCSV(DataTable dtDataTable, string strFilePath)
{
    StreamWriter sw = new StreamWriter(strFilePath, false);
    //headers  
    for (int i = 0; i < dtDataTable.Columns.Count; i++)
    {
        sw.Write(dtDataTable.Columns[i]);
        if (i < dtDataTable.Columns.Count - 1)
        {
            sw.Write(",");
        }
    }
    sw.Write(sw.NewLine);
    foreach (DataRow dr in dtDataTable.Rows)
    {
        for (int i = 0; i < dtDataTable.Columns.Count; i++)
        {
            if (!Convert.IsDBNull(dr[i]))
            {
                string value = dr[i].ToString();
                if (value.Contains(','))
                {
                    value = String.Format("\"{0}\"", value);
                    sw.Write(value);
                }
                else
                {
                    sw.Write(dr[i].ToString());
                }
            }
            if (i < dtDataTable.Columns.Count - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);
    }
    sw.Close();
}        

for more details see  converting datatable values to CSV