从mysql表导出日期到.csv

本文关键字：日期 csv mysql | 更新日期: 2023-09-27 18:11:19

我试图将MySQL表导出为。csv，但有些东西不起作用，我找不到它是什么。我首先尝试将日期保存到DataTable中，然后使用以下函数将它们导出到.csv:

public void CreateCSVFile(DataTable dtDataTablesList, string strFilePath)
    {
        // Create the CSV file to which grid data will be exported.
        StreamWriter sw = new StreamWriter(strFilePath, false);
        //First we will write the headers.
        int iColCount = dtDataTablesList.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dtDataTablesList.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write("", "");
            }
        }
        sw.Write(sw.NewLine);
        // Now write all the rows.
        foreach (DataRow dr in dtDataTablesList.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount - 1)
                {
                    sw.Write("", "");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
    }

问题是:它停在sda.Fill(data)给出这个:

  • {MySql.Data.MySqlClient连接。MySqlConnection} MySql.Data.MySqlClient.MySqlConnection
  • sSql {MySql.Data.MySqlClient。MySqlCommand} MySql.Data.MySqlClient.MySqlCommand
  • sda {MySql.Data.MySqlClient。MySqlDataAdapter} MySql.Data.MySqlClient.MySqlDataAdapter
  • data {} System.Data.DataTable

脚本:

MySqlConnection connection = CDBAccess.GetCon;
MySqlCommand sSql = new MySqlCommand("SELECT * from mytable;", connection);
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = sSql;
DataTable data = new DataTable();
sda.Fill(data);
String path = @"C:'Users'Public'Documents'MyDocument";
CreateCSVFile(data, path);

从mysql表导出日期到.csv

试试下面的代码:

MySqlConnection = CDBAccess.GetCon;
string queryString = "SELECT * from mytable;";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet ds = new DataSet();
adapter.Fill(ds, "mytable");
DataTable data = ds.Tables[0];
String path = @"C:'Users'Public'Documents'MyDocument";
CreateCSVFile(data, path);
但是，一般来说，在Java代码中遍历实际表是不必要的，而且容易出错。相反，您应该利用MySQL公开的SELECT ... INTO OUTFILE结构。通过这种方式，您可以让MySQL完成将数据编组到CSV文件中的繁重工作。

只需从Java代码中执行以下原始查询，就可以了:

SELECT *
INTO OUTFILE 'C:/Users/Public/Documents/MyDocument/output.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ''n'
FROM mytable

好了，我找到问题了。首先，您需要通过使用MySqlDataAdapter填充数据表来将所有内容放入数据表。之后，您可以使用数据创建StringBuilder，并使用函数创建. csv日期。它看起来像这样:

MySqlDataAdapter sda = new MySqlDataAdapter();
                    sda.SelectCommand = cmdDataBase;
                    DataTable data = new DataTable();
                    sda.Fill(data);
                    BindingSource aSource = new BindingSource();
                    aSource.DataSource = data;
                    dataGridView1.DataSource = aSource;
                    sda.Update(data);
                    StringBuilder sb = new StringBuilder();
                    string[] columnNames = data.Columns.Cast<DataColumn>().
                                                      Select(column => column.ColumnName).
                                                      ToArray();
                    sb.AppendLine(string.Join(",", columnNames));
                    foreach (DataRow row in data.Rows)
                    {
                        string[] fields = row.ItemArray.Select(field => field.ToString()).
                                                        ToArray();
                        sb.AppendLine(string.Join(",", fields));
                    }
                    File.WriteAllText("test.csv", sb.ToString());

谢谢你的帮助!

这段代码可以帮助人们从mysql表中生成一个tab分隔的字符串，并显示或下载。

        <div >
               <div style="float:left; margin-left: 20px"> Table to download
                <asp:DropDownList ID="ddlTable" runat="server">
                     <asp:ListItem Value="components">components</asp:ListItem>
                </asp:DropDownList></div>
             <div style="float:left;  margin-left: 20px; margin-top: 5px"><asp:Button ID="btnShow" class="button-main primary-btn"  runat="server" Visible="true" Text="Show" OnClick="btnShow_Click" /></div>
             <div style="float:left;  margin-left: 20px; margin-top: 5px"><asp:Button ID="btnDownload" class="button-main primary-btn"  runat="server" Visible="true" Text="Download" OnClick="btnDownload_Click" /></div>
        </div>
        <div style="clear: both"></div>
        <div runat="server" id="divOutput"></div>

        protected string generate()
        {
            string sql = "";
            string row = "";
            string datatable = "";
            int cols = 0;
            string hdr = "";

            if (ddlTable.SelectedValue == "components")
            {
                sql = "SELECT * from components";
                hdr = "id,category_id,product_code,supplier,description,price,diameter,extra_info'r'n";
            }
            datatable = hdr.Replace(',',''t');
            using (var connection = DBController.CreateOpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        cols = dataReader.FieldCount;
                        row = "";
                        for (int i = 0; i < cols; i++)
                        {
                            if (i > 0) row += "'t"; // <TAB> separated
                            if (dataReader[i] != null)
                            {
                                row = row + dataReader[i].ToString();
                            }
                            else
                            {
                                row = row + "NULL";
                            }
                        }
                        row = row + "'r'n";
                        datatable = datatable + row;
                    }
                }
            }
            return datatable;
        }
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string data = generate();
            string fileName = String.Format("{0}-{1}.csv", ddlTable.SelectedValue, DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
            Response.Clear();
            Response.ContentType = "text/csv";
            Response.AddHeader("content-disposition", "filename=" + fileName);
            // write string data to Response.OutputStream here
            Response.Write(data);
            Response.End();
        }
        protected void btnShow_Click(object sender, EventArgs e)
        {
            string data = generate();
            divOutput.InnerHtml = "<pre>" +data + "</pre>";
        }
相关文章：