Put the content of multiple .txt files into Excel

Question

Is there any way I can put the content of different multiple .txt files (practically the content of all .txt file in one folder) into Excel 2010? I need one cell (A1) to be the name of the file, and the other cell (A2) to be the whole content of that .txt file. Same goes for the other .txt files, i.e. B1-B2, C1-C2, etc.

Thanks in advance.

Answer 1

If a CSV is acceptable, you can write a little program to read in all text files in the given directory and write out the data you need in CSV format:

"File Name 1","Contents of File 1" 
"File Name 2","Contents of File 2"

If you open the CSV in Excel, the data will display just as you specify.

If you must have a true Excel file (.xls, .xlsx), you can use Interop to access the Excel libraries from C#, but this solution is somewhat more complex.

http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

You can use Directory.EnumerateFiles to list all of the file names in the desired folder, and File.ReadAllText to read in the contents of each file.

When working with CSV files, there are some nuances around proper quoting of the output (see the Wikipedia link at the start of my answer). I wrote a little extension method to make it easier to output properly quoted CSV:

   static public class Extensions
    {
        static public string CsvQuote(this string text)
        {
            if (text == null)
            {
                return string.Empty;
            }

            bool containsQuote = false;
            bool containsComma = false;
            int len = text.Length;
            for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
            {
                char ch = text[i];
                if (ch == '"')
                {
                    containsQuote = true;
                }
                else if (ch == ',')
                {
                    containsComma = true;
                }
            }

            bool mustQuote = containsComma || containsQuote;

            if (containsQuote)
            {
                text = text.Replace("\"", "\"\"");
            }

            if (mustQuote)
            {
                return "\"" + text + "\"";  // Quote the cell and replace embedded quotes with double-quote
            }
            else
            {
                return text;
            }
        }

    }

EDIT:

Off the top of my head (not debugged or anything), the code to write out the CSV could look something like this:

string myDirectory = @"C:\Temp";
StringBuilder csv = new StringBuilder();
foreach (string fileName in Directory.EnumerateFiles(myDirectory))
{
    string fileContents = File.ReadAllText(fileName);
    csv.Append(fileName).Append(",").AppendLine(fileContents.CsvQuote());
}
File.WriteAllText(@"C:\SomePath\SomeFile.csv", csv.ToString());