Solved

how to import comma separated text file in sql table using c#

Posted on 2015-02-04
11
436 Views
Last Modified: 2015-02-14
I need to write a console application using c#  to import a comma separated text file into sql table.
If the sql table already has lien code, It should update the record else it should insert a new row.
 I am attaching the text file.  I have added column names for your reference. The first column is a fixed lenght of 52 characters and the remaining are comma separated.
lien.txt
0
Comment
Question by:Meinhoonaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40589618
There is no automatic way that I know of that will take a comma separated file and do what you ant it to do. You will need to parse the lines place them into variables and build SQL statements to insert or update the tables.
0
 
LVL 9

Expert Comment

by:dustock
ID: 40589677
I had this block of code from an old homework assignment.  Since I'm not sure how your going about connecting to your database and inserting records or checking if records exist I have stripped most of that out.  If you need help with that let me know what you have already.

syntax to run this would be:

c:\yourfilepath\programname.exe -F pathtofiletoimport
eg
c:\users\user1\Desktop\Program.exe -F c:\temp\data.csv

        static void Main(string[] args)
        {
            String strFile = "";
            StreamReader srData = null;
            String strRecord;
            String[] strFields;
            
            //Create variables to hold the data
            String strBusinessName = "";

            // Check for parameter list
            if (args.Length < 1)
            {
                Console.WriteLine("Filename parameter is missing");
                Environment.Exit(-1);
            }

            // Validate filename parameter
            for (intIdx = 0; intIdx < args.Length; intIdx++)
            {
                if (args[0].ToString().StartsWith("-F"))
                {
                    strFile = args[0].ToString().Substring(2);
                }
            }

                srData = new StreamReader(strFile);

                //Open a connection to your database

                while (srData .Peek() > 0)
                {
                    strRecord = srEmp.ReadLine();
                    strFields = strRecord.Split(",".ToCharArray());
                    Int32 intFieldCount = strFields.Count();

                    //Check to see if you have the right number of fields
                    if (strFields.Length == 11) //This was the number of fields I counted from your sample file
                    {
                        //Validate fields in the record
                        Boolean insertRecs = true;

                        //Do any checking you want here, make insertRecs false if the error checking fails
			if ()
				strBusinessName = strFields[0].ToString();
			else
				insertRecs = false;

                        if (insertRecs == true)
                        {
                            //If the record doesnt exist, insert a new one
                            if () //What ever code you have to check if the record exists
                            {
				//Build up your insert statement using the variables you've assigned the data to
			    }
                        }
                    }
                    else
                    {
                        //Display error
                    }
                    Console.WriteLine();
                }
		//Close and dispose of your database connection and StreamReader
                cnSQL.Close();
                cnSQL.Dispose();
                srEmp.Close();
                srEmp.Dispose();
           }

Open in new window

0
 

Author Comment

by:Meinhoonaa
ID: 40593933
I have written a program to insert the records from the text file to the sql table. However, I need help to set the is_active status to 'N' for the existing records before I insert the new records. My code shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using StateLienholdersReader.Common;


namespace StateLienholderReader
{
    class Program
    {
        static void Main(string[] args)
        {
            string file = @"c:\SC\liens.txt";
            //string[] sourceFiles = Directory.GetFiles(ConfigurationManager.AppSettings["sc_file_path"], "*.txt");

            List<string> list = new List<string>();
            using (StreamReader reader = new StreamReader(file))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    list.Add(line); // Add to list.
                    //Console.WriteLine(line); // Write to console.
                }
            }
       
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["sc_DSN"]))
            {  
                string sql = "INSERT INTO sc_lienholder(lienholder_code,name,address,address2,city,state,zipcode,business_type,is_active,insert_dtim,insert_person) VALUES(@lienholder_code,@name,@address,@address2,@city,@state,@zipcode,@business_type,@is_active,@insert_dtim,@insert_person)";
               
                // define the SqlCommmand to do the insert - use the using() approach again  
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                        cn.Open();                      

                        for (int index = 0; index < list.Count; index++)
                        {
                            string[] items = list[index].Substring(53).Split(new char[] { ',' });
                            string item = list[index].Substring(0, 51);
                            cmd.Parameters.AddWithValue("@name", item.Replace("'",""));
                            cmd.Parameters.AddWithValue("@address", items[4]);
                            cmd.Parameters.AddWithValue("@address2", items[5]);
                            cmd.Parameters.AddWithValue("@city", items[6]);
                            cmd.Parameters.AddWithValue("@state", items[7]);
                            cmd.Parameters.AddWithValue("@zipcode", items[8]);                          
                            cmd.Parameters.AddWithValue("@lienholder_code", items[9]);
                            cmd.Parameters.AddWithValue("@business_type", items[10].Trim());
                            cmd.Parameters.AddWithValue("@is_active", "Y");                            
                            cmd.Parameters.AddWithValue("@insert_dtim", DateTime.Now);
                            cmd.Parameters.AddWithValue("@insert_person", "Batch Job");
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        cn.Close();
                    }
                }
            }
        }
    }
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 9

Expert Comment

by:dustock
ID: 40593972
I'm not sure I understand the question.  What records are you looking to set is_active status to 'N'?  Are you trying to set any existing items in the table to is_active = 'N' or are there specific records you want to set to is_active = 'N'?
0
 

Author Comment

by:Meinhoonaa
ID: 40594014
Never mind, I fugured it out.
0
 
LVL 9

Expert Comment

by:dustock
ID: 40594044
Awesome
0
 

Author Comment

by:Meinhoonaa
ID: 40599403
how do I continue inserting into the sql table if there is error. code below:

 using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        cn.Open();
                        for (int index = 0; index < list.Count; index++)
                            try
                            {
                                string[] items = list[index].Substring(53).Split(new char[] { ',' });
                                string item = list[index].Substring(0, 51);
                                cmd.Parameters.AddWithValue("@name", item.Replace("'", ""));
                                cmd.Parameters.AddWithValue("@address", items[4]);
                                cmd.Parameters.AddWithValue("@address2", items[5]);
                                cmd.Parameters.AddWithValue("@city", items[6]);
                                cmd.Parameters.AddWithValue("@state", items[7]);
                                cmd.Parameters.AddWithValue("@zipcode", items[8]);
                                cmd.Parameters.AddWithValue("@lienholder_code", items[9]);
                                cmd.Parameters.AddWithValue("@business_type", items[10].Trim());
                                cmd.Parameters.AddWithValue("@is_active", "Y");
                                cmd.Parameters.AddWithValue("@insert_dtim", DateTime.Now);
                                cmd.Parameters.AddWithValue("@insert_person", "Batch Job");
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                                Logger.Instance.LogEvent(String.Format("{0} lienholders copied", index));
                            }
                            catch (Exception ex)
                            {
                                Logger.Instance.LogException(String.Format("Exception: {0}", ex.Message), ex);
                                Emailer.SendMail(ex.Message, "Error in South Carolina Lien Console Application");
                            }

                        {
                            cn.Close();
                        }
0
 
LVL 9

Expert Comment

by:dustock
ID: 40599411
You could wrap it in another try/catch block

 using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        cn.Open();
                        for (int index = 0; index < list.Count; index++)
                            try
                            {
                                try
                                {
                                string[] items = list[index].Substring(53).Split(new char[] { ',' });
                                string item = list[index].Substring(0, 51);
                                cmd.Parameters.AddWithValue("@name", item.Replace("'", ""));
                                cmd.Parameters.AddWithValue("@address", items[4]);
                                cmd.Parameters.AddWithValue("@address2", items[5]);
                                cmd.Parameters.AddWithValue("@city", items[6]);
                                cmd.Parameters.AddWithValue("@state", items[7]);
                                cmd.Parameters.AddWithValue("@zipcode", items[8]);
                                cmd.Parameters.AddWithValue("@lienholder_code", items[9]);
                                cmd.Parameters.AddWithValue("@business_type", items[10].Trim());
                                cmd.Parameters.AddWithValue("@is_active", "Y");
                                cmd.Parameters.AddWithValue("@insert_dtim", DateTime.Now);
                                cmd.Parameters.AddWithValue("@insert_person", "Batch Job");
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                                Logger.Instance.LogEvent(String.Format("{0} lienholders copied", index));
                                }
                                catch
                                {
                                      \\Add the errors to a stringbuilder or something else that is global or instantiated outside of this block and then list anything that errored out at the end
                                }
                            }
                            catch (Exception ex)
                            {
                                Logger.Instance.LogException(String.Format("Exception: {0}", ex.Message), ex);
                                Emailer.SendMail(ex.Message, "Error in South Carolina Lien Console Application");
                            }

                        {
                            cn.Close();
                        }

Open in new window

0
 

Accepted Solution

by:
Meinhoonaa earned 0 total points
ID: 40599439
never mind, figured it out
0
 
LVL 9

Expert Comment

by:dustock
ID: 40599569
If you figured it out can you please post your solution.  The idea behind this side is for users to not only post question but to be able to look at answered questions to see if there is a solution that can help them.  When you accept "Never mind, I figured it out" as the solution it doesn't help other users on this site.

Thank you
0
 

Author Closing Comment

by:Meinhoonaa
ID: 40609548
ccc
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question