CSV to FlatFile Fast and Easy

When developing, I often have to use sample files which are not always in the desired format, in coma separated files or tab separated files and i have to put them in a flat format.

Using the String.Split() and the String.Format() methods I can format a flat file very fast. Here is a piece of code highlighting the functionality. I have omitted all code not directly related to the conversion like error handling.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

String fileIn = "myFile.txt";
String fileOut = "myFlatFile.txt";

StreamReader sr = new StreamReader(fileIn);
StreamWriter sw = new StreamWriter(fileOut, false);

String currentRecord = sr.ReadLine();

String[] tokens = {};

while (null != currentRecord && currentRecord.Length > 0)
{
   tokens = currentRecord.Split(',');

   if (tokens.Length > 0)
   {
   currentRecord = String.Format("{0, 3}{1, 4}{2, -5}{3, 2}", tokens);
   sw.WriteLine(currentRecord);
   }
}

sr.Close();
sw.Flush();
sw.Close();

There are 2 steps :

Using the Split() method with the appropriate separator to divide the record into an array of fields. Using the Format() method with the array resulting from the Split. No need to use all fields one by one. It is way easier to read.

This is pretty straight forward.

What if you want to change the order of the fields in the flat file records ? Simply change the order of the indexes in the String Format :

1

   String.Format("{2, -5}{0, 3}{1, 4}{3, 2}", tokens)

Format String in short:

Each {n,length} element represent a field which is an element of the Token Array. The n represents the element index and the length the length of the element. A negative length value means that the value must be left aligned.

In that configuration, we cannot format Dates, Numbers or currencies. If you really want to do so, it is best to replace the value of an element with its formatted value. The formatting looks like {n,length:stringFormat} where stringFormat can be any formatting string you like. Thus to convert a string into a date formatted string you will have to do the following : Lets assume the 3rd element is containing the date (index = 2):

1
2

    tokens[2] = String.Format("{0:yyyyMMdd}", Convert.ToDateTime(tokens[2]));
    String.Format("{0, 3}{2, -8}{3, 2}", tokens)

So if we need to format non-string values, we have to add one step:

Using the Split() method with the appropriate separator to divide the record into an array of fields; Format the required fields as needed; Using the Format() method with the array resulting from the Split. No need to use all fields one by one.;