Format CSV using C#

Question

_{Friday, September 21, 2007 8:15 PM}

Hello,

I get CSV file like this from a C# application.

WebClient wc = new WebClient(); 
string uri = rpturl.appURL;
string fname = "c:/data_file.csv";
wc.DownloadFile(uri,fname);

The CSV file i get from application got 6 fields

ID    FNAME   MNAME   LNAME  MARK   SUBJECT

I want only 5 field output from CSV in this order.

ID   LNAME  FNAME  SUBJECT  MARK

I have another application looking for fields in the following order.How can i make CSV like this using C# code?.

Please help me with code. I never done anything with CSV before.

Thanks

All replies (21)

_{Friday, September 21, 2007 8:24 PM ✅Answered}

csv is just coma delimited plain text file. if the field contains coma, you cna use double quote around it.

such as:

1024, "test, another test", nothing

so basically you need to parse/write such format. if there's no ',' coma in your fields, it's just trivial.

you can read each line, use string.split(',') to get all fields into string[]

then you can use StreamWriter.Writeline(string.format("{0},{1},{2},{3},{4}",fields[0],fields[3],fields[5],fields[4]) to convert the 6 fields to 5 fields as you presented above.

_{Friday, September 21, 2007 9:17 PM}

Thanks Tony. Got some idea.

Please post  real sample.

never did so much c# coding.

_{Saturday, September 22, 2007 7:31 AM}

Tony did post some sample code, now it's up to you to put some effort in it... (And if that doesn't work, post the code you've tried, and tell us what is going wrong... But don't expect us to do your homework.)

_{Sunday, September 23, 2007 9:50 PM}

Hi

I will suggest you to try out logparser. You can do almost everything with CSVs using that.

I dont know if latest still doesn't allow for joing two files with different formats but I have a work around on that so let me know.

Also http://www.codeproject.com/cs/database/CsvReader.asp might help.

_{Monday, September 24, 2007 7:45 PM}

Thanks yogesh.

What is logparser?. How can i do?.

Do u have a code sample.

I got CSV file getting from an application...i need to remove some fields and reorder the CSV file.

Add a new row on the top of CSV file.

This changed CSV file will be cosumed by anther application. So i need to format CSV for the new application.

How can i open a CSV file, parse through CSV file.

Write only required columns back to CSV file.

Add new row to new CSV file on the top with some string name?.

Experts, Please post a code sample. I am C# begineer.

_{Monday, September 24, 2007 8:06 PM}

for the simple case without double quote:

Code Snippet

StreamWriter writer = new StreamWriter("output.csv");
string[] lines = File.ReadAllLines("input.csv");
foreach(string line in lines){

string[] fields = line.Split(',');

writer.Writeline(string.format("{0},{1},{2},{3},{4}",fields[0],fields[3],fields[5],fields[4])

}
writer.close();

for large file, you probably want to read each line using streamreader, for complicated csv file handling, please try 3rd party code

_{Tuesday, September 25, 2007 4:21 PM}

Hi

Logparser provides SQL like access to log files usually CSV etc. Please google 'logparser'. It was Microsoft internal tool but can downloaded now. From your problem I think you can just create a new file with whatever column you want by querying through logparser.

(http://www.microsoft.com/downloads/details.aspx?FamilyID=890CD06B-ABF8-4C25-91B2-F8D975CF8C07&displaylang=en)

Regards

Yogesh

_{Friday, September 28, 2007 6:43 PM}

thanks tony.

i modified the code according to my requirement....this code is not working...

How can i make it work?...  Please let me know.

StreamWriter writer = new StreamWriter("//server/app/output.csv");

string[] lines = File.ReadAllLines("//server/app/input.csv");

foreach(string line in lines){

string[] fields = line.Split(',');

writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31},{32},{33},{34},{35},{36}", fields[0], fields[1], fields[2], fields[22], fields[21], fields[31]));

}

writer.Close();

_{Friday, September 28, 2007 6:57 PM}

Code Block

StreamWriter.WriteLine(...)
string.Format(...)
StreamWriter.Close(...)

The code is not verified but I think it should be correct, despite case errors like above.

_{Friday, September 28, 2007 7:30 PM}

thanks tony.

i fixed the case problem.

But code is not working...

i am getting blank csv file as output.

how can i read mutiple line of csv file data and re-order columns and write back to output csv file?.

_{Friday, September 28, 2007 7:51 PM}

so it compiles now? does it throw exception?

the idea is there, but you need to make it work. I see the {0} input argument list count doesn't match that in the format string in your code above.

if you manage to get it to run, try put a break point at WriteLine so you can see what the data is per line and what is about to be written.

_{Monday, October 1, 2007 12:17 AM}

When i try this code.....i am getting all first field in the output file....all other fields are empty...why so?.

it is reading full time...but it is outputting only first field.....all other fields are empty in output file.

StreamWriter writer = new StreamWriter("output.csv");

string[] lines = File.ReadAllLines("input.csv");

foreach(string line in lines)

{

string[] fields = line.Split(',');

writer.WriteLine(string.Format(fields[0],fields[1],fields[2],fields[22],fields[21],fields[31]));

}

writer.Close();

_{Monday, October 1, 2007 1:28 PM}

this line looks wrong without the red part.

writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",fields[0],fields[1],fields[2],fields[22],fields[21],fields[31]));

_{Monday, October 1, 2007 4:26 PM}

If i add red part...i am getting wrong field values.......

why so?.

How u control field order?.

_{Monday, October 1, 2007 5:10 PM}

You use format string plus/or the input parameter order to control the field order.

Code Block

writer.WriteLine(.Format("{0},{1},{2},{3},{5},{4}",fields[1],fields[0],fields[2],fields[21],fields[21],fields[31]));

e.g.
writer.WriteLine(string.Format("{0},{1},{2},{3},{5},{4}",fields[0],fields[1],fields[2],fields[22],fields[21],fields[31]));

writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}",fields[1],fields[0],fields[2],fields[22],fields[21],fields[31]));

writer.WriterLine(string.Format("{0},{1},{2},{3},{5},{4}",fields[0],fields[1],fields[2],fields[22],fields[21],fields[31]));

My suggestion is to put a break point at this line, check what's the value for each field, then format the string, order fields as you needed.

_{Monday, October 1, 2007 5:24 PM}

Thanks a lot tony....u r great...that a bunch for ur all help....

it worked...

i was doing a silly mistake...

_{Monday, October 1, 2007 5:30 PM}

Dear tony,

i need one more thing to do...

how can i stop creating output file...when there is no data in the input file?.....

we have column heading in the input file...so it creates output file...if data is empty...

how can i stop that...

i want output file...if there is data..

If there is no data....... i don't want output file.  what is the easy way to do that.

working code is here....   how is this code?.  little different from ur code sample. almost same.

using (StreamReader sr = new StreamReader("Input_" + DateTime.Today.ToString("MM_dd_yyyy") + ".csv"))

using (StreamWriter sw = new StreamWriter("Output_" + DateTime.Today.ToString("MM_dd_yyyy") + ".csv"))

{

if (sr == null)

return;

string line;

while ((line = sr.ReadLine()) != null)

{

string[] columns = line.Split(',');

sw.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}", columns[0], columns[1], columns[2], columns[21], columns[20], columns[30]));

}

}

_{Monday, October 1, 2007 5:36 PM}

StreamWriter sw = null;

using (StreamReader sr = new StreamReader("Input_" + DateTime.Today.ToString("MM_dd_yyyy") + ".csv"))

//using (StreamWriter sw = new StreamWriter("Output_" + DateTime.Today.ToString("MM_dd_yyyy") + ".csv"))

{

if (sr == null)

return;

string line;

while ((line = sr.ReadLine()) != null)

{

string[] columns = line.Split(',');

//you probably need to change columns.Length>0 to check if there are enough columns, if the data is valid, etc
if (columns.Length > 0) {

if (sw == null) sw = new StreamWriter("Output_" + DateTime.Today.ToString("MM_dd_yyyy") + ".csv";

sw.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}", columns[0], columns[1], columns[2], columns[21], columns[20], columns[30]));

}

}

}

_{Monday, October 29, 2007 4:03 PM}

Thanks tony...

When i use this code process CSV...

My columns[2] data got one or more comma (,) in it.

So output is not coming correct.

How can i fix this issue?.

columns[2] data example are

One, Two and Three

One

Two

One, Two, Three and Four

using (StreamReader sr = new StreamReader("Input.csv"))

using (StreamWriter sw = new StreamWriter("Output.csv"))

{

if (sr == null)

return;

string line;

while ((line = sr.ReadLine()) != null)

{

string[] columns = line.Split(',');

sw.WriteLine(string.Format("{0},{1},\{2}\,{3}", columns[0], columns[1], columns[2], columns[12]));

}

}

_{Wednesday, November 7, 2007 3:30 AM}

Hi Tony, I also have the same problem with reading CSV files but I use C++ hope you can help me.. I've tried these codes but they don't work:

private: System::Void Form1_Load(System:bject^  sender, System::EventArgs^  e) {
     // Open the file to read from

    String^ path = "C:\Chris\Testing\Assignment1\Assignment1\data.txt";
    MessageBoxIcon::Asterisk);
    if (File::Exists(path))
     {
     MessageBox:how("File exists!","AddressBook", MessageBoxButtons:K, MessageBoxIcon::Asterisk);
     StreamReader^ sr = File:penText( path );
     try
     {
       array<String^>^ arr = gcnew array<String^>(100);
       String^ buffer = "";
       String^ temp;
       int found = 0;

       int count = 0;
       while (buffer=sr->ReadLine())
       {
         //to get 1 line of sentence

        temp = buffer;
        count++;

        arr[count] = temp->Split(",");
        } //while
     }
     finally
     {
       if ( sr )
     delete (IDisposable^)sr;
     }
    } //if file exists
   }

The error was:

Build started: Project: Assignment1, Configuration: Debug Win32
Compiling...
Assignment1.cpp
c:\chris\testing\assignment1\assignment1\Form1.h(597) : error C2664: 'cli::array<Type> ^System:tring:plit(...cli::array<wchar_t,dimension> ^)' : cannot convert parameter 1 from 'const char [2]' to 'wchar_t'
        with
        [
            Type=System:tring ^,
            dimension=1
        ]
        There is no context in which this conversion is possible
Build log was saved at "file://c:\Chris\Testing\Assignment1\Assignment1\Debug\BuildLog.htm"
Assignment1 - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

So what should I do?

Thank you very much.

_{Thursday, January 2, 2014 11:59 AM}

I want add or write new data at first row and then append the old data which are existing in file...

plz replay me thanks in advance...