Program to convert XLS files to CSV not creating the file when run

Question

I am building an application in Visual Studio 2019 Community edition to convert an Excel XLS file to a CSV. I had found some example code that was using the ExcelReader library and modified it so it would pull the XLS file and make a test.csv file. When I run this I do not get any errors back, but the file is not created.

This is to help automate a currently manual process where we have to manually open the XLS file and save it as a CSV. I am using Visual Studio Community edition (2019) and have added the ExcelReader library.

'''public partial class Default2 : System.Web.UI.Page
{
    static void Main() { }
    DataSet result = new DataSet();
    string filePath = 
@"C:\Users\BEM26331\Documents\AppDevProjects\9.10.19.xls";

    protected void UploadButton_Click(object sender, EventArgs e)
    {
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        FileStream stream = File.Open(filePath, FileMode.Open, 
FileAccess.Read);

        Excel.IExcelDataReader excelReader = 
Excel.ExcelReaderFactory.CreateBinaryReader(stream);
        DataSet result = excelReader.AsDataSet();
        excelReader.Close();

        result.Tables[0].TableName.ToString();

        string csvData = "";
        int row_no = 0;
        int ind = 0;

        while (row_no < result.Tables[ind].Rows.Count)
        {
            for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
            {
                csvData += result.Tables[ind].Rows[row_no][i].ToString() 
+ ",";
            }
            row_no++;
            csvData += "\n";
        }

        string output = 
@"C:\Users\BEM26331\Documents\AppDevProjects\test.csv";
        StreamWriter csv = new StreamWriter(@output, false);
        csv.Write(csvData);
        csv.Close();
    }
}'''

The end result should create the same file, but saved as a CSV. The actual output does not create any file.

score 1 · Accepted Answer · answered Oct 15 '19 at 13:08

1

Something like this perhaps?:

    public void ConvertToCSV(string sourceFile, string targetFile)
            {
                using (var stream = System.IO.File.Open(sourceFile, System.IO.FileMode.Open, System.IO.FileAccess.Read))
                {
                    //add ExcelDataReader and ExcelDataReader.DataSet
                    //Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    using (var reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream))
                    {
                        //DataSet result = reader.AsDataSet();
                        DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                        {
                            ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                        });
                        if (result.Tables.Count > 0)
                        {
                                System.Text.StringBuilder output = new StringBuilder();
                            DataTable table = result.Tables[0];
                            //save column names
                            output.AppendLine(String.Join(",", table.Columns.Cast<System.Data.DataColumn>().ToList()));
                            //save all rows
                            foreach (System.Data.DataRow dr in table.Rows)
                            {
                                output.AppendLine(String.Join(",", dr.ItemArray.Select(f=>f.ToString() ).ToList()   ) );
                            }
                            System.IO.File.WriteAllText(targetFile, output.ToString());
                        }
                    }
                }
            }

answered Oct 15 '19 at 13:08

yob

528
4
9

Thanks! I will test this and let you know. – htmlbran86 Oct 15 '19 at 13:43
Sorry, do you recommend using this in place of the code I had posted, or would this be added to it? – htmlbran86 Oct 15 '19 at 16:11
@htmlbran86 - is it working for you as expected? then, yes, you can just re-use it. – yob Oct 15 '19 at 17:25
I'm still having issues with this. I've tried using both sets of code, then just yours and getting errors. I do appreciate the help. I'll be able to look at this more tonight. – htmlbran86 Oct 15 '19 at 21:31
@htmlbran86 - what are the errors?, what are you using - webforms, winforms? – yob Oct 16 '19 at 00:37
I am not using webforms or winforms. It is just meant to be an executable I'm hoping to set as a scheduled task eventually. I'm going to build a new solution starting with the code you provided and then add the ExcelReader Library. I'll keep you posted. – htmlbran86 Oct 17 '19 at 14:34
Got things figured out. Thanks for the help! – htmlbran86 Oct 17 '19 at 21:45
I can create a new question for this, but does anyone have a good way to search for said Excel file, but have it just look for the .xls instead of needing the full filename? If there is a better post where this has been answered let me know. Thanks! – htmlbran86 Oct 18 '19 at 18:18
@htmlbran86 - what do you mean by "search"? select excel-like files in a given directory? or search for such files within a given path+subdirectories? check https://learn.microsoft.com/en-us/dotnet/api/system.io.directory.getfiles?view=netframework-4.8 - public static string[] GetFiles (string path, string searchPattern); where searchPattern is "*.xls" . – yob Oct 19 '19 at 13:41
Thanks @yob! I appreciate it. – htmlbran86 Oct 21 '19 at 13:20

Program to convert XLS files to CSV not creating the file when run

1 Answers1