We have an application where the data in Excel file (present in shared path) moves to Database. In case of any error, the files moves to error folder by writing the error in a log file.It uses a windows service for the operation.
Sometimes the file doesn't have any error still moves to error folder by writing log External table is not in the expected format. But the same file uploading again for once or multiple times, its moving to Database without any errors.
The windows service, DB and shared path are present in XP Server. Application was running fine all these years. But in the recent days, above mentioned problem is occurring for almost every file.
We have installed Microsoft 2003, 2007,2012 office components and access engines too. But still the issue still persists.
I am mentioning the Windows service code below. Pls help. Thanks in advance.
using System.IO;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;
namespace Impexp_Service
{
public partial class Service1 : ServiceBase
{
System.Timers.Timer T1 = new System.Timers.Timer();
public Service1()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
{
SqlConnection strconnection = new SqlConnection();
strconnection.ConnectionString = @"Data Source=XXXXXX;Initial Catalog=XXXX;User ID=XX;Password=XXXXXX;";
strconnection.Open();
DirectoryInfo directory = new DirectoryInfo(@"D:\Impexp\Data\");
FileInfo[] files = directory.GetFiles("*.xlsx");
foreach (var f in files)
{
string path = f.FullName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
DbDataReader dr = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
sqlBulk.DestinationTableName = "imp_master_test";
sqlBulk.WriteToServer(dr);
excelConnection.Close();
File.Delete(path);
T1.Interval = 20000;
T1.Enabled = true;
T1.Start();
T1.Elapsed += new System.Timers.ElapsedEventHandler(T1_Elapsed);
}
}
}
void T1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
T1.Enabled = false;
try
{
SqlConnection strconnection = new SqlConnection();
strconnection.ConnectionString = @"Data Source=10.91.XXXXXX;Initial Catalog=XXXXX;User ID=XXXXX;Password=XXXXX;";
strconnection.Open();
DirectoryInfo directory = new DirectoryInfo(@"D:\Impexp\Data\");
FileInfo[] files = directory.GetFiles("*.xlsx");
foreach (var f in files)
{
string path = f.FullName;
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
try
{
excelConnection.Open();
OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
DbDataReader dr = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
sqlBulk.DestinationTableName = "imp_master_prod";
sqlBulk.WriteToServer(dr);
excelConnection.Close();
File.Delete(path);
}
catch (Exception exp)
{
excelConnection.Close();
File.Move(path, Path.Combine(@"D:\Impexp\error\", f.Name));
string path1 = @"D:\Impexp\error\error.txt";
if (File.Exists(path1))
{
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine("File : " + path + " : " + exp.Message);
sw.Flush();
}
}
T1.Enabled = true;
T1.Start();
}
}
strconnection.Close();
}
catch (UnauthorizedAccessException UAEx)
{
string path1 = @"D:\Impexp\error\error.txt";
if (File.Exists(path1))
{
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine(UAEx.Message);
sw.Flush();
}
}
T1.Enabled = true;
T1.Start();
}
catch (PathTooLongException PathEx)
{
string path1 = @"D:\Impexp\error\error.txt";
if (File.Exists(path1))
{
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine(PathEx.Message);
sw.Flush();
}
}
T1.Enabled = true;
T1.Start();
}
T1.Enabled = true;
T1.Start();
}
protected override void OnStop()
{
}
}
}
What I have tried:
We have installed Microsoft 2003, 2007,2012 office components and access engines too. But still the issue still persists.