Problem with rows and columns from a CSV

Question

Hi, I'm having a major problem with writing values into a new CSV file.

I receive a csv file from which I parse all of the values. This works fine, I've brought this up and put it into a datagridview.

The first thing that we want to do with this file is to re-arrange it. As it stands, there are two sets of headers: one along the top, and one along the left. Basically, we want it the other way around: put the headers on the left onto the top, and the headers on the top to work their way down the file.

What happens now, though, is that the following code makes a CSV that has one line (which is right) with the correct amount of columns (instead of rows, again correct)... however all of the values for the table are "1, 1, 1, 1, 1, 1, 1, 1, 1, 1" etc.

EG:

I get the file like so:

File:, 1, 2, 3, 4, 5

type a, 9%, 10%, 11%, 12%, 13%

type b, 9%, 10%, 11%, 12%, 13%

type c, 9%, 10%, 11%, 12%, 13%

type d, 9%, 10%, 11%, 12%, 13%

I want to make it like so:

File:, type a, type b, type c, type d

1, 9%, 9%, 9%, 9%

2, 10%, 10%, 10%,10%

3, 11%, 11%, 11%, 11%

4, 12%, 12%, 12%, 12%

5, 13%, 13%, 13%, 13%

Here is the code I have so far:

if (!File.Exists(path))
            return null;

        string full = Path.GetFullPath(path);
        string file = Path.GetFileName(full);
        string dir = Path.GetDirectoryName(full);

        //create the "database" connection string
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
          + "Data Source=\"" + dir + "\\\";"
          + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

        //create the database query
        string query = "SELECT * FROM [" + file + "]";

        //create a DataTable to hold the query results
        DataTable dTable = new DataTable();

        //create an OleDbDataAdapter to execute the query
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

        //Get the CSV file to change position.


        try
        {
            //fill the DataTable
            dAdapter.Fill(dTable);

            string activedir = dir;
            //Now write in new format.
            StreamWriter sw = new StreamWriter(File.Create(dir + "\\modified_" + file.ToString()));
            int iRowCount = dTable.Rows.Count;
            foreach(DataRow dr in dTable.Rows)
            {
                string writeVal = (string)dTable.Columns[0].ToString();
                sw.Write(writeVal);
                sw.Write(",");
            }
            sw.Write(sw.NewLine);

            sw.Close();
            dAdapter.Dispose();

        }
        catch (InvalidOperationException /*e*/)
        { }

        string newPath = dir + "\\modified_" + file.ToString();

        if (!File.Exists(newPath))
            return null;

        string f = Path.GetFullPath(newPath);
        string fi = Path.GetFileName(f);
        string di = Path.GetDirectoryName(f);

        //create the "database" connection string
        string conn = "Provider=Microsoft.Jet.OLEDB.4.0;"
          + "Data Source=\"" + di + "\\\";"
          + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

        //create the database query
        string q = "SELECT * FROM [" + fi + "]";

        //create a DataTable to hold the query results
        DataTable dTe = new DataTable();

        //create an OleDbDataAdapter to execute the query
        OleDbDataAdapter dA = new OleDbDataAdapter(q, connString);

        //Get the CSV file to change position.


        try
        {
            //fill the DataTable
            dA.Fill(dTe);

        }
        catch (InvalidOperationException /*e*/)
        { }

        return dTe;

Any help?

Thanks!

Answer 1

Changed file so that "string writeVal = ..."

is now

object writeVal = dr[0];

Answer 2

I believe you are looking for data-transposing. See this for quick transpose of a datatable.