Meinhoonaa
asked on
how to import comma separated text file in sql table using c#
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
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
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.
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\programnam e.exe -F pathtofiletoimport
eg
c:\users\user1\Desktop\Pro gram.exe -F c:\temp\data.csv
syntax to run this would be:
c:\yourfilepath\programnam
eg
c:\users\user1\Desktop\Pro
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();
}
ASKER
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.Com mon;
namespace StateLienholderReader
{
class Program
{
static void Main(string[] args)
{
string file = @"c:\SC\liens.txt";
//string[] sourceFiles = Directory.GetFiles(Configu rationMana ger.AppSet tings["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(Configuratio nManager.A ppSettings ["sc_DSN"] ))
{
string sql = "INSERT INTO sc_lienholder(lienholder_c ode,name,a ddress,add ress2,city ,state,zip code,busin ess_type,i s_active,i nsert_dtim ,insert_pe rson) VALUES(@lienholder_code,@n ame,@addre ss,@addres s2,@city,@ state,@zip code,@busi ness_type, @is_active ,@insert_d tim,@inser t_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.AddWithValu e("@name", item.Replace("'",""));
cmd.Parameters.AddWithValu e("@addres s", items[4]);
cmd.Parameters.AddWithValu e("@addres s2", items[5]);
cmd.Parameters.AddWithValu e("@city", items[6]);
cmd.Parameters.AddWithValu e("@state" , items[7]);
cmd.Parameters.AddWithValu e("@zipcod e", items[8]);
cmd.Parameters.AddWithValu e("@lienho lder_code" , items[9]);
cmd.Parameters.AddWithValu e("@busine ss_type", items[10].Trim());
cmd.Parameters.AddWithValu e("@is_act ive", "Y");
cmd.Parameters.AddWithValu e("@insert _dtim", DateTime.Now);
cmd.Parameters.AddWithValu e("@insert _person", "Batch Job");
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
cn.Close();
}
}
}
}
}
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.Com
namespace StateLienholderReader
{
class Program
{
static void Main(string[] args)
{
string file = @"c:\SC\liens.txt";
//string[] sourceFiles = Directory.GetFiles(Configu
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);
}
}
using (SqlConnection cn = new SqlConnection(Configuratio
{
string sql = "INSERT INTO sc_lienholder(lienholder_c
// 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).
string item = list[index].Substring(0, 51);
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
cn.Close();
}
}
}
}
}
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'?
ASKER
Never mind, I fugured it out.
Awesome
ASKER
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.AddWithValu e("@name", item.Replace("'", ""));
cmd.Parameters.AddWithValu e("@addres s", items[4]);
cmd.Parameters.AddWithValu e("@addres s2", items[5]);
cmd.Parameters.AddWithValu e("@city", items[6]);
cmd.Parameters.AddWithValu e("@state" , items[7]);
cmd.Parameters.AddWithValu e("@zipcod e", items[8]);
cmd.Parameters.AddWithValu e("@lienho lder_code" , items[9]);
cmd.Parameters.AddWithValu e("@busine ss_type", items[10].Trim());
cmd.Parameters.AddWithValu e("@is_act ive", "Y");
cmd.Parameters.AddWithValu e("@insert _dtim", DateTime.Now);
cmd.Parameters.AddWithValu e("@insert _person", "Batch Job");
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Logger.Instance.LogEvent(S tring.Form at("{0} lienholders copied", index));
}
catch (Exception ex)
{
Logger.Instance.LogExcepti on(String. Format("Ex ception: {0}", ex.Message), ex);
Emailer.SendMail(ex.Messag e, "Error in South Carolina Lien Console Application");
}
{
cn.Close();
}
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
cn.Open();
for (int index = 0; index < list.Count; index++)
try
{
string[] items = list[index].Substring(53).
string item = list[index].Substring(0, 51);
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Logger.Instance.LogEvent(S
}
catch (Exception ex)
{
Logger.Instance.LogExcepti
Emailer.SendMail(ex.Messag
}
{
cn.Close();
}
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();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thank you
ASKER
ccc