Formatting formatted file for insert into Database[Answered] RSS

• • Reply
  bbcompent1

  All-Star

  35218 Points

  9955 Posts

  Moderator

  Formatting formatted file for insert into Database

  Jun 22, 2017 04:22 PM|bbcompent1|LINK

  Hello folks, I have an export file that gives me records in the following format:

  Project: Test Project - Version: Test Project - version 1      
  ==============================================                                  
  Developer ID: CN=Doe\, John,OU=OU1,OU=Tempworkers,OU=OU2,OU=OU3,OU=OU4,DC=mytest,DC=domain,DC=com

  What I would like to do is turn this into a data table that can be inserted into the database. I'm imagining the schema to look like this:

  Last_Name | First_Name | PVId
  ------------------------------
  Doe       | John       | 123

  I have a corresponding table with the Project Version Name with a PVId column.

  I can figure the matching part out, what I need help with is parsing the data into those fields. Any thoughts? Thanks all!

  Mark as answer posts that helped you.
  
• • Reply
  ryanbesko

  Contributor

  7048 Points

  2189 Posts

  Re: Formatting formatted file for insert into Database

  Jun 22, 2017 04:59 PM|ryanbesko|LINK

  bbcompent1

  Hello folks, I have an export file that gives me records in the following format:

  Project: Test Project - Version: Test Project - version 1      
  ==============================================                                  
  Developer ID: CN=Doe\, John,OU=OU1,OU=Tempworkers,OU=OU2,OU=OU3,OU=OU4,DC=mytest,DC=domain,DC=com

  What I would like to do is turn this into a data table that can be inserted into the database. I'm imagining the schema to look like this:

  Last_Name | First_Name | PVId
  ------------------------------
  Doe       | John       | 123

  I have a corresponding table with the Project Version Name with a PVId column.

  I can figure the matching part out, what I need help with is parsing the data into those fields. Any thoughts? Thanks all!

  You will need to add a project reference to Microsoft.VisualBasic.FileIO.

  using Microsoft.VisualBasic.FileIO;
  using System;
  using System.Data;
  
  namespace ConsoleApp1
  {
      class Program
      {
          static void Main(string[] args)
          {
              using (var dt = new DataTable())
              {
                  #region Column Definitions
                  dt.Columns.Add("Last_Name", typeof(string));
                  dt.Columns.Add("First_Name", typeof(string));
                  dt.Columns.Add("PVid", typeof(int));
                  #endregion
  
                  // Change to your path, or use Server.MapPath, etc.  Can change to MemoryStream object, too.
                  using (var parser = new TextFieldParser(@"C:\test.csv"))
                  {
                      parser.Delimiters = new string[] { "," };
                      parser.HasFieldsEnclosedInQuotes = false;
                      parser.TrimWhiteSpace = true;
  
                      parser.ReadLine(); // Skip the Project info row.
                      parser.ReadLine(); // Skip the header row.
  
                      string[] rowdata; // One row of data
                      DataRow dr; // For storing parsed fields to add to DataTable later.
                      int i; // Index of the = sign in first field.
  
                      while (!parser.EndOfData)
                      {
                          rowdata = parser.ReadFields();
                          i = rowdata[0].ToString().IndexOf("=");
  
                          dr = dt.NewRow();
                          dr["Last_Name"] = rowdata[0].ToString().Substring(i + 1, rowdata[0].Length - i - 2);
                          dr["First_Name"] = rowdata[1].ToString();
                          dr["PVid"] = 123; // Something here to get your PVid
                          dt.Rows.Add(dr);
                      }
  
                      foreach(DataRow row in dt.Rows)
                      {
                          Console.WriteLine(row[0].ToString() + "|" + row[1].ToString() + "|" + row[2].ToString());
                      }
  
                      Console.ReadLine();
                  }
              }
          }
      }
  }
  

  Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
• • Reply
  bbcompent1

  All-Star

  35218 Points

  9955 Posts

  Moderator

  Re: Formatting formatted file for insert into Database

  Jun 23, 2017 10:37 AM|bbcompent1|LINK

  Ok Ryan, I'll try this and get back to you. Thank you.

  Mark as answer posts that helped you.
  
• • Reply
  bbcompent1

  All-Star

  35218 Points

  9955 Posts

  Moderator

  Re: Formatting formatted file for insert into Database

  Jun 28, 2017 12:51 PM|bbcompent1|LINK

  ryanbesko

  You will need to add a project reference to Microsoft.VisualBasic.FileIO.

  using Microsoft.VisualBasic.FileIO;
  using System;
  using System.Data;

  This worked extremely well, really appreciate your time and work on this! I can take this code and turn it into a windows service that will convert when a new import file appears and have it check for the new file every hour or so.

  Mark as answer posts that helped you.