Converting XLSX file using to a CSV file

Question

I need to convert an XLSX file to another CSV file. I've done a lot of research on how to do this process, but I did not find anything that suited me. I found this Github Gist only Convert an Epplus ExcelPackage to a CSV file

That returns an Array of binary. But apparently it does not work any more.

I'm trying to load Array using LoadFromCollection

FileInfo novoArquivoCSV = new FileInfo(fbd.SelectedPath);
var fileInfoCSV = new FileInfo(novoArquivo + "\\" + nameFile.ToString() + ".csv");

using (var csv = new ExcelPackage(fileInfoCSV))
{
    csv.Workbook.Worksheets.Add(nameFile.ToString());
    var worksheetCSV = csv.Workbook.Worksheets[1];

    worksheetCSV.Cells.LoadFromCollection(xlsx.ConvertToCsv());
}

Wellfixed

Step 1: rename your variables. You and I only get confused by that. If xls is actually filename, then name it so. If xls is the filename, then what is nameFile? BTW: csv is not a CSV file, it's an EPPlus Excel package. That also has the wrong variable name. – Crackup

Whatever xls is, worksheetCSV.Cells.LoadFromCollection creates an XLSX file. WHY do you want to convert xlsx to CSV anyway? XLSX is just a zipped package of XML files that can be used by any program or language without problems. You don't need Excel to read it or create it. CSVs though have issues with date, number and separator formats – Deodorize

Yes I know that. However, I need the .csv file to import into a SQL DTO 2000, unfortunately I can only with .csv file, so the project was created. :/ – Wellfixed

The earliest supported SQL Server version is 2012. Do you really have to work with SQL Server 2000 ????? – Deodorize

Anyway, just load the XLSX with EPPlus, loop over the Cells and write them out. What's wrong with the gist you linked to? – Deodorize

@PanagiotisKanavos Unfortunately I can not choose the version of sql I'm going to work with. If I could, it would certainly not be SQL 2000 – Wellfixed

@G.Sena Then what's wrong with the gist? The code you posted tries to create an XLSX, not read it. – Deodorize

Did you think that the array returned has to be converted back to an XLSX? That's a buffer that contains the final CSV text. You can write it directly to an ASCII file. Although I wouldn't do it this way. Instead of using a MemoryStream, I'd pass the target file to ConvertToCsv and use a StreamWriter to write directly to the output file – Deodorize

@G.Sena regarding the version of SQL Server - 2000 is way too old. You could use a current SQL Server 2016 Express instance to hold the same data and provide better performance. Back then server has 1 or 2 CPUs running at 733MHz. Express nowadays offers columnstores, compression and in-memory tables. I hope you charged triple price for working with such obsolete software. – Deodorize

@PanagiotisKanavos It's really a shame to work with such obsolete software. But we already have a project to migrate to SQL Server 2012, but this should take a while. – Wellfixed

@G.Sena 2012 is the oldest supported version today. Why not migrate to a fresh version? 2016 SP1 provides columnstores, in-memory tables, compression even in Express and LocalDB. Why deal with 4 year old bugs when you can have the fastest and most stable version now? – Deodorize

@G.Sena btw availability through AlwaysOn improved a lot from 2012 to 2016. Make sure you won't waste your licensing money – Deodorize

Answer 1

The code you linked to reads an XLSX sheet and returns the CSV data as a byte buffer through a memory stream.

You can write directly to a file instead, if you remove the memory stream and pass the path to the target file in ConvertToCsv :

public static void ConvertToCsv(this ExcelPackage package, string targetFile)
{
        var worksheet = package.Workbook.Worksheets[1];

        var maxColumnNumber = worksheet.Dimension.End.Column;
        var currentRow = new List<string>(maxColumnNumber);
        var totalRowCount = worksheet.Dimension.End.Row;
        var currentRowNum = 1;

        //No need for a memory buffer, writing directly to a file
        //var memory = new MemoryStream();

        using (var writer = new StreamWriter(targetFile,false, Encoding.UTF8))
        {
        //the rest of the code remains the same
        }

        // No buffer returned
        //return memory.ToArray();
}

Encoding.UTF8 ensures the file will be written as UTF8 with a Byte Order Mark that allows all programs to understand this is a UTF8 file instead of ASCII. Otherwise, a program could read the file as ASCII and choke on the first non-ASCII character encountered.

Deodorize

This works for me too, be aware that the present code is referred to the github code. I needed to integrated the classes in my project, but with 2 hours of work I made it running fine! – Ency

Answer 2

Checkout the .SaveAs() method in Excel object.

wbWorkbook.SaveAs("c:\yourdesiredFilename.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)

Or following:

public static void SaveAs()
{
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Add(Type.Missing);
    Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets;
    Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1];

    Microsoft.Office.Interop.Excel.Range thisCell = (Microsoft.Office.Interop.Excel.Range)CurSheet.Cells[1, 1];

    thisCell.Value2 = "This is a test.";

    wbWorkbook.SaveAs(@"c:\one.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    wbWorkbook.SaveAs(@"c:\two.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    wbWorkbook.Close(false, "", true);
}

Is there any simple way to convert .xls file to .csv file? (Excel)

There are several other resources online that can help with this ind of thing. Actually, for something generic like this, you should always Google for a solution, and try to figure it out yourself. That's the best way to learn how to do technical things. If you get stuck, or if you have a very specific question, this site is a great place to post your question(s). It seems to me, you probably started here, and you didn't do any preliminary work yourself.

Algonkian