gpt4 book ai didi

c# - 某些 Excel 文件未从共享路径移动到 SQL Server

转载 作者:可可西里 更新时间:2023-11-01 08:50:06 25 4
gpt4 key购买 nike

我们有一个应用程序,其中 Excel 文件中的数据(存在于共享路径中)移动到数据库。如果出现任何错误,文件会通过将错误写入日志文件来移动到错误文件夹。它使用 Windows 服务进行操作。

有时文件没有任何错误仍然通过写入日志移动到错误文件夹 External table is not in the expected format. 但是同一个文件再次上传一次或多次,它移动到数据库没有任何错误。

Windows 服务、数据库和共享路径存在于 XP Server 中。这些年来应用程序运行良好。但是最近几天,几乎每个文件都出现了上述问题。

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

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

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()
{
}
}
}

最佳答案

我对 OLEDB com 和更新版本的 Excel 进行了一些搜索。似乎很多人都遇到了兼容性问题。

不幸的是,微软似乎并没有对此给予任何关注。 Microsoft 多年前就宣布 OLEDB 功能的贬值,并且他们已经停止在其 Office 产品和 SQL 服务器中添加任何类型的内部支持。实际上,MSAccess Web Apps and Web Databases 的官方关闭日期是 2018 年 4 月。既然如此,服务器更新、Windows 客户端版本或 Excel 客户端版本可能会触发此事件,而且看起来不像会有一个修复。我自己已经开始使用 3rd 方包(免费包可用)来处理与办公产品的互操作,因为我厌倦了用头撞墙来创建解决方法。老实说,如果 Access 取消了以编程方式连接到 Access 数据库的能力,我不知道为什么 Access 仍然存在。

我知道这不能解决您的问题,但面对事实并继续前进比尝试解决无法解决的问题要好。

关于c# - 某些 Excel 文件未从共享路径移动到 SQL Server,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38120279/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com