/// <summary> /// Returns TRUE if file was modified or if it's new. /// </summary> /// <param name="m_filePath">Full file path like: C:\\folder\\file.xls</param> /// <returns></returns> public static bool FileModified(string m_filePath) { Support.CreateFile(ProcessedFilesLog); // if file already exists, nothing is done. string[] ProcessedFiles = Support.getFileLines(ProcessedFilesLog); foreach (string ProcessedFile in ProcessedFiles) { if (ProcessedFile.Split(';')[0] == m_filePath && ProcessedFile.Split().Length > 1) { try { FileInfo FileInfo = new FileInfo(m_filePath); //LogFile.write_LogFile("FileModified?: " + FileInfo.LastWriteTime.ToString() + "==" + ProcessedFile.Split(';')[1] + " e " + (BuildMd5Checksum(m_filePath) + "==" + ProcessedFile.Split(';')[2])); if (FileInfo.Name.Contains("~$")) { return(false); //file is a temporary file } if (FileInfo.LastWriteTime.ToString() == ProcessedFile.Split(';')[1]) // && (BuildMd5Checksum(m_filePath) == ProcessedFile.Split(';')[2])) { return(false); //file wasnt modified } return(true); //file was modified } catch (Exception e) { LogFile.write_LogFile("Error verifying file modification for: " + m_filePath + " with message: " + e.Message); return(false); } } } //File is new return(true); }
//updates the log window private void updateLOG() { Support.CreateFile(LogFile.LogFilename); FileInfo FileInfo = new FileInfo(LogFile.LogFilePath); if (FileInfo.LastWriteTime.ToString() != lastWrite) { lastWrite = FileInfo.LastWriteTime.ToString(); string text = ""; string[] lines = Support.getFileLines(LogFile.LogFilename); for (int i = lines.Length - 1; i >= 0; i--) { text = text + lines[i] + Environment.NewLine; } richTextBox_Log.Text = text; } }
/// <summary> /// Adds/update file info on processed files log. /// </summary> /// <param name="m_filePath"></param> public static void RecordProcessedFile(string m_filePath) { Support.CreateFile(ProcessedFilesLog); // if file already exists, nothing is done. string[] ProcessedFiles = Support.getFileLines(ProcessedFilesLog); FileInfo FileInfo = new FileInfo(m_filePath); for (int i = 0; i < ProcessedFiles.Length; i++) { if (ProcessedFiles[i].Split(';')[0] == m_filePath && ProcessedFiles[i].Split().Length > 1) { //update existing record for that file ProcessedFiles[i] = m_filePath + ";" + FileInfo.LastWriteTime.ToString();// + ";" + BuildMd5Checksum(m_filePath); //rewrite file WriteAllLines(ProcessedFilesLog, ProcessedFiles, true); return; } } //add new record to end of file Support.WriteOneLine(ProcessedFilesLog, m_filePath + ";" + FileInfo.LastWriteTime.ToString(), true);// + ";" + BuildMd5Checksum(m_filePath),true); }
/// <summary> /// Tries to convert excel file to csv. Returns TRUE in case of success. /// </summary> /// <param name="m_filePath"></param> /// <returns></returns> public bool ConvertExcelFile(string m_filePath) { string[] whitelistLines; bool fileinlist = false; List <string[]> SheetFilters = new List <string[]>(); try { FileInfo currentFileInfo = new FileInfo(m_filePath); try { Support.CreateFile(Support.WhitelistFilePrefix + ConfigName + ".txt"); whitelistLines = Support.getFileLines(Support.WhitelistFilePrefix + ConfigName + ".txt"); } catch { LogFile.write_LogFile("No WhiteList file was found. All files will be converted."); whitelistLines = new string[] { "*;*" }; throw; } if (currentFileInfo.Extension == ".xlsx" || currentFileInfo.Extension == ".xls") { try { string fileName = currentFileInfo.Name; foreach (string currentLine in whitelistLines) { string[] currentLineSplit = currentLine.Split(';'); if (Regex.IsMatch(fileName.ToLower(), "^" + currentLineSplit[0].ToLower().Replace("*", ".*") + "$")) { fileinlist = true; if (currentLineSplit.Length == 2) { SheetFilters.Add(new string[] { currentLineSplit[1], "" }); } else if (currentLineSplit.Length >= 3) { SheetFilters.Add(new string[] { currentLineSplit[1], currentLineSplit[2] }); } } } } catch (Exception e) { LogFile.write_LogFile("Error reading whitelist: " + e.Message); //throw; } DataSet outputDS = new DataSet(); string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_filePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'"; using (OleDbConnection oleCon = new OleDbConnection(ConnectionString)) { oleCon.Open(); OleDbCommand oleCmd = new OleDbCommand(); oleCmd.Connection = oleCon; DataTable SheetsTable = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow current_sheet in SheetsTable.Rows) { string sheetName = Regex.Replace(current_sheet["TABLE_NAME"].ToString(), @"[;\'$]", ""); if (!fileinlist || SheetFilters.Exists(x => Regex.IsMatch(sheetName.ToLower(), "^" + x[0].ToLower().Replace("*", ".*") + "$"))) { oleCmd.CommandText = "select * from [" + sheetName + "$]"; DataTable currentDataTable = new DataTable(); OleDbDataAdapter OleDA; try { OleDA = new OleDbDataAdapter(oleCmd); currentDataTable.TableName = sheetName; OleDA.Fill(currentDataTable); } catch { continue; } outputDS.Tables.Add(currentDataTable); } } oleCmd = null; oleCon.Close(); StringBuilder outputString = new StringBuilder(); string cellvalue = ""; foreach (DataTable currentTable in outputDS.Tables) { outputString.Clear(); int firstrow = 0; foreach (string[] currentfilter in SheetFilters) { if (Regex.IsMatch(Regex.Replace(currentTable.TableName, @"[;\'$]", ""), "^" + currentfilter[0].Replace("*", ".*") + "$")) { if (!int.TryParse(currentfilter[1], out firstrow) && currentfilter[1] != "") { for (int i = 0; i < currentTable.Rows.Count; i++) { for (int j = 0; j < currentTable.Columns.Count; j++) { if (currentfilter[1] == currentTable.Rows[i].ItemArray[j].ToString()) { firstrow = i; } } } } break; } } for (int i = firstrow; i < currentTable.Rows.Count; i++) { cellvalue = ""; for (int j = 0; j < currentTable.Columns.Count; j++) { cellvalue += Regex.Replace(currentTable.Rows[i].ItemArray[j].ToString(), @"[;\'$|\r\n|\r|\n]", " ") + ";"; } outputString.AppendFormat("{0}", cellvalue.TrimEnd(';')); outputString.AppendLine(); } string Outputpath = Regex.Split(Destination, @"(.*[\\|\/])([^\\|\/]*)")[1]; //LogFile.write_LogFile("Saving excel output to: " + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".txt"); try { File.AppendAllText(Outputpath + NamePrefix + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv", outputString.ToString()); } catch (Exception exx) { LogFile.write_LogFile("Error saving output for file " + currentFileInfo.Name.Split('.')[0] + " at sheet " + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + " with message: " + exx.Message); } } return(true); } } return(false); } catch (Exception e) { LogFile.write_LogFile("Error during excel conversion, with message: " + e.Message); LogFile.write_LogFile("File was added to list of processed files: " + m_filePath); return(true); } }
/// <summary> /// Tries to convert excel file to csv. Returns TRUE in case of success. /// </summary> /// <param name="m_filePath"></param> /// <returns></returns> public bool ConvertAccdbFile(string m_filePath) { string[] tablelistLines; bool fileinlist = false; List <string[]> TableFilters = new List <string[]>(); try { Support.CreateFile(Support.TablelistFilePrefix + ConfigName + ".txt"); tablelistLines = Support.getFileLines(Support.TablelistFilePrefix + ConfigName + ".txt"); } catch { LogFile.write_LogFile("No WhiteList file was found. All files will be converted."); tablelistLines = new string[] { "*;*" }; } FileInfo currentFileInfo = new FileInfo(m_filePath); if (Regex.IsMatch(currentFileInfo.Extension, @"(.*db$)") || currentFileInfo.Extension == ".oee") { try { string fileName = currentFileInfo.Name; foreach (string currentLine in tablelistLines) { string[] currentLineSplit = currentLine.Split(';'); if (Regex.IsMatch(fileName.ToLower(), "^" + currentLineSplit[0].ToLower().Replace("*", ".*") + "$")) { fileinlist = true; if (currentLineSplit.Length < 4) { //LogFile.write_LogFile("Table filter: "+currentLineSplit[0]); TableFilters.Add(new string[] { currentLineSplit[1] }); } else if (currentLineSplit.Length >= 4) { //LogFile.write_LogFile("Table filter: "+currentLineSplit[0]+"," + currentLineSplit[1]+"," + currentLineSplit[2]); TableFilters.Add(new string[] { currentLineSplit[1], currentLineSplit[2], currentLineSplit[3] }); } } } } catch (Exception e) { LogFile.write_LogFile(e); } //Don't extract if there's no table defined if (!fileinlist) { //LogFile.write_LogFile("File não tava na lista"); return(false); } DataSet outputDS = new DataSet(); string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_filePath + ";Persist Security Info=False"; OleDbConnection oleCon = null; bool OneTableExtracted = false; try { using (oleCon = new OleDbConnection(ConnectionString)) { OneTableExtracted = false; oleCon.Open(); OleDbDataReader reader = null; foreach (string[] tableFilter in TableFilters) { string SQL_Comand; if (tableFilter.Length == 1) { SQL_Comand = "SELECT * FROM " + tableFilter[0] + ";"; } else if (tableFilter.Length == 3) { SQL_Comand = "SELECT * FROM " + tableFilter[0] + " WHERE " + tableFilter[1] + ">(Select Max(" + tableFilter[1] + ") from " + tableFilter[0] + ")-" + tableFilter[2] + ";"; } else { break; } //LogFile.write_LogFile("Conectando com string: " + SQL_Comand); OleDbCommand command = new OleDbCommand(SQL_Comand, oleCon); //command.Parameters.AddWithValue("@1", userName); reader = command.ExecuteReader(); string outputString = ""; var table = reader.GetSchemaTable(); var nameCol = table.Columns["ColumnName"]; outputString = Regex.Replace(table.Rows[0][nameCol].ToString(), "[;|\r\n|\r|\n]", " "); if (table.Rows.Count > 1) { for (int i = 1; i < table.Rows.Count; i++) { outputString = outputString + ";" + Regex.Replace(table.Rows[i][nameCol].ToString(), "[;|\r\n|\r|\n]", " "); } } outputString = outputString + Environment.NewLine; while (reader.Read()) { outputString = outputString + reader[0]; if (reader.FieldCount > 1) { for (int i = 1; i < reader.FieldCount; i++) { outputString = outputString + ";" + Regex.Replace(reader[i].ToString(), "[;|\r\n|\r|\n]", " "); } } outputString = outputString + Environment.NewLine; } string Outputpath = Regex.Split(Destination, @"(.*[\\|\/])([^\\|\/]*)")[1]; //LogFile.write_LogFile("Saving excel output to: " + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".txt"); File.AppendAllText(Outputpath + NamePrefix + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(tableFilter[0], @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv", outputString.ToString()); OneTableExtracted = true; } } } catch (Exception e) { LogFile.write_LogFile("Error during extraction for config: " + ConfigName + "at file:" + currentFileInfo.Name.Split('.')[0] + " with message: " + e.Message); } finally { oleCon.Close(); oleCon.Dispose(); oleCon = null; } return(OneTableExtracted); } return(false); }