某些Excel文件未从共享路径移动到SQL Server [英] Some excel files not moving from shared path to SQL server

问题描述

我们有一个应用程序，其中Excel文件(位于共享路径中)中的数据移至数据库.如果发生任何错误，则通过将错误写入日志文件来将文件移动到错误文件夹，它使用Windows服务进行操作.

有时通过写日志文件没有任何错误仍然会移到错误文件夹，外部表的格式不正确.但是同一文件再次上载一次或多次，将其无任何错误地移至数据库.

Windows Server，DB和共享路径位于XP Server中.这些年来，应用程序运行良好.但是最近几天，几乎每个文件都出现了上述问题.

我们还安装了Microsoft 2003、2007、2012办公组件和访问引擎.但是问题仍然存在.

我在下面提到Windows服务代码.请帮助.提前谢谢.

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)
          {
            ///start
            ///

            {
                SqlConnection strconnection = new SqlConnection();
                strconnection.ConnectionString = @"Data Source=XXXXXX;Initial Catalog=XXXX;User ID=XX;Password=XXXXXX;";
                strconnection.Open();
                // To get the all files placed at the shared path
                DirectoryInfo directory = new DirectoryInfo(@"D:\Impexp\Data\");
                FileInfo[] files = directory.GetFiles("*.xlsx");



                foreach (var f in files)
                {
                    string path = f.FullName;

                    // TO establish connection to the excel sheet
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
                    //Create Connection to Excel work book
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);



                    excelConnection.Open();
                    //Create OleDbCommand to fetch data from Excel
                    OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);

                    DbDataReader dr = cmd.ExecuteReader();
                    // OleDbDataReader dReader;
                    // dReader = cmd.ExecuteReader();
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
                    //Give your Destination table name
                    sqlBulk.DestinationTableName = "imp_master_test";
                    sqlBulk.WriteToServer(dr);

                    excelConnection.Close();

                    File.Delete(path);




                    // To move error files to the error folder



                    /// end


                    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();
                // To get the all files placed at the shared path
                DirectoryInfo directory = new DirectoryInfo(@"D:\Impexp\Data\");
                FileInfo[] files = directory.GetFiles("*.xlsx");



                foreach (var f in files)
                {
                    string path = f.FullName;

                    // TO establish connection to the excel sheet
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
                    //Create Connection to Excel work book
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                try
                {
                    excelConnection.Open();
                    //Create OleDbCommand to fetch data from Excel
                    OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);

                    DbDataReader dr = cmd.ExecuteReader();
                    // OleDbDataReader dReader;
                    // dReader = cmd.ExecuteReader();
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
                    //Give your Destination table name
                    sqlBulk.DestinationTableName = "imp_master_prod";
                    sqlBulk.WriteToServer(dr);

                    excelConnection.Close();

                    File.Delete(path);



                }
                // To move error files to the error folder
                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))
                    {
                        // Create a file to write to. 
                        using (StreamWriter sw = File.AppendText(path1))
                        {
                            sw.WriteLine("File : " + path + " : " + exp.Message);
                            sw.Flush();

                        }
                    }


                        T1.Enabled = true;
                        T1.Start();

                    }
                }
                strconnection.Close();

            // End of TRY 1

            }
            catch (UnauthorizedAccessException UAEx)
            {
                string path1 = @"D:\Impexp\error\error.txt";
                if (File.Exists(path1))
                {
                    // Create a file to write to. 
                    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))
                {
                    // Create a file to write to. 
                    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()
        {
        }
    }
}

我尝试过的事情:

我们还安装了Microsoft 2003、2007、2012办公组件和访问引擎.但是问题仍然存在.

What I have tried:

We have installed Microsoft 2003, 2007,2012 office components and access engines too. But still the issue still persists.

推荐答案

"，excelConnection); DbDataReader dr = cmd.ExecuteReader(); // OleDbDataReader dReader; // dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = 新 SqlBulkCopy(strconnection); // 提供目标表名称 sqlBulk.DestinationTableName = " ; sqlBulk.WriteToServer(dr); excelConnection.Close(); File.Delete(路径); // 要将错误文件移至错误文件夹 /// 结束 T1.Interval = 20000 ; T1.Enabled = true ; T1.Start(); T1.Elapsed + = 新 System.Timers.ElapsedEventHandler(T1_Elapsed); } } } 无效 T1_Elapsed(对象发​​送者，System.Timers.ElapsedEventArgs e) { T1.Enabled = false ; 尝试 { SqlConnection strconnection = 新 SqlConnection(); strconnection.ConnectionString = @" ; strconnection.Open(); // 获取所有放置在共享路径中的文件 DirectoryInfo目录= 新建 DirectoryInfo( @" ); FileInfo [] files = directory.GetFiles(" ); foreach ((文件中的 var f 文件)) { 字符串 path = f.FullName; // 建立与Excel工作表的连接 字符串 excelConnectionString = @" +路径+ ;扩展属性= \"Excel 12.0; HDR =是； IMEX = 1 \;" ; // 创建与Excel工作簿的连接 OleDbConnection excelConnection = 新 OleDbConnection(excelConnectionString); 尝试 { excelConnection.Open(); // 创建OleDbCommand以便从Excel中获取数据 OleDbCommand cmd = 新 OleDbCommand("

", excelConnection); DbDataReader dr = cmd.ExecuteReader(); // OleDbDataReader dReader; // dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection); //Give your Destination table name sqlBulk.DestinationTableName = "imp_master_test"; sqlBulk.WriteToServer(dr); excelConnection.Close(); File.Delete(path); // To move error files to the error folder /// end 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(); // To get the all files placed at the shared path DirectoryInfo directory = new DirectoryInfo(@"D:\Impexp\Data\"); FileInfo[] files = directory.GetFiles("*.xlsx"); foreach (var f in files) { string path = f.FullName; // TO establish connection to the excel sheet string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";"; //Create Connection to Excel work book OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); try { excelConnection.Open(); //Create OleDbCommand to fetch data from Excel OleDbCommand cmd = new OleDbCommand("Select * from [Report

"，excelConnection)； DbDataReader dr = cmd.ExecuteReader(); // OleDbDataReader dReader; // dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = 新 SqlBulkCopy(strconnection); // 提供目标表名称 sqlBulk.DestinationTableName = " ; sqlBulk.WriteToServer(dr); excelConnection.Close(); File.Delete(路径); } // 要将错误文件移至错误文件夹 捕获(异常exp) { excelConnection.Close(); File.Move(path，Path.Combine( @" ，f .姓名)); 字符串 path1 = @" ; 如果(File.Exists(path1)) { // 创建要写入的文件. 使用(StreamWriter sw = File.AppendText(path1)) { sw.WriteLine(" + path + " :" + exp.Message)； sw.Flush(); } } T1.Enabled = true ; T1.Start(); } } strconnection.Close(); // TRY 1结束 } 捕获(UnauthorizedAccessException UAEx) { 字符串 path1 = @" ; 如果(File.Exists(path1)) { // 创建要写入的文件. 使用(StreamWriter sw = File.AppendText(path1)) { sw.WriteLine(UAEx.Message); sw.Flush(); } } T1.Enabled = true ; T1.Start(); } 捕获(PathTooLongException PathEx) { 字符串 path1 = @" ; 如果(File.Exists(path1)) { // 创建要写入的文件. 使用(StreamWriter sw = File.AppendText(path1)) { sw.WriteLine(PathEx.Message); sw.Flush(); } } T1.Enabled = true ; T1.Start(); } T1.Enabled = true ; T1.Start(); } 受保护的 覆盖 void OnStop() { } } }

", excelConnection); DbDataReader dr = cmd.ExecuteReader(); // OleDbDataReader dReader; // dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection); //Give your Destination table name sqlBulk.DestinationTableName = "imp_master_prod"; sqlBulk.WriteToServer(dr); excelConnection.Close(); File.Delete(path); } // To move error files to the error folder 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)) { // Create a file to write to. using (StreamWriter sw = File.AppendText(path1)) { sw.WriteLine("File : " + path + " : " + exp.Message); sw.Flush(); } } T1.Enabled = true; T1.Start(); } } strconnection.Close(); // End of TRY 1 } catch (UnauthorizedAccessException UAEx) { string path1 = @"D:\Impexp\error\error.txt"; if (File.Exists(path1)) { // Create a file to write to. 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)) { // Create a file to write to. 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() { } } }

我尝试过的事情:

我们还安装了Microsoft 2003、2007、2012办公组件和访问引擎.但是问题仍然存在.

What I have tried:

We have installed Microsoft 2003, 2007,2012 office components and access engines too. But still the issue still persists.

这篇关于某些Excel文件未从共享路径移动到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！