locked
Formatting formatted file for insert into Database RRS feed

  • Question

  • User-718146471 posted

    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!

    Thursday, June 22, 2017 4:22 PM

Answers

  • User2053451246 posted

    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(); } } } } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 22, 2017 4:59 PM

All replies

  • User2053451246 posted

    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(); } } } } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 22, 2017 4:59 PM
  • User-718146471 posted

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

    Friday, June 23, 2017 10:37 AM
  • User-718146471 posted

    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.

    Wednesday, June 28, 2017 12:51 PM