none
read formatted text file separated by pipe by console application and recognise table name, columns and data -then insert values in sql table RRS feed

  • Question

  • Hi Friends,

    How to read this text file ?

    FYR i highlighted yellow->table name,green->colum name,blue->data

    CONTENT_TYPE = HCC_CONFIG_BITS-------------------àTable name

    HCC_ASSY_PART_NUMBER|BIT_ID|HCC_API_PIN_CONNECTION|HCC_BEARING_TYPE|HCC_CENTER_JET|HCC_CUTTING_STRUCTURE|HCC_DRILLABLE_SIZE|HCC_FIXED_TFA|HCC_GAUGE_LENGTH|HCC_GAUGE_ROW|HCC_GAUGE_TRIMMERS|HCC_HEEL_ROW|HCC_HYDRAULIC_FLOW_RATE|HCC_IADC|HCC_INNER_ROW|HCC_JUNK_SLOT_AREA|HCC_MAKEUP_TORQUE|HCC_NOZZLE_TYPE|HCC_NUM_OF_BLADES|HCC_NUM_OF_NOZZLES|HCC_OD_HARDFACING|HCC_PASS_THROUGH_SIZE|HCC_PILOT_BIT_SIZE|HCC_PRIMARY_CUTTING_SIZE|HCC_ROTATION_SPEED|HCC_STANDARD_GAUGE_LENGTH|HCC_TOTAL_NUM_OF_CUTTERS|HCC_WEIGHT_ON_BIT|HCC_NOZZLE_INSTALL_KIT_ID|HCC_BIT_SIZE|HCC_SHANK_ID|SIDE_IMAGE|SIDE_IMAGE_WIDTH|SIDE_IMAGE_HEIGHT|FACE_IMAGE|FACE_IMAGE_WIDTH|FACE_IMAGE_HEIGHT|HCC_BIT_WEIGHT|HCC_BIT_DISPLAY|HCC_TOOTH_HARDFACING|HCC_CENTER_JET_DISPLAY|HCC_SEAL|HCC_APPLICATION_BULLET|HCC_BIT_BREAKER|HCC_DIA_STATUS_ID|HCC_TOP_IMAGE|HCC_TOP_IMAGE_HEIGHT|HCC_TOP_IMAGE_WIDTH|HCC_PART_NUMBER_PDF-------------------àColumn name

    301E18390|DIA:EZB304|3-1/2 API REG|N/A|N/A|304|6.75 in. (171.5 mm.)|0|4|N/A|N/A|N/A|Contact Baker Hughes|M243|N/A|7.131|5.2 - 8.5 kft-lb (7.1 - 11.5 kNm)|N/A|4|4|N/A|0|0|.323|N/A|2.5|54, ~44|15 klb (6 tn or kdaN)|DIA:003002081|6.75|123224621|pdc_not_available.jpg|250|375|hccbitfeatures.jpg|125|125|88.32 lb|EZB304(3.500)|N/A|N/A|N/A|N/A|123223323,123267509,123202125,123232836||N/A|-1|-1|N/A

    A51000287|DIA:D331|2-3/8 API REG|N/A|N/A|331|3.75 in. (95.3 mm.)|.25|-10000000|N/A|N/A|N/A|100 - 200 gpm (400 - 750 lpm)|M713|N/A|0|1.8 - 3.4 kft-lb (2.4 - 4.6 kNm)|N/A|9|-1|N/A|0|0|0|For Turbine and High Speed Rotary/Motor Applications|2|N/A|10 klb (5 tn or kdaN)|N/A|3.75|123203914|03.625d331geb_tcw220253.jpg|250|375|dia_natural_dia_gauge.jpg|125|125|50 lb|D331GEB|N/A|N/A|N/A|N/A|N/A||N/A|-1|-1|N/A------------------------àData

    Further tell after creating connection string how to insert this data in sql db table -HCC_Config_bit


    sudhanshu sharma Do good and cast it into river :)

    Thursday, March 20, 2014 12:17 PM

Answers

  • got o/p by below code-

    namespace CA_ToInsertColValues
    {
        class InsertColValues
        {
            static void Main(string[] args)
            {
                       
                string[] lines = System.IO.File.ReadAllLines(@"D:\Sudhanshu\NewText.txt");
                System.Console.WriteLine("Contents of text file = ");
                //table name needs to extract after = sign
                string tablename = lines[0].Split(new char[] {'='})[1];
                DataTable dt = new DataTable(tablename);
                // Column names need to extract after pipe |
                string [] arrColumns =  lines[1].Split(new char[] { '|' });
                Console.Write("COLUMNS :: ");
                foreach (string col in arrColumns)
                {
                   // Console.Write(col);
                    dt.Columns.Add(col);
                }
                Console.WriteLine(" ");
                //For values which starts from line 2 after table name and column name
                for (int i = 2; i < lines.Length; i++ )
                {            
                     {
                        string[] columnsvals = lines[i].Split(new char[] { '|' });
                       // Console.Write("VALUES :: ");
                        
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < columnsvals.Length;j++) 
                        {
                            Console.Write(columnsvals[j]);
                            if (string.IsNullOrEmpty(columnsvals[j]))
                                dr[j] = DBNull.Value;
                            else
                                dr[j] = columnsvals[j];
                                                      
                        }
                        dt.Rows.Add(dr);
                    }
                                  
                }
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PUNITP255214D\\LUCKY;" + "Initial Catalog=POC_Sud;" + "Integrated Security=SSPI;";
                conn.Open();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
                bulkcopy.DestinationTableName = dt.TableName;
                bulkcopy.WriteToServer(dt);
                conn.Close();
                // Keep the console window open in debug mode.
                Console.WriteLine("Press any key to exit.");
                System.Console.ReadKey();
            }


    sudhanshu sharma Do good and cast it into river :)

    Monday, March 24, 2014 12:44 PM

All replies

  • Find Out the seperation between Tablename, Column name string and Data string

    For the column name and data you can split those strings with below code

    string fullColumnNameString= "col1|col2|col3";//put your column name string delimited by | here string[] ListColumnNames = fullColumnNameString.Split('|'); string fullDataString= "data1|data2|data3";//put your data string delimited by | here string[] ListDataValues = fullDataString.Split('|'); foreach (var item in ListColumnNames) { //put a datarow with with key = columname and value = data

    //or create a sql query with column names and values }



    Regards,
    Vaibhav Tikam
    http://codepinch.blogspot.com
    Whenever you see a reply and if you think is helpful, click "Vote As Helpful"! And whenever you see a reply being an answer to the question of the thread, click "Mark As Answer". It'll help other users who are visiting your thread for the similar problem

    Thursday, March 20, 2014 1:05 PM
  • got o/p by below code-

    namespace CA_ToInsertColValues
    {
        class InsertColValues
        {
            static void Main(string[] args)
            {
                       
                string[] lines = System.IO.File.ReadAllLines(@"D:\Sudhanshu\NewText.txt");
                System.Console.WriteLine("Contents of text file = ");
                //table name needs to extract after = sign
                string tablename = lines[0].Split(new char[] {'='})[1];
                DataTable dt = new DataTable(tablename);
                // Column names need to extract after pipe |
                string [] arrColumns =  lines[1].Split(new char[] { '|' });
                Console.Write("COLUMNS :: ");
                foreach (string col in arrColumns)
                {
                   // Console.Write(col);
                    dt.Columns.Add(col);
                }
                Console.WriteLine(" ");
                //For values which starts from line 2 after table name and column name
                for (int i = 2; i < lines.Length; i++ )
                {            
                     {
                        string[] columnsvals = lines[i].Split(new char[] { '|' });
                       // Console.Write("VALUES :: ");
                        
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j < columnsvals.Length;j++) 
                        {
                            Console.Write(columnsvals[j]);
                            if (string.IsNullOrEmpty(columnsvals[j]))
                                dr[j] = DBNull.Value;
                            else
                                dr[j] = columnsvals[j];
                                                      
                        }
                        dt.Rows.Add(dr);
                    }
                                  
                }
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=PUNITP255214D\\LUCKY;" + "Initial Catalog=POC_Sud;" + "Integrated Security=SSPI;";
                conn.Open();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
                bulkcopy.DestinationTableName = dt.TableName;
                bulkcopy.WriteToServer(dt);
                conn.Close();
                // Keep the console window open in debug mode.
                Console.WriteLine("Press any key to exit.");
                System.Console.ReadKey();
            }


    sudhanshu sharma Do good and cast it into river :)

    Monday, March 24, 2014 12:44 PM