Transform Flat File without delimiters

Question

I would like to transform flat file test.txt to flat file test-output.txt.

Below the scheme:

---

EXAMPLE INPUT: test.txt

COD/ID:37
PRJ/NAME: Josephy Murphy
PRJ/EMAIL: jmurphy@email.com
PRJ/DESCRIPTION: test37, test37, test37 ...

COD/ID:38
PRJ/NAME: Paul Newman
PRJ/EMAIL: pnewman@email.com
PRJ/DESCRIPTION: test38, test38, test38 ...

.
.

---

EXAMPLE OUTPUT: test-output.txt (pipe delimited without labels)

37|Josephy Murphy|jmurphy@email.com|test37, test37, test37 ...
38|Paul Newman|pnewman@email.com|test38, test38, test38 ...
.
.

---

Links to screenshots:
test.txt
test-output.txt

I want to import this file into SQL Server. But the file test.txt (15,000,000 lines) is not in default for import with delimiters.

I will use SSIS to import the data, but must be in CSV format or other format with delimiters.

I thought about using REGEX or SSIS Script Component. I know the import procedure by SSIS files with formatted text, but this file is not formatted.

Answer 1

You can do it without regex in this case since the context is known.

Use this:

public class EntryN
{
   public string id { get; set; }
   public string name { get; set; }
   public string email { get; set; }
   public string description { get; set; }

   public EntryN()
   {
      this.id = this.name = this.email = this.description = string.Empty;
   }
   public string ToLine()
   { 
       return this.id + "|" + this.name + "|" + this.email + "|" + this.description; 
   }
}

var entries = new List<EntryN>();
using (var sl = new StreamReader(@"c:\YOURPATH.txt", true))
{
    var entry = new EntryN();
    var line = string.Empty;
    while ((line = sl.ReadLine()) != null)
    {
       if (line.StartsWith("COD/ID:"))
          entry.id = line.Substring(8).Trim();
       else if (line.StartsWith("PRJ/NAME:"))
          entry.name = line.Substring(10).Trim();
       else if (line.StartsWith("PRJ/EMAIL"))
          entry.email = line.Substring(11).Trim();
       else if (line.StartsWith("PRJ/DESCRIPTION"))
          entry.description = line.Substring(17).Trim();
      else if (line.Trim() == string.Empty)
      {
          entries.Add(entry);
          entry = new EntryN();
      }
    }
    if (!entry.Equals(new EntryN()))
       entries.Add(entry);
    sl.Close();
}

var resulted = entries.Select(p => p.ToLine()).ToList();

Output:

EDIT: Another code with no separate class that will write directly without creating additional strings:

var id = string.Empty;
var name = string.Empty;
var email = string.Empty;
var description = string.Empty;
using (var sw = new StreamWriter(@"OUTPUT_FILE", false, Encoding.UTF8))
{
    using (var sl = new StreamReader(@"INPUT_FILE", true))
    {
       var line = string.Empty;
       while ((line = sl.ReadLine()) != null)
       {
           if (line.StartsWith("COD/ID:"))
              id = line.Substring(8).Trim();
           else if (line.StartsWith("PRJ/NAME:"))
              name = line.Substring(10).Trim();
           else if (line.StartsWith("PRJ/EMAIL"))
              email = line.Substring(11).Trim();
           else if (line.StartsWith("PRJ/DESCRIPTION"))
              description = line.Substring(17).Trim();
           else if (line.Trim() == string.Empty)
           {
               sw.WriteLine(string.Format("{0}|{1}|{2}|{3}", id, name, email, description));
               id = name = email = description = string.Empty;
            }
        }
        if (!new string[] {id, name, email, description}.Any(p => string.IsNullOrWhiteSpace(p)))
            sw.WriteLine(string.Format("{0}|{1}|{2}|{3}", id, name, email, description));
        sl.Close();
     }
     sw.Close();
 }

Answer 2

With Regex for example:

    class Program
    {
        private static Regex reg = new Regex(@"COD/ID:\s(?<id>\d+)\r\nPRJ/NAME:\s(?<name>.+?)\r\nPRJ/EMAIL:\s(?<email>\S+?@\S+?\.\S+?)\r\nPRJ/DESCRIPTION:\s(?<description>.*?)(?:\n|$)");

        static void Main(string[] args)
        {
            string original = @"
COD/ID: 37
PRJ/NAME: Josephy Murphy
PRJ/EMAIL: jmurphy@email.com
PRJ/DESCRIPTION: test37, test37, test37 ...

COD/ID: 38
PRJ/NAME: Paul Newman
PRJ/EMAIL: pnewman@email.com
PRJ/DESCRIPTION: test38, test38, test38 ...";


            string result = string.Join(
                "\n",
                reg.Matches(original)
                .Cast<Match>()
                .Select(m => string.Format("{0}|{1}|{2}|{3}",m.Groups["id"].Value,m.Groups["name"].Value,m.Groups["email"].Value,m.Groups["description"].Value)));
            Console.WriteLine(result);
        }
    }

Edit

class Program
{
    private static Regex reg = new Regex(@"COD/ID:\s(?<id>\d+)\r\nPRJ/NAME:\s(?<name>.+?)\r\nPRJ/EMAIL:\s(?<email>\S+?@\S+?\.\S+?)\r\nPRJ/DESCRIPTION:\s(?<description>.*?)\r\n");

    static void Main(string[] args)
    {
        StringBuilder intermediateStringBuilder = new StringBuilder();

        using (StreamReader reader = new StreamReader(@"YourInputPath.txt",true))
        {               
            using (StreamWriter writer = new StreamWriter("YourOutputPath.txt"))
            {
                while (reader.Peek() > 0)
                {
                    string line = reader.ReadLine();
                    if (!string.IsNullOrWhiteSpace(line))
                    {
                        intermediateStringBuilder.AppendLine(line);
                    }
                    else
                    {
                        WriteToFile(intermediateStringBuilder, writer);
                    }
                } 
                WriteToFile(intermediateStringBuilder,writer);
            }
        }
    }

    private static void WriteToFile(StringBuilder intermediateStringBuilder, StreamWriter writer)
    {
        Match m = reg.Match(intermediateStringBuilder.ToString());
        writer.WriteLine("{0}|{1}|{2}|{3}", m.Groups["id"].Value, m.Groups["name"].Value, m.Groups["email"].Value, m.Groups["description"].Value);
        intermediateStringBuilder.Clear();
    }
}