将值从sql数据库映射到.csv文件. [英] Mapping values from a sql database to a .csv file.

问题描述

我有一个应用程序，可以将.xls或.xlsx格式的文件转换为.csv文件.初始文件的策略编号与我公司使用的策略编号有些不同.因此，我需要查询一个sql数据库，并获取与.xls文件中的原始文件匹配的策略号，但以我的公司格式，以便可以将其写入.csv文件.

当我尝试加载从SQL数据库将我的实际策略号到我的数据表(dt1)的SqlDataReader(reader)时，它似乎没有任何内容，并且随后未写入该文件.

有人可以看看我的代码并为我指出正确的方向吗?谢谢！

I have an application that takes files in one format, .xls or .xlsx and converts them into .csv files. The initial file has a policy number that is a bit different than the one my company uses. So I need to query a sql database and get the policy number that matches the original in the .xls file but in my companies format so I can write that to the .csv file.

When I try loading my SqlDataReader(reader) that is getting my actual policy number from the sql database into my datatable (dt1), it does not appear to have anything in it and subsequently does not write to the file.

Can someone take a look at my code and point me in the right direction. Thank you!

if (file.Exists)
                {
                    //The connection string to the excel file
                    string connstr = @"Provider=Microsoft.Ace.Oledb.12.0;Data Source=C:\travelersTEST\travelersTEST.xlsx;" + "Extended Properties=" + "\"" + "Excel 12.0;" + "\""; //C:\inetpub\temp\travelersTEST.xlsx
                    //The connection to that file
                    OleDbConnection conn = new OleDbConnection(connstr);
                    //The query
                    string strSQL = "SELECT [Policy Number], [Comm Due], [Effective Date], [Comm Rate], [Transaction Premium] FROM [Sheet1$]";
                    //The command 
                    OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                    DataTable dt = new DataTable();
                    conn.Open();
                    string date1 = DateTime.Now.ToString("yyyy-MM-dd");
                    string date2 = DateTime.Now.ToString("hh-mm-ss");
                    try
                    {
                        OleDbDataReader dr1 = cmd.ExecuteReader();
                        StreamWriter sw = new StreamWriter(@"C:\travelersTEST\Travelers - " + date1 + " @ " + date2 + ".csv");
                        if (dr1.HasRows)
                        {
                            dt.Load(dr1);
                            dt.AcceptChanges();
                        }


----------------------------The code I need help with is below-----------------------------------------------------------

                        // Now write all the rows.
                        sw.Write("POLICY NUMBER, Commission Amount, POLICY Effective Date, COMMISSION RATE, Premium");
                        sw.Write(sw.NewLine);
                        SqlConnection myConn = new SqlConnection(@"Data Source=lrstest-sql1;Initial Catalog=VelocityReports;Integrated Security=SSPI");
                        myConn.Open();
                        SqlCommand myCommand = myConn.CreateCommand();
                        
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr[0].ToString() != "")
                            {
                                string polNumber = dr[0].ToString();
                                int index = polNumber.IndexOf(" ");
                                if (index > 0)
                                {
                                    polNumber = polNumber.Substring(0, index);
                                }
                                myCommand.CommandText = "Select polPolicyNumber From tblPolicies Where polPayee = 543 and polPolicyNumber like " +
                                    "''IOUB" + polNumber + "''and polEffectiveDate =" + dr[2].ToString() + " Order by polEffectiveDate desc";
                                myCommand.CommandType = CommandType.Text;
                                SqlDataReader reader = myCommand.ExecuteReader();
                                DataTable dt1 = new DataTable();
                                dt1.Load(reader);
                                foreach (DataRow dr2 in dt1.Rows)
                                {
                                    sw.Write(dr2[0].ToString() + ",");
                                }
                                sw.Write(dr[1].ToString() + ",");
                                sw.Write(dr[2].ToString() + ",");
                                sw.Write(dr[3].ToString() + ",");
                                sw.Write(dr[4].ToString());
                                sw.Write(sw.NewLine);
                                reader.Close();
                            }
                         }
                        
                        myConn.Close();
                        sw.Close();

推荐答案

; //命令 OleDbCommand cmd =新的OleDbCommand(strSQL，conn); DataTable dt = new DataTable(); conn.Open(); 字符串date1 = DateTime.Now.ToString("yyyy-MM-dd"); 字符串date2 = DateTime.Now.ToString("hh-mm-ss"); 尝试 { OleDbDataReader dr1 = cmd.ExecuteReader(); StreamWriter sw =新的StreamWriter(@"C:\ travelersTEST \ Travelers-" + date1 +"@" + date2 +".csv")； 如果(dr1.HasRows) { dt.Load(dr1); dt.AcceptChanges(); } 我需要帮助的代码如下--------------------------------------------- //现在写所有行. sw.Write(政策编号，佣金金额，政策生效日期，佣金率，溢价")； sw.Write(sw.NewLine); SqlConnection myConn =新的SqlConnection(@数据源= lrstest-sql1；初始目录= VelocityReports；集成安全性= SSPI")； myConn.Open(); SqlCommand myCommand = myConn.CreateCommand(); foreach(dt.rows中的DataRow dr) { 如果(dr [0] .ToString()！=") { 字符串polNumber = dr [0] .ToString(); int index = polNumber.IndexOf("); 如果(索引> 0) { polNumber = polNumber.Substring(0，index); } myCommand.CommandText =从tblPolicies中选择polPolicyNumber，其中polPayee = 543和polPolicyNumber如" + " IOUB"+ polNumber +"和polEffectiveDate =" + dr [2] .ToString()+"polEffectiveDate desc的顺序"; myCommand.CommandType = CommandType.Text; SqlDataReader reader = myCommand.ExecuteReader(); DataTable dt1 = new DataTable(); dt1.Load(阅读器); foreach(dt1.Rows中的DataRow dr2) { sw.Write(dr2 [0] .ToString()+，"); } sw.Write(dr [1] .ToString()+，"); sw.Write(dr [2] .ToString()+，"); sw.Write(dr [3] .ToString()+，"); sw.Write(dr [4] .ToString()); sw.Write(sw.NewLine); reader.Close(); } } myConn.Close(); sw.Close();

"; //The command OleDbCommand cmd = new OleDbCommand(strSQL, conn); DataTable dt = new DataTable(); conn.Open(); string date1 = DateTime.Now.ToString("yyyy-MM-dd"); string date2 = DateTime.Now.ToString("hh-mm-ss"); try { OleDbDataReader dr1 = cmd.ExecuteReader(); StreamWriter sw = new StreamWriter(@"C:\travelersTEST\Travelers - " + date1 + " @ " + date2 + ".csv"); if (dr1.HasRows) { dt.Load(dr1); dt.AcceptChanges(); } ----------------------------The code I need help with is below----------------------------------------------------------- // Now write all the rows. sw.Write("POLICY NUMBER, Commission Amount, POLICY Effective Date, COMMISSION RATE, Premium"); sw.Write(sw.NewLine); SqlConnection myConn = new SqlConnection(@"Data Source=lrstest-sql1;Initial Catalog=VelocityReports;Integrated Security=SSPI"); myConn.Open(); SqlCommand myCommand = myConn.CreateCommand(); foreach (DataRow dr in dt.Rows) { if (dr[0].ToString() != "") { string polNumber = dr[0].ToString(); int index = polNumber.IndexOf(" "); if (index > 0) { polNumber = polNumber.Substring(0, index); } myCommand.CommandText = "Select polPolicyNumber From tblPolicies Where polPayee = 543 and polPolicyNumber like " + "''IOUB" + polNumber + "''and polEffectiveDate =" + dr[2].ToString() + " Order by polEffectiveDate desc"; myCommand.CommandType = CommandType.Text; SqlDataReader reader = myCommand.ExecuteReader(); DataTable dt1 = new DataTable(); dt1.Load(reader); foreach (DataRow dr2 in dt1.Rows) { sw.Write(dr2[0].ToString() + ","); } sw.Write(dr[1].ToString() + ","); sw.Write(dr[2].ToString() + ","); sw.Write(dr[3].ToString() + ","); sw.Write(dr[4].ToString()); sw.Write(sw.NewLine); reader.Close(); } } myConn.Close(); sw.Close();

我的解决方案是将Select语句更改为可接受的格式.

My solution was to alter my Select statement to an acceptable format.

这篇关于将值从sql数据库映射到.csv文件.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！