Add 3 DataTable type data to 3 sheets each and save as csv file.

[patrick]

Hello

I want to add three DataTables to each of the three sheets and save them as one csv file.

How do I modify the code below?

If possible, I don't want to use the Nuget package.

        public DataTable SqlDataAdapter1(string sqlString)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlString, conn);
                adapter.Fill(dt);

                conn.Close();
            }

            return dt;
        }

        public void ReadData(string startdate, string enddate)
        {
            string sqlString = string.Format("exec dbo.usp_selectdata @startdate = '{0}', @enddate = '{1}' ", startdate, enddate);

            DataTable dt = DBHandler.SqlDataAdapter1(sqlString);

            string fileName = "D:\\0214.csv";

            SaveCSV(fileName, dt);
        }

// Below is This code saves one DataTable as one CSV file on one Sheet.
// Here, I want to add three DataTables to each of the three sheets and save them as one csv file.
// DataTable 1 is added to Sheet 1
// DataTable 2 is added to Sheet 2
// DataTable 3 is added to Sheet 3
// <==== Save this as one csv file

        public bool SaveCSV(string fileName, DataTable table)
        {
            List<string> buff = new List<string>();

            string[] columnNames = table.Columns.Cast<DataColumn>().
            Select(column => column.ColumnName).
            ToArray();

            string line = string.Join(",", columnNames);
            buff.Add(line);

            foreach (DataRow row in table.Rows)
            {
                string[] fields = row.ItemArray.Select(field => field.ToString()).
                ToArray();

                line = string.Join(",", fields);
                buff.Add(line);
            }

            File.WriteAllLines(fileName, buff.ToArray(), Encoding.UTF8);


            return true;
        }

 

[Skydiver]

CSV files do not have multiple sheets. A single CSV files represents a single sheet.

You can make up your own file format that can have multiple sheets and make that format CSV like, but please don't call it a CSV file.

 

[patrick]

CSV files so not have multiple sheets. CSV files contain just a one sheet.

There is Nuget free package ???
Is there method?

 

[Skydiver]

You can make up your own file format that can have multiple sheets and make that format CSV like, but please don't call it a CSV file.

 

[patrick]

Please explain easily..

Your answer is Excel Sheet ??? Then Excel file convert csv ??

You can make up your own file format that can have multiple sheets and make that format CSV like, but please don't call it a CSV file.

==>what is mean??? Please Example c# Code

Please help me... i am waiting your answer

 

[Skydiver]

The original spreadsheet programs only supported a single sheet. This is the origin of the .CSV file format as a way to save that single sheet of data into a plain text representation that human readable, and also machine readable that can be imported by other programs. Notice that the term workbook only came up much later where a workbook contained multiple spreadsheets. As far as I know, there isn't a commonly accepted plain text representation of a workbook.

Your attempting to save multiple sheets into a single .CSV file. There is no such commonly accepted file format. In the late 80's through late 90's there was a competitor for Paradox, dBase, FoxBase, etc. I don't remember their name right now. I'd like to say it was PowerBase. Anyway, they created their own plain text file format for exporting their data. The format looked something like:

$ AA - Students
$ AC - List of students enrolled
ID, Name, Age
7, Patrick, 12
3, Jennifer, 14

$ AA - Grades
$ AC - Grades for students
StudentID, SubjectID, Grade
7, 3, A
7, 1, A
3, 3, A
3, 1, B+

$ AA - Subjects
$ AC - List of subjects
ID, Name
3, History
4, Math

Basically they had multiple comma separated values for each of their tables, but the tables were separated by those $ AA and $ AC prefixed lines. Basically they invented their own format.

 

[patrick]

The original spreadsheet programs only supported a single sheet. This is the origin of the .CSV file format as a way to save that single sheet of data into a plain text representation that human readable, and also machine readable that can be imported by other programs. Notice that the term workbook only came up much later where a workbook contained multiple spreadsheets. As far as I know, there isn't a commonly accepted plain text representation of a workbook.

Your attempting to save multiple sheets into a single .CSV file. There is no such commonly accepted file format. In the late 80's through late 90's there was a competitor for Paradox, dBase, FoxBase, etc. I don't remember their name right now. I'd like to say it was PowerBase. Anyway, they created their own plain text file format for exporting their data. The format looked something like:

$ AA - Students
$ AC - List of students enrolled
ID, Name, Age
7, Patrick, 12
3, Jennifer, 14

$ AA - Grades
$ AC - Grades for students
StudentID, SubjectID, Grade
7, 3, A
7, 1, A
3, 3, A
3, 1, B+

$ AA - Subjects
$ AC - List of subjects
ID, Name
3, History
4, Math

Basically they had multiple comma separated values for each of their tables, but the tables were separated by those $ AA and $ AC prefixed lines. Basically they invented their own format.

I can't do this because it's too difficult.
This problem doesn't work.
Thank you for your reply.

 

[Skydiver]

I've seen you write more complex and difficult code than this in your other questions.

This is actually very easy to implement. I think the problem is that you are locked into thinking that you need to use your current SaveCSV() method which looks to have just been cribbed from this StackOverflow question and answer. If you took time to actually understand the code, it's easy to modify. Just use AppendAllLines() instead of WriteAllLines(), and be sure to have some kind of separator between the tables that are appended to the file.

 

[Skydiver]

As an aside, you should avoid most of the implementations in that SO question and answers that use a StringBuilder and/or Llist<string>. You are wasting memory. Just write directly into the file. I think there's one or two answers there that do this.

 

[cjard]

Or just use a nuget package like EPPlus that can save a dataset (multiple tables) to an XLSX (multiple sheets)

As mentioned, CSV don't have multiple sheets, but if you can provide me with a CSV that renders as multiple sheets when opened in excel, then I'll write you the code that creates it. I won't even charge!

By the way, you don't need a using statement or statements to open and close the connection when using a DataAdapter