locked
Creating CSV file from Table RRS feed

  • 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

    Thursday, October 1, 2015 12:31 PM

Answers

  • 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.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 2, 2015 5:19 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 6, 2015 1:23 AM

All replies

  • 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

    Thursday, October 1, 2015 1:48 PM
  • User1210767569 posted

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

    Friday, October 2, 2015 4:32 AM
  • 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

    Friday, October 2, 2015 4:56 AM
  • 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.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 2, 2015 5:19 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 6, 2015 1:23 AM