0

If you manually open a tab delimited text file within Excel it will auto format using the “Text Import Wizard”.

This is the exact functionality I need to accomplish but programmatically with C#.

What steps would you recommend to make this as straight forward as possible?

The format of the text will always be the same with the exception some lines might include multiple tabs("\t") in a row if that "cell" should be blank.

I've tried parsing the data directly into a DataTable like the following post recommends.

Efficient function for reading a delimited file into DataTable

I ended up with row data in the wrong cells, I believe this is due to areas were there are multiple tabs("/t") in a row.

            var filename = "report.txt";
            var reader = ReadAsLines(filename);

            var data = new DataTable();

            var headers = reader.First().Split('\t');
            foreach (var header in headers)
                data.Columns.Add(header);

            var records = reader.Skip(1);
            foreach (var record in records)
                data.Rows.Add(record.Split('\t'));

            StringBuilder sb = new StringBuilder();

            string[] columnNames = data.Columns.Cast<DataColumn>().
            Select(column => column.ColumnName).
            ToArray();
            sb.AppendLine(string.Join(",", columnNames));

            foreach (DataRow row in data.Rows)
            {
                string[] fields = row.ItemArray.
                Select(field => field.ToString()).
                ToArray();
                sb.AppendLine(string.Join(",", fields));
            }

            File.WriteAllText("test.csv", sb.ToString());
        }

static IEnumerable<string> ReadAsLines(string filename)
        {
            using (var reader = new StreamReader(filename))
                while (!reader.EndOfStream)
                    yield return reader.ReadLine();
        }
Pyreal
  • 517
  • 3
  • 8
  • 19
  • `This is the exact functionality I need to accomplish but programmatically with C#.` You want to programmatically open Excel? – mjwills Apr 06 '18 at 21:32
  • It would be awesome if you could share your [mcve] of what you have tried so far. – mjwills Apr 06 '18 at 21:32
  • There is an absolutely wonderful tool in excel: record macro. Simply start recording and open the file with the wizard and check out the recorded code. Translating the vba code to c# interop code should be rather trivial. On how to programmatically start an excel application from c# to actually leverage the translated recorded code, you have tons of questions on SO showing you how to do it. – InBetween Apr 06 '18 at 21:34
  • updated with sample code. – Pyreal Apr 06 '18 at 21:51
  • @mjwills Ideally, if I could have my C# program read in the .txt file, open up Excel and automatically format the exact way "Text Import Wizard" does i'd be fine with that. – Pyreal Apr 06 '18 at 21:55
  • @InBetween that's not a bad idea, I haven't used VBA or the record macro before, does the C# package contain all the same functionality that Excel provides? – Pyreal Apr 06 '18 at 21:57
  • I'd be surprised if it didn't, at least concerning your current scenario. – InBetween Apr 06 '18 at 22:01

0 Answers0