Need to import data from excel to SQL

Question

Need to read excel data from the server folder path and update it in the SQL server .once it is updated need to move to another folder using the C# console application

code:

string value = "read";
string ConStr;
string HDR;
HDR = "YES";
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
    + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(ConStr);
conn.Open();
OleDbCommand newvalues = new OleDbCommand("select * from [" + value + "$]", conn);
OleDbDataAdapter newvalue = new OleDbDataAdapter(newvalues);
DataTable dt = new DataTable();
dt.Fill(dt1);
conn.Close();
DataTable dt = dt1;
SqlCommand cmd = new SqlCommand("spupdate", connect);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter valparam = new SqlParameter("@update", SqlDbType.Structured);
valparam.TypeName = "tablevalue";
valparam.Value = values;
cmd.Parameters.Add(valparam);
cmd.ExecuteNonQuery();
connect.Close();

Naimish Makwana · Answer

Use below code: string datetime = DateTime.Now.ToString("M/d/yyyy"); string fileName = Path.GetFileName(sourceFolderPath); string destination = Path.Combine(destinationFolderPath, "File1_" + datetime + Path.GetExtension(sourceFolderPath)); File.Move(sourceFolderPath, destination);

Jignesh Kumar · Answer

Please try this one, string datetime = DateTime.Now.ToString("dd/MM/yyyy"); string datetime = DateTime.Now.ToString("dd/MM/yyyy"); string fileName = Path.GetFileName(sourceFolderpath); var extension = Path.GetExtension(fileName); var newName = fileName.Replace(extension,"_") + datetime + extension; string destination = Path.Combine(destinationFolderPath, newName ); File.Move(sourceFolderpath, destination);

Naimish Makwana · Answer

Add time stemp in filename. try below string fileName = Path.GetFileName(filePath); string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(filePath); string fileExtension = Path.GetExtension(filePath); // Get the current date and time DateTime now = DateTime.Now; // Create a timestamp string in the format "yyyyMMddHHmmss" string timestamp = now.ToString("yyyyMMddHHmmss"); // Create a new filename with timestamp string newFileName = $"{fileNameWithoutExtension}_{timestamp}{fileExtension}"; // Combine the new filename with the destination folder path string destinationPath = Path.Combine(destinationFolder, newFileName); // Move the file to the destination folder with the new filename File.Move(filePath, destinationPath);

Naimish Makwana · Answer

try the following solutions: Check if the file is open by another user: If the file is being used by another user, you may need to wait for them to close it or ask them to close the file. Alternatively, you can try accessing the file from another computer or workstation. Check file permissions: Ensure that you have the necessary permissions to access the file. You can do this by right-clicking on the file, selecting Properties, and then clicking on the Security tab. If you do not have the necessary permissions, you will need to contact the file owner or administrator to grant you access. Close other applications: Close any other applications that may be using the file, as they may be preventing Microsoft Access from accessing the file. Restart the computer: If none of the above solutions work, try restarting your computer to release any locks on the file. Thanks Naimish

Naimish Makwana · Answer

Hello Meghna, Try below code: string filePath = @"C:\SourceFolder\ExcelFile.xlsx"; // Replace with your file path string destinationFolder = @"C:\DestinationFolder\"; // Replace with your destination folder string sheetName = "Sheet1"; // Replace with your sheet name string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\""; string storedProcedureName = "spUpdate"; // Replace with your stored procedure name // Read Excel data using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", connection); OleDbDataAdapter adapter = new OleDbDataAdapter(command); DataTable table = new DataTable(); adapter.Fill(table); connection.Close(); // Update data in SQL Server using stored procedure using (SqlConnection sqlConnection = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")) { sqlConnection.Open(); SqlCommand sqlCommand = new SqlCommand(storedProcedureName, sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameter = new SqlParameter("@update", SqlDbType.Structured); parameter.TypeName = "tablevalue"; parameter.Value = table; sqlCommand.Parameters.Add(parameter); sqlCommand.ExecuteNonQuery(); sqlConnection.Close(); } // Move file to destination folder string fileName = Path.GetFileName(filePath); string destinationPath = Path.Combine(destinationFolder, fileName); File.Move(filePath, destinationPath); } Thanks Naimish Makwana

Jignesh Kumar · Answer

Helli, Please refer this one for insert excel data to SQL, https://www.c-sharpcorner.com/blogs/import-excel-spreadsheet-data-into-sql-server-table-using-c-sharp Move file once insert operation done, public class SimpleFileMove { static void Main() { string sourceFile = @"C:\Users\Public\public	est.xls"; string destinationFile = @"C:\Users\Public\private	est.xls"; // To move a file or folder to a new location: System.IO.File.Move(sourceFile, destinationFile); // To move an entire directory. To programmatically modify or combine // path strings, use the System.IO.Path class. System.IO.Directory.Move(@"C:\Users\Public\public	est", @"C:\Users\Public\private"); } }

Meghana M · Answer

string datetime = DateTime.Now.ToString("MM-dd-yyyy"); string fileName = Path.GetFileName(sourceFolderpath); string destination = Path.Combine(destinationFolderPath, fileName); File.Move(sourceFolderpath, destination); -DestinationFolder file Output Should be in the below format once it is moved from source to destination File1_12/2/2022.xlsx

Jignesh Kumar · Answer

Get filename and append datetime string to filename. string timestamp =DateTime.Now.ToString("MMddyyyy.HHmmss"); var filepath = "C:\YourPath\YourFileName_"+timestamp+".png";

Meghana M · Answer

how to add a timestamp for the filename once it is moved to the destination folder destination folder should be in this format Eg:filename_datetime.xlsx

Jignesh Kumar · Answer

Hello Meghana, Its permission issue, You can try to put in another drive of your machine.

Meghana M · Answer

I'm getting the below error, I have given the source path, and destination path as the local machine path The Microsoft Access database engine cannot open or write to the file 'C:\servername\filename'. It is already opened exclusively by another user, or you need permission to view and write its data.'

Tuhin Paul · Answer

string value = "read"; string HDR = "YES"; string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; using (OleDbConnection conn = new OleDbConnection(ConStr)) { conn.Open(); OleDbCommand newvalues = new OleDbCommand("select * from [" + value + "$]", conn); OleDbDataAdapter newvalue = new OleDbDataAdapter(newvalues); DataTable dt = new DataTable(); newvalue.Fill(dt); conn.Close(); using (SqlConnection connect = new SqlConnection("your connection string")) { connect.Open(); SqlCommand cmd = new SqlCommand("spupdate", connect); cmd.CommandType = CommandType.StoredProcedure; SqlParameter valparam = new SqlParameter("@update", SqlDbType.Structured); valparam.TypeName = "tablevalue"; valparam.Value = dt; cmd.Parameters.Add(valparam); cmd.ExecuteNonQuery(); connect.Close(); } } // Move the file to another folder string sourcePath = "your source folder path"; string destinationPath = "your destination folder path"; string fileName = Path.GetFileName(fileFullPath); string destinationFile = Path.Combine(destinationPath, fileName); File.Move(Path.Combine(sourcePath, fileName), destinationFile); you need to replace "your connection string", "your source folder path", and "your destination folder path" with your actual values. Remember that the File.Move method will throw an exception if the destination file already exists, so you may want to add some error handling code to handle this case.

Tuhin Paul · Answer

To read data from an Excel file you are using OleDb. To move the file to another folder after the update, you can use the File.Move method in C#.

Need to import data from excel to SQL

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (13)