Click here to Skip to main content
15,867,453 members
Articles / Desktop Programming / Windows Forms

Convert .xlsx & .xls to .csv

Rate me:
Please Sign up or sign in to vote.
4.04/5 (13 votes)
15 Sep 2011CPOL2 min read 268.6K   28K   27   28
Simple file conversion using C#.NET

Introduction

This article explains how to use the ExcelDataReader library for simple file conversion, particularly Excel to csv. Easily convert your Excel 97-2003(.xls) or 2007(.xlsx) files to simple comma separated value file (.csv).

The CSV file is the easier file to operate with in the program.

Need

As I am a beginner, I experienced some difficulty in operating Excel file directly in my program. So I thought of doing it.

Some of the problems I faced are like I couldn't fetch the appropriate cell, difficulty in accessing row wise, etc.

Since CSV is a simple stream file, it is easier to deal with the file data. CSV data can also be viewed as an excel sheet.

Working

The project works on ExcelDataReader library.

The main aim of this project is to read the Excel data into the program & convert it to an easy to operate format that is CSV.

Coming to the working of project:

C#
IExcelDataReader excelReader 

This object allows us to operate with the Excel file.

Then we read whole Excel data into a Dataset & that Dataset will be used to produce CSV file.

C#
DataSet result = excelReader.AsDataSet(); 

I am using Dataset here because it is easy to get the values and write the CSV file from contents of dataset as the matrix separated by commas.

User friendly Interface

image.jpg

  • Browse for input Excel file to convert.
  • Browse for output folder & file name.
  • Click the CONVERT button.

The converted CSV file will be in the output folder specified.

Using the Code

The ExcelDataReader library is used. Only prototypes are shown in the preview. Get full code in the attached source file. Add the ExcelDataReader library references to your project if you use this code in other projects (References are attached).

C#
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

// Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

// DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();

// Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Sheets in Excel file become tables in Dataset:

C#
result.Tables[0].TableName.ToString(); // to get first sheet name (table name)

Using the above codes, the CSV file can be generated.

C#
string csvData = "";
int row_no = 0;

while (row_no < result.Tables[ind].Rows.Count) // ind is the index of table
				// (sheet name) which you want to convert to csv
   {
      for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
          {
             csvData += result.Tables[ind].Rows[row_no][i].ToString() + ",";
          }
      row_no++;
      csvData  += "\n";
   }

After generating CSV data, write it to file.

C#
string output = filepath + filename + ".csv"; // define your own filepath & filename
StreamWriter csv = new StreamWriter(@output, false);
csv.Write(csvData);
csv.Close();

I thank the author of ExcelDataReader library which helped a lot in getting my project done.

Here is the reference for ExcelDataReader:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionConvert .xlsx & .xls to .csv VB.net Pin
Hemil Gandhi13-Apr-20 7:11
Hemil Gandhi13-Apr-20 7:11 
Questionxls to csv is working but xlsx not Pin
Member 145240259-Jul-19 1:56
Member 145240259-Jul-19 1:56 
QuestionConvert .xlsx to .xls Pin
Member 1394733513-Aug-18 0:55
Member 1394733513-Aug-18 0:55 
Questionuse it in VB.Net Pin
Teoh Chia Wei15-Feb-17 14:00
Teoh Chia Wei15-Feb-17 14:00 
can i use it in my VB.Net?
QuestionGerman Umlaute (äüöß) Pin
Member 118847295-Aug-15 4:18
Member 118847295-Aug-15 4:18 
AnswerRe: German Umlaute (äüöß) Pin
titolr106-Oct-15 6:44
titolr106-Oct-15 6:44 
QuestionC# code Issue Pin
arunkumarxm17-Mar-15 22:23
arunkumarxm17-Mar-15 22:23 
QuestionGetting error - Cannot find central directory Pin
PrakashKathar30-Oct-14 1:55
PrakashKathar30-Oct-14 1:55 
GeneralThank you Pin
brayan216929-Oct-14 23:09
brayan216929-Oct-14 23:09 
QuestionError in getting the datatale.Table[0] value. Pin
Er Daljeet Singh3-Jul-14 19:05
professionalEr Daljeet Singh3-Jul-14 19:05 
Questionhelp please Pin
Member 108073439-May-14 7:51
Member 108073439-May-14 7:51 
QuestionNo conserva los valores de la celda Pin
damian99999999916-Apr-14 5:55
damian99999999916-Apr-14 5:55 
QuestionRE: working version Pin
andrew sin (andrewsin.com)24-Feb-14 5:52
andrew sin (andrewsin.com)24-Feb-14 5:52 
SuggestionDon't use string copy functions to add the content to temporary variables! They have a very bad performance. Pin
Uwe Arndt5-Jun-13 6:49
Uwe Arndt5-Jun-13 6:49 
PraiseRe: Don't use string copy functions to add the content to temporary variables! They have a very bad performance. Pin
Member 1387359214-Jun-18 23:39
Member 1387359214-Jun-18 23:39 
Questionrecommendation Pin
msakdillz1-Feb-13 6:08
msakdillz1-Feb-13 6:08 
QuestionNOT WORKING Pin
a2z4u076-Nov-12 11:00
a2z4u076-Nov-12 11:00 
QuestionDate column values changing for .xls ?? Pin
akshay k 231-May-12 8:11
akshay k 231-May-12 8:11 
AnswerRe: Date column values changing for .xls ?? Pin
srinivasvemula4u24-Nov-12 2:42
srinivasvemula4u24-Nov-12 2:42 
QuestionWell Pin
Yves22-Sep-11 14:29
Yves22-Sep-11 14:29 
GeneralMy vote of 2 Pin
ShermansLagoon13-Sep-11 6:53
ShermansLagoon13-Sep-11 6:53 
GeneralRe: My vote of 2 Pin
Srikanth Anandateertha14-Sep-11 18:48
Srikanth Anandateertha14-Sep-11 18:48 
GeneralRe: My vote of 2 Pin
ShermansLagoon15-Sep-11 8:15
ShermansLagoon15-Sep-11 8:15 
GeneralRe: My vote of 2 Pin
Srikanth Anandateertha15-Sep-11 8:19
Srikanth Anandateertha15-Sep-11 8:19 
GeneralRe: My vote of 2 Pin
ShermansLagoon16-Sep-11 10:12
ShermansLagoon16-Sep-11 10:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.