Store one .xls column as .txt file using C#

Question

I want to save one column from my .xls file into .txt file using C#. The column of .xls file contain the City name of a country. So far What I did, I extract the city name which contain the population upper than 20000. Now I want to store only the City name not the population with newline order in a text file. But When I try to save it stores as blank text file. Here is my code to extract data from. xls file.

public class City
{

    public DataTable DataService()
    {
        var data = new DataTable();

        var startPath = Application.StartupPath;
        string folderName = Path.Combine(startPath, "CityList");
        System.IO.Directory.CreateDirectory(folderName);
        string SavedfileName = "POI_list.txt";
        var Saving_path = Path.Combine(folderName, SavedfileName);

        string fileName = "Zensus_Gemeinden_org.xlsx";
        var path = Path.Combine(startPath, fileName);

        String name = "Gemeinden_31.12.2011_Vergleich";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                       path + ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select [3] as City,[4] as Population, * From [" + name + "$D7:E11300] Where [4] > 20000", con);
        //con.Open();

        OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
        sda.Fill(data);

        //string Place_Json = "Place_List:" + JsonConvert.SerializeObject(data, Formatting.Indented);
        File.WriteAllText(Saving_path, data.ToString()); // it stores as blank text file
        return data;
    }
}

Modify: Now I am trying in this way. But there shows error athat sb does not exist in the current folder

 sda.Fill(data);

                foreach(DataRow r in data.Rows)
                {
                    string output= sb.append(r["City"] + "\n");
                    File.WriteAllText(Saving_path, data);
                }

New Modification:

var sb = new StringBuilder();
                using (DataTable dt = new DataTable())
                {
                  sda.Fill(dt);
                    foreach(DataRow r in dt.Rows)
                    {
                        sb.Appent(r["City"] + "\n"); 

                    }
                    File.WriteAllText(Saving_path,sb.ToString());
                    return dt;

                }

I got My .txt file in this way

Flensburg Kiel Lübeck Neumünster Heide Geesthacht.....

@MikeMiller I updated.But shows error for append. Please see the new update in the question — harry.luson, Feb 24 '16 at 10:46
@MikeMiller. I corrected the Append error. Now what I got every city in one line not in new line order. So sb.ToSting is not working here. — harry.luson, Feb 24 '16 at 10:52
you might need \r\n if you mean a Windows Newline not a NewLine. http://stackoverflow.com/questions/15433188/r-n-r-n-what-is-the-difference-between-them — Mike Miller, Feb 24 '16 at 10:53

Mike Miller · Accepted Answer · 2016-02-24T10:37:27.540

2

So you need to fill a data table with your data first,

var sb = new StringBuilder();
using (DataTable dt = new DataTable())
{
  sda.Fill(dt);
  ..loop here
}

Then your loop needs to loop through the rows

foreach(DataRow r in dt.Rows)
{      
  ..append data to text here
}

Then you need to use a stringbuilder to build the string in the append bit, and then write that to a file. That would look something like

sb.append(r["City"] + "\n");

edited Feb 24 '16 at 10:37

answered Feb 24 '16 at 10:24

Mike Miller

16,195
1
20
27

I have a question that Shoud I implement using (Datatable ....at the begining of my code. Because at the begining I already mention Var data=new Datatable – harry.luson Feb 24 '16 at 10:31
TBH - i would use using around all your data access, to ensure the scope is as narrow as possible and the objects are disposed of in a timely fashion. In this instance yes you can, but in future it might be worth reading http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx who shows a good pattern there. – Mike Miller Feb 24 '16 at 10:34
Ah wait, do you use that Datatable in the return? if so then yes, use yours. Sorry didn't read the code properly. – Mike Miller Feb 24 '16 at 10:36
File.WriteAllText(Saving_path, data); should be outside of the loop, and you should be writing sb.ToString() not data. Step through it in the debugger and inspect what is happening to understand. – Mike Miller Feb 24 '16 at 10:38

Store one .xls column as .txt file using C#

1 Answers1