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());
}

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. — Thomas Weller, Mar 21 '17 at 12:11
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 — Panagiotis Kanavos, Mar 21 '17 at 12:13
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. :/ — G. Sena, Mar 21 '17 at 12:19
The earliest supported SQL Server version is 2012. Do you really have to work with SQL Server 2000 ????? — Panagiotis Kanavos, Mar 21 '17 at 12:21
Anyway, just load the XLSX with EPPlus, loop over the `Cells` and write them out. What's wrong with the gist you linked to? — Panagiotis Kanavos, Mar 21 '17 at 12:22
@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 — G. Sena, Mar 21 '17 at 12:24
@G.Sena Then what's wrong with the gist? The code you posted tries to *create* an XLSX, not read it. — Panagiotis Kanavos, Mar 21 '17 at 12:25
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 — Panagiotis Kanavos, Mar 21 '17 at 12:28
@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. — Panagiotis Kanavos, Mar 21 '17 at 12:39
@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. — G. Sena, Mar 21 '17 at 12:48
@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](https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1). Why deal with 4 year old bugs when you can have the fastest and most stable version now? — Panagiotis Kanavos, Mar 21 '17 at 12:50
@G.Sena btw availability through AlwaysOn improved a *lot* from 2012 to 2016. Make sure you won't waste your licensing money — Panagiotis Kanavos, Mar 21 '17 at 12:52

score 9 · Accepted Answer · answered Mar 21 '17 at 12:35

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.

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! — Nelson Gomes Matias, Mar 19 '19 at 10:09

score 2 · Answer 2 · edited May 23 '17 at 11:46

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.

Converting XLSX file using to a CSV file

2 Answers2

Linked