/// <summary> /// Gets the encrypted passphrase of a setting, if needed it will open a windows form /// </summary> /// <param name="setting">The setting.</param> /// <returns></returns> /// <exception cref="ApplicationException"> /// The private key for decryption has not been setup /// or /// A passphrase is needed for decryption. /// </exception> public static string GetEncryptedPassphraseOpenForm(this IFileSetting setting) { if (ApplicationSetting.ToolSetting?.PGPInformation?.PrivateKeys?.IsEmpty() ?? true) { throw new ApplicationException("The private key for decryption has not been setup"); } if (!string.IsNullOrEmpty(setting?.Passphrase)) { return(setting.Passphrase); } if (!string.IsNullOrEmpty(ApplicationSetting.ToolSetting.PGPInformation.EncryptedPassphase)) { return(ApplicationSetting.ToolSetting.PGPInformation.EncryptedPassphase); } // Need to enter Passphrase using (var frm = new FormPassphrase()) { if (frm.ShowDialog() == DialogResult.OK) { return(frm.EncryptedPassphrase); } else { throw new ApplicationException("A passphrase is needed for decryption."); } } }
/// <summary> /// Fills the Column Format for writer fileSettings /// </summary> /// <param name="cancellationToken">The cancellation token.</param> /// <param name="fileSettings">The file settings.</param> /// <param name="all">if set to <c>true</c> event string columns are added.</param> public static void FillGuessColumnFormatWriter(this IFileSetting fileSettings, bool all, CancellationToken cancellationToken) { var fileWriter = fileSettings.GetFileWriter(cancellationToken); if (string.IsNullOrEmpty(fileSettings.SqlStatement)) { throw new ApplicationException("No SQL Statement given"); } using (var dataReader = ApplicationSetting.SQLDataReader(fileSettings.SqlStatement, cancellationToken)) { // Put the information into the list foreach (DataRow schemaRow in dataReader.GetSchemaTable().Rows) { var header = schemaRow[SchemaTableColumn.ColumnName].ToString(); var colType = ((Type)schemaRow[SchemaTableColumn.DataType]).GetDataType(); if (!all && (colType == DataType.String)) { continue; } var fsColumn = new Column { Name = header, DataType = colType }; fileSettings.ColumnAdd(fsColumn); } } }
/// <summary> /// Gets the writer source columns. /// </summary> /// <param name="fileSettings">The file settings.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns></returns> public static IEnumerable <ColumnInfo> GetWriterSourceColumns(IFileSetting fileSettings, CancellationToken cancellationToken) { Contract.Requires(fileSettings != null); var writer = fileSettings.GetFileWriter(cancellationToken); return(writer.GetColumnInformation(writer.GetSchemaReader())); }
/// <summary> /// Initializes a new instance of the <see cref="FormDetail" /> class. /// </summary> /// <param name="dataTable">The data table.</param> /// <param name="uniqueFieldName">Name of the unique field.</param> /// <param name="setting">a FileSetting</param> /// <param name="readOnly">if set to <c>true</c> the data will not be editable</param> /// <param name="onlyErrors">if set to <c>true</c> non error will be hidden</param> /// <param name="frozenColumns">The frozen columns.</param> /// <param name="cancellationToken">The cancellation token</param> /// public FormDetail(DataTable dataTable, IEnumerable <string> uniqueFieldName, IFileSetting setting, bool readOnly, bool onlyErrors, int frozenColumns, CancellationToken cancellationToken = default(CancellationToken)) { Contract.Requires(dataTable != null); SuspendLayout(); DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle { BackColor = System.Drawing.Color.Gainsboro }; DataGridViewCellStyle dataGridViewCellStyle2 = new System.Windows.Forms.DataGridViewCellStyle { Alignment = DataGridViewContentAlignment.MiddleLeft, BackColor = System.Drawing.SystemColors.Window, Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))), ForeColor = System.Drawing.Color.Black, SelectionBackColor = System.Drawing.SystemColors.Highlight, SelectionForeColor = System.Drawing.SystemColors.HighlightText, WrapMode = System.Windows.Forms.DataGridViewTriState.False }; detailControl = new DetailControl { CancellationToken = cancellationToken, AlternatingRowDefaultCellSyle = dataGridViewCellStyle1, DefaultCellStyle = dataGridViewCellStyle2, Dock = DockStyle.Fill, Location = new System.Drawing.Point(0, 0), Name = "detailControl", ReadOnly = readOnly, Size = new System.Drawing.Size(767, 394), TabIndex = 0, FileSetting = setting }; AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F); ClientSize = new System.Drawing.Size(767, 394); MinimumSize = new System.Drawing.Size(100, 100); Controls.Add(this.detailControl); Icon = global::CsvToolLib.Resources.SubFormIcon; KeyPreview = true; Name = "FormDetail"; ResumeLayout(false); detailControl.DataTable = dataTable; // Need to set UniqueFieldName last detailControl.UniqueFieldName = uniqueFieldName; if (frozenColumns > 0) { detailControl.FrozenColumns = frozenColumns; } if (onlyErrors) { detailControl.OnlyShowErrors(); } }
/// <summary> /// Invalidate the column header cache of a file /// </summary> /// <param name="fileSetting">The file setting.</param> public static void InvalidateColumnHeader(IFileSetting fileSetting) { Contract.Requires(fileSetting != null); if (fileSetting.InternalID.Length > 0) { ApplicationSetting.CacheList.Remove(CacheListKeyColumnHeader(fileSetting.ID, true)); ApplicationSetting.CacheList.Remove(CacheListKeyColumnHeader(fileSetting.ID, false)); } }
/// <summary> /// Initializes a new instance of the <see cref="FormColumnUI" /> class. /// </summary> /// <param name="column">The column format.</param> /// <param name="writeSetting">if set to <c>true</c> [write setting].</param> /// <param name="fileSetting">The file setting.</param> public FormColumnUI(Column column, bool writeSetting, IFileSetting fileSetting) { Contract.Requires(column != null); m_FileSetting = fileSetting; m_ColumnRef = column; column.CopyTo(m_ColumnEdit); m_WriteSetting = writeSetting; InitializeComponent(); }
/// <summary> /// Caches the column header. /// </summary> /// <param name="fileSetting">The file setting.</param> /// <param name="includeIgnored">if set to <c>true</c> [include ignored].</param> /// <param name="columns">The columns.</param> public static void CacheColumnHeader(IFileSetting fileSetting, bool includeIgnored, ICollection <string> columns) { Contract.Requires(fileSetting != null); var key = CacheListKeyColumnHeader(fileSetting.ID, includeIgnored); if (key.Length > 2) { ApplicationSetting.CacheList.Set(key, columns); } }
public void AddSetting(IFileSetting setting) { if (setting == null || string.IsNullOrEmpty(setting.ID)) { throw new ArgumentNullException(nameof(setting)); } if (!m_ReadSetting.Any(x => x.ID.Equals(setting.ID, StringComparison.OrdinalIgnoreCase))) { m_ReadSetting.Add(setting); } }
private static IFileReader DefaultFileReader([NotNull] IFileSetting setting, [CanBeNull] string timeZone, [CanBeNull] IProcessDisplay processDisplay) { switch (setting) { case ICsvFile csv when csv.JsonFormat: return(new JsonFileReader(csv, processDisplay)); case ICsvFile csv: return(new CsvFileReader(csv, processDisplay)); default: throw new NotImplementedException($"Reader for {setting} not found"); } }
public static IProcessDisplay GetProcessDisplay( [NotNull] this IFileSetting fileSetting, [CanBeNull] Form owner, bool withLogger, CancellationToken cancellationToken) { if (!fileSetting.ShowProgress) { return(new CustomProcessDisplay(cancellationToken)); } var processDisplay = new FormProcessDisplay(fileSetting.ToString(), withLogger, cancellationToken); processDisplay.Show(owner); return(processDisplay); }
private void ImportCOM(IFileSetting setting) { IFileReaderCOM fileReader = null; try { fileReader = (IFileReaderCOM)DataReaderFactory.GetFileReader(setting); fileReader.Open(true); MakeTableCOM(fileReader); } finally { if (fileReader != null) { fileReader.Dispose(); } } }
/// <summary> /// Gets the process display. /// </summary> /// <param name="fileSetting">The setting.</param> /// <param name="cancellationToken">A Cancellation token</param> /// <param name="owner"> /// The owner form, in case the owner is minimized or closed this progress will do the same /// </param> /// <returns>A process display, if the stetting want a process</returns> public static IProcessDisplay GetProcessDisplay(this IFileSetting fileSetting, Form owner, bool withLogger, CancellationToken cancellationToken) { if (!fileSetting.ShowProgress) { return(new DummyProcessDisplay(cancellationToken)); } if (withLogger) { var processDisplay = new FormProcessDisplayLogger(fileSetting.GetProcessDisplayTitle(), cancellationToken); processDisplay.Show(owner); return(processDisplay); } else { var processDisplay = new FormProcessDisplay(fileSetting.GetProcessDisplayTitle(), cancellationToken); processDisplay.Show(owner); return(processDisplay); } }
/// <summary> /// Opens the a setting for reading /// </summary> /// <param name="setting">The setting.</param> /// <returns></returns> /// <exception cref="ApplicationException"> /// Please provide a passphrase. /// or /// Please reenter the passphrase, the passphrase could not be decrypted. /// </exception> public static ImprovedStream OpenRead(IFileSetting setting) { var retVal = OpenBaseStream(setting.FullPath, setting.GetEncryptedPassphraseFunction); retVal.ResetToStart(delegate(Stream sr) { if (retVal.AssumePGP) { // Store the passphrase for next use, this does not mean it is correct setting.Passphrase = retVal.EncryptedPassphrase; if (ApplicationSetting.ToolSetting?.PGPInformation != null && ApplicationSetting.ToolSetting.PGPInformation.EncryptedPassphase.Length == 0) { ApplicationSetting.ToolSetting.PGPInformation.EncryptedPassphase = retVal.EncryptedPassphrase; } } }); return(retVal); }
/// <summary> /// Gets the column header of a file /// </summary> /// <param name="fileSetting">The file setting.</param> /// <param name="includeIgnored">Set to <c>true</c> if ignored columns should be listed as well, otherwise they will not be /// listed</param> /// <param name="processDisplay">The process display.</param> /// <returns> /// An array of string with the column headers /// </returns> public static ICollection <string> GetColumnHeader(IFileSetting fileSetting, bool includeIgnored, bool openIfNeeded, IProcessDisplay processDisplay) { Contract.Requires(fileSetting != null); var key = CacheListKeyColumnHeader(fileSetting.ID, includeIgnored); if (key.Length > 3 && ApplicationSetting.CacheList.TryGet(key, out var retValue)) { return(retValue); } if (!openIfNeeded) { return(null); } using (var fileReader = fileSetting.GetFileReader()) { fileReader.ProcessDisplay = processDisplay; fileReader.Open(false, processDisplay?.CancellationToken ?? CancellationToken.None); // if teh key was long enough it has been stored if (key.Length > 3) { return(ApplicationSetting.CacheList.Get(key)); } else { var header = new HashSet <string>(StringComparer.OrdinalIgnoreCase); for (var colindex = 0; colindex < fileReader.FieldCount; colindex++) { var col = fileReader.GetColumn(colindex); if (includeIgnored || !col.Ignore) { header.Add(col.Name); } } return(header); } } }
/// <summary> /// Get sample values for a column /// </summary> /// <param name="fileSetting">The file setting.</param> /// <param name="columnName">the Name of the column</param> /// <returns> /// The index of the given name, -1 if not found. /// </returns> public static int GetColumnIndex(IFileSetting fileSetting, string columnName, bool openIfNeeded = false) { if (string.IsNullOrEmpty(columnName) || fileSetting == null) { return(-1); } var columnIndex = 0; var headers = GetColumnHeader(fileSetting, true, openIfNeeded, null); if (headers != null) { foreach (var col in headers) { if (col.Equals(columnName, StringComparison.OrdinalIgnoreCase)) { return(columnIndex); } columnIndex++; } } return(-1); }
public async Task StartAsync([NotNull] IFileSetting fileSetting, bool includeError, TimeSpan durationInitial, [NotNull] IProcessDisplay processDisplay, [CanBeNull] EventHandler <WarningEventArgs> addWarning) { m_FileReader = FunctionalDI.GetFileReader(fileSetting, TimeZoneInfo.Local.Id, processDisplay); if (m_FileReader == null) { throw new FileReaderException($"Could not get reader for {fileSetting}"); } RowErrorCollection warningList = null; if (addWarning != null) { warningList = new RowErrorCollection(m_FileReader); m_FileReader.Warning += addWarning; m_FileReader.Warning -= warningList.Add; } Logger.Information("Reading data for display"); await m_FileReader.OpenAsync(processDisplay.CancellationToken).ConfigureAwait(false); if (addWarning != null) { warningList.HandleIgnoredColumns(m_FileReader); warningList.PassWarning += addWarning; } m_DataReaderWrapper = new DataReaderWrapper(m_FileReader, fileSetting.RecordLimit, includeError, fileSetting.DisplayStartLineNo, fileSetting.DisplayRecordNo, fileSetting.DisplayEndLineNo); m_ActionBegin?.Invoke(); await GetBatchByTimeSpan(durationInitial, includeError, processDisplay, m_SetDataTable).ConfigureAwait(false); m_SetLoadNextBatchAsync?.Invoke(process => GetBatchByTimeSpan(TimeSpan.MaxValue, includeError, process, (dt) => m_GetDataTable().Merge(dt))); m_ActionFinished?.Invoke(m_DataReaderWrapper); }
#pragma warning restore CA1051 // Do not declare visible instance fields protected BaseFileReaderTyped(IFileSetting fileSetting) : base(fileSetting) { }
/// <summary> /// Initializes a new instance of the <see cref="BaseFileWriter" /> class. /// </summary> /// <param name="fileSetting">the file setting with the definition for the file</param> /// <param name="cancellationToken">A cancellation token to stop writing the file</param> protected BaseFileWriter(IFileSetting fileSetting, CancellationToken cancellationToken) { Contract.Requires(fileSetting != null); m_CancellationToken = cancellationToken; m_FileSetting = fileSetting; }
public static string GetProcessDisplayTitle(this IFileSetting fileSetting) { return(string.IsNullOrEmpty(fileSetting.ID) ? FileSystemUtils.GetShortDisplayFileName(fileSetting.FileName, 80) : fileSetting.ID); }
/// <summary> /// Gets the column header of a file /// </summary> /// <param name="fileSetting">The file setting.</param> /// <param name="processDisplay">The get process display.</param> /// <returns> /// An array of string with the column headers where the column is empty /// </returns> public static string[] GetEmptyColumnHeader(IFileSetting fileSetting, IProcessDisplay processDisplay) { Contract.Requires(fileSetting != null); Contract.Requires(processDisplay != null); Contract.Ensures(Contract.Result <string[]>() != null); var emptyColumns = new List <string>(); if (!fileSetting.HasFieldHeader) { return(emptyColumns.ToArray()); } using (var fileReader = fileSetting.GetFileReader()) { Contract.Assume(fileReader != null); fileReader.ProcessDisplay = processDisplay; fileReader.Open(true, processDisplay.CancellationToken); if (fileSetting is CsvFile) { for (var column = 0; column < fileReader.FieldCount; column++) { var col = fileReader.GetColumn(column); if (col.Size == 0) { emptyColumns.Add(col.Name); } } } else { var columnHasData = new HashSet <int>(); for (var row = 0; row < 2000 && fileReader.Read(); row++) { for (var column = 0; column < fileReader.FieldCount; column++) { if (columnHasData.Contains(column)) { continue; } if (fileReader[column].ToString().Length > 0) { columnHasData.Add(column); } } if (columnHasData.Count == fileReader.FieldCount) { break; } } for (var column = 0; column < fileReader.FieldCount; column++) { if (!columnHasData.Contains(column)) { emptyColumns.Add(fileReader.GetName(column)); } } } } return(emptyColumns.ToArray()); }
/// <summary> /// Writes the file ans displays performance information /// </summary> /// <param name="fileSetting">The file setting.</param> /// <param name="showSummary"> /// Flag indicating that a message Box should be displayed. If <c>true</c> a message box will be /// shown /// </param> /// <param name="cancellationToken">A CancellationToken</param> /// <param name="fileSettingSourcesCurrent">The file setting sources current.</param> /// <param name="settingLaterThanSources">The setting later than sources.</param> /// <param name="ask">if set to <c>true</c> [ask].</param> /// <returns> /// Number of record written to file /// </returns> public static long WriteFileWithInfo(this IFileSetting fileSetting, bool showSummary, FileSettingChecker fileSettingSourcesCurrent, bool ask, bool onlyOlder, CancellationToken cancellationToken) { if (fileSetting == null) { return(0); } long written = 0; var fi = FileSystemUtils.FileInfo(fileSetting.FullPath); var dir = FileSystemUtils.GetDirectoryName(fi.FullName); if (!FileSystemUtils.DirectoryExists(dir)) { if (_MessageBox.Show(null, $"The directory {dir.RemovePrefix()} does not exist, should it be created?", "Directory", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) { FileSystemUtils.CreateDirectory(dir); } else { return(0); } } var fileInfo = new FileInfo(fileSetting.FullPath); if (fileInfo.Exists) { fileSetting.FileLastWriteTimeUtc = fi.LastWriteTimeUtc; } var stringBuilder = new System.Text.StringBuilder(); using (var processDisplay = fileSetting.GetProcessDisplay(null, true, cancellationToken)) { fileSettingSourcesCurrent?.Invoke(fileSetting, processDisplay); if (onlyOlder && fileSetting.SettingLaterThanSources(processDisplay.CancellationToken)) { return(0); } fileSetting.FullPath.DeleteFileQuestion(ask); var errors = new RowErrorCollection(50); var writer = fileSetting.GetFileWriter(processDisplay.CancellationToken); writer.ProcessDisplay = processDisplay; writer.Warning += errors.Add; written = writer.Write(); var hasIssues = !string.IsNullOrEmpty(writer.ErrorMessage) || (errors.CountRows > 0); if (showSummary || hasIssues) { fi = FileSystemUtils.FileInfo(fileSetting.FullPath); // if all source settings are file settings, get the latest file time and set this fileTime var latest = DateTime.MinValue; fileSetting.GetSourceFileSettings(delegate(IFileSetting setting) { if (!(setting is IFileSettingRemoteDownload)) { if (latest < setting.FileLastWriteTimeUtc) { latest = setting.FileLastWriteTimeUtc; } } else { var fiSrc = FileSystemUtils.FileInfo(setting.FullPath); if (fiSrc.Exists && latest < fiSrc.LastWriteTimeUtc) { latest = fiSrc.LastWriteTimeUtc; } } return(false); }, cancellationToken); stringBuilder.Append($"Finished writing file\r\rRecords: {written:N0}\rFile size: {fi.Length / 1048576.0:N} MB"); if (latest < DateTime.MaxValue && latest > DateTime.MinValue) { stringBuilder.Append($"\rTime adjusted to latest source file: {latest.ToLocalTime():D}"); fi.LastWriteTimeUtc = latest; fileSetting.FileLastWriteTimeUtc = latest; } if (hasIssues) { stringBuilder.Append("\rIssues:\r"); } if (!string.IsNullOrEmpty(writer.ErrorMessage)) { stringBuilder.Append(writer.ErrorMessage); } if (errors.CountRows > 0) { stringBuilder.Append(errors.DisplayByRecordNumber); } } } if (stringBuilder.Length > 0) { _MessageBox.Show(null, stringBuilder.ToString(), FileSystemUtils.GetShortDisplayFileName(fileSetting.FileName, 80), MessageBoxButtons.OK); } return(written); }
/// <summary> /// Fills the Column Format for reader fileSettings /// </summary> /// <param name="fileSetting">The file setting to check, and fill</param> /// <param name="addTextColumns">if set to <c>true</c> event string columns are added.</param> /// <param name="processDisplay">The process display.</param> public static IList <string> FillGuessColumnFormatReader(this IFileSetting fileSetting, bool addTextColumns, IProcessDisplay processDisplay) { if (processDisplay == null) { throw new ArgumentNullException(nameof(processDisplay)); } Contract.Requires(fileSetting != null); var result = new List <string>(); // if we should not detect, we can finish if (!ApplicationSetting.FillGuessSettings.DetectBoolean && !ApplicationSetting.FillGuessSettings.DetectGUID && !ApplicationSetting.FillGuessSettings.DectectNumbers && !ApplicationSetting.FillGuessSettings.DetectDateTime && !ApplicationSetting.FillGuessSettings.DectectPercentage && !ApplicationSetting.FillGuessSettings.SerialDateTime) { return(result); } var resetSkipRows = false; try { // Make sure that if we do have a CSV file without header that we will skip the first row that // might contain headers, but its simply set as without headers. if (fileSetting is CsvFile && !fileSetting.HasFieldHeader && fileSetting.SkipRows == 0) { fileSetting.SkipRows = 1; resetSkipRows = true; } var othersValueFormatDate = CommonDateFormat(fileSetting.Column.Select(x => x.ValueFormat)); using (var fileReader = fileSetting.GetFileReader()) { Contract.Assume(fileReader != null); // fileReader.ProcessDisplay = processDisplay; fileReader.Open(false, processDisplay.CancellationToken); if (fileReader.FieldCount == 0 || fileReader.EndOfFile) { return(result); } processDisplay.SetProcess("Getting column headers"); processDisplay.Maximum = fileReader.FieldCount; var columnNamesInFile = new List <string>(); for (var colindex = 0; colindex < fileReader.FieldCount; colindex++) { var newColumn = fileReader.GetColumn(colindex); Contract.Assume(newColumn != null); columnNamesInFile.Add(newColumn.Name); var oldColumn = fileSetting.GetColumn(newColumn.Name); processDisplay.SetProcess(newColumn.Name + " – Getting values", colindex); var samples = GetSampleValues(fileReader, ApplicationSetting.FillGuessSettings.CheckedRecords, colindex, ApplicationSetting.FillGuessSettings.SampleValues, fileSetting.TreatTextAsNull, processDisplay.CancellationToken); if (samples.IsEmpty()) { processDisplay.SetProcess(newColumn.Name + " – No values found", colindex); if (!addTextColumns) { continue; } result.Add($"{newColumn.Name} – No values found – Format : {newColumn.GetTypeAndFormatDescription()}"); fileSetting.ColumnAdd(newColumn); } else { var detect = !(ApplicationSetting.FillGuessSettings.IgnoreIdColums && StringUtils.AssumeIDColumn(newColumn.Name) > 0); if (samples.Count < 10) { processDisplay.SetProcess($"{newColumn.Name} – Only {samples.Count} values found in {ApplicationSetting.FillGuessSettings.CheckedRecords} rows", colindex); } else { processDisplay.SetProcess($"{newColumn.Name} – {samples.Count} values found – Examining format", colindex); } var checkResult = GuessValueFormat(samples, ApplicationSetting.FillGuessSettings.MinSamplesForIntDate, ApplicationSetting.FillGuessSettings.TrueValue, ApplicationSetting.FillGuessSettings.FalseValue, ApplicationSetting.FillGuessSettings.DetectBoolean && detect, ApplicationSetting.FillGuessSettings.DetectGUID && detect, ApplicationSetting.FillGuessSettings.DectectNumbers && detect, ApplicationSetting.FillGuessSettings.DetectDateTime && detect, ApplicationSetting.FillGuessSettings.DectectPercentage && detect, ApplicationSetting.FillGuessSettings.SerialDateTime && detect, ApplicationSetting.FillGuessSettings.CheckNamedDates && detect, othersValueFormatDate, processDisplay.CancellationToken); if (checkResult == null) { if (addTextColumns) { checkResult = new CheckResult { FoundValueFormat = new ValueFormat() } } ; else { continue; } } // if we have a mapping to a template that expects a integer and we only have integers but not enough... if (oldColumn != null) { var oldValueFormat = oldColumn.GetTypeAndFormatDescription(); // if we have a date value format already store this if (othersValueFormatDate == null && checkResult.FoundValueFormat.DataType == DataType.DateTime && checkResult.PossibleMatch) { othersValueFormatDate = checkResult.FoundValueFormat; } if (checkResult.FoundValueFormat.Equals(oldColumn.ValueFormat)) { processDisplay.SetProcess($"{newColumn.Name} – Format : {oldValueFormat} – not changed", colindex); } else { oldColumn.ValueFormat = checkResult.FoundValueFormat; } var newValueFormat = checkResult.FoundValueFormat.GetTypeAndFormatDescription(); if (oldValueFormat.Equals(newValueFormat, StringComparison.Ordinal)) { continue; } var msg = $"{newColumn.Name} – Format : {newValueFormat} – updated from {oldValueFormat}"; result.Add(msg); processDisplay.SetProcess(msg, colindex); } else { if (!addTextColumns && checkResult.FoundValueFormat.DataType == DataType.String) { continue; } newColumn.ValueFormat = checkResult.FoundValueFormat; var msg = $"{newColumn.Name} – Format : {newColumn.GetTypeAndFormatDescription()}"; processDisplay.SetProcess(msg, colindex); result.Add(msg); fileSetting.ColumnAdd(newColumn); } } } // The fileReader does not have the column information yet, let the reader know fileReader.OverrideColumnFormatFromSetting(fileReader.FieldCount); // in case its Excel, check all doubles if they could be integer if (fileSetting is IExcelFile) { for (var colindex = 0; colindex < fileReader.FieldCount; colindex++) { var oldColumn = fileReader.GetColumn(colindex); var detect = !(ApplicationSetting.FillGuessSettings.IgnoreIdColums && StringUtils.AssumeIDColumn(oldColumn.Name) > 0); if (oldColumn != null && oldColumn.DataType == DataType.Double) { Column newColumn = null; if (detect) { var samples = GetSampleValues(fileReader, ApplicationSetting.FillGuessSettings.CheckedRecords, colindex, ApplicationSetting.FillGuessSettings.SampleValues, fileSetting.TreatTextAsNull, processDisplay.CancellationToken); if (!samples.IsEmpty()) { var checkResult = GuessNumeric(samples, false, true, processDisplay.CancellationToken); if (checkResult != null && checkResult.FoundValueFormat.DataType != DataType.Double) { newColumn = fileSetting.GetColumn(oldColumn.Name); if (newColumn == null) { newColumn = fileSetting.ColumnAdd(oldColumn); } newColumn.DataType = checkResult.FoundValueFormat.DataType; } } } else { newColumn = fileSetting.GetColumn(oldColumn.Name); if (newColumn == null) { newColumn = fileSetting.ColumnAdd(oldColumn); } newColumn.DataType = DataType.String; } if (newColumn != null) { var msg = $"{newColumn.Name} – Overwritten Excel Format : {newColumn.GetTypeAndFormatDescription()}"; processDisplay.SetProcess(msg, colindex); result.Add(msg); } } } } if (ApplicationSetting.FillGuessSettings.DateParts) { // Try to find a time for a date if the date does not already have a time // Case a) TimeFormat has already been recognized for (var colindex = 0; colindex < fileReader.FieldCount; colindex++) { var columnDate = fileReader.GetColumn(colindex); // Possibly add Time Zone if (columnDate.DataType == DataType.DateTime && string.IsNullOrEmpty(columnDate.TimeZonePart)) { for (var coltimeZone = 0; coltimeZone < fileReader.FieldCount; coltimeZone++) { var columnTimeZone = fileReader.GetColumn(coltimeZone); var colName = columnTimeZone.Name.NoSpecials().ToUpperInvariant(); if (columnTimeZone.DataType != DataType.String && columnTimeZone.DataType != DataType.Integer || colName != "TIMEZONE" && colName != "TIMEZONEID" && colName != "TIME ZONE" && colName != "TIME ZONE ID") { continue; } columnDate.TimeZonePart = columnTimeZone.Name; result.Add($"{columnDate.Name} – Added Time Zone : {columnTimeZone.Name}"); } } if (columnDate.DataType != DataType.DateTime || !string.IsNullOrEmpty(columnDate.TimePart) || columnDate.ValueFormat.DateFormat.IndexOfAny(new[] { ':', 'h', 'H', 'm', 's', 't' }) != -1) { continue; } // We have a date column without time for (var coltime = 0; coltime < fileReader.FieldCount; coltime++) { var columnTime = fileReader.GetColumn(coltime); if (columnTime.DataType != DataType.DateTime || !string.IsNullOrEmpty(columnDate.TimePart) || columnTime.ValueFormat.DateFormat.IndexOfAny(new[] { '/', 'y', 'M', 'd' }) != -1) { continue; } // We now have a time column, // checked if the names somehow make sense if (!columnDate.Name.NoSpecials().ToUpperInvariant().Replace("DATE", string.Empty).Equals(columnTime.Name.NoSpecials().ToUpperInvariant().Replace("TIME", string.Empty), StringComparison.Ordinal)) { continue; } columnDate.TimePart = columnTime.Name; columnDate.TimePartFormat = columnTime.ValueFormat.DateFormat; result.Add($"{columnDate.Name} – Added Time Part : {columnTime.Name}"); } } // Case b) TimeFormat has not been recognized (e.G. all values are 08:00) only look in adjacent fields for (var colindex = 0; colindex < fileReader.FieldCount; colindex++) { var columnDate = fileReader.GetColumn(colindex); if (columnDate.DataType != DataType.DateTime || !string.IsNullOrEmpty(columnDate.TimePart) || columnDate.ValueFormat.DateFormat.IndexOfAny(new[] { ':', 'h', 'H', 'm', 's', 't' }) != -1) { continue; } if (colindex + 1 < fileReader.FieldCount) { var columnTime = fileReader.GetColumn(colindex + 1); if (columnTime.DataType == DataType.String && columnDate.Name.NoSpecials().ToUpperInvariant() .Replace("DATE", string.Empty) .Equals(columnTime.Name.NoSpecials().ToUpperInvariant().Replace("TIME", string.Empty), StringComparison.OrdinalIgnoreCase)) { columnDate.TimePart = columnTime.Name; { var samples = GetSampleValues(fileReader, 1, colindex + 1, 1, fileSetting.TreatTextAsNull, processDisplay.CancellationToken); var first = samples.FirstOrDefault(); if (first != null) { if (first.Length == 8 || first.Length == 5) { columnTime.DataType = DataType.DateTime; var val = new ValueFormat(DataType.DateTime) { DateFormat = first.Length == 8 ? "HH:mm:ss" : "HH:mm" }; columnTime.ValueFormat = val; fileSetting.ColumnAdd(columnTime); result.Add($"{columnTime.Name} – Format : {columnTime.GetTypeAndFormatDescription()}"); } } } result.Add($"{columnDate.Name} – Added Time Part : {columnTime.Name}"); continue; } } if (colindex <= 0) { continue; } { var columnTime = fileReader.GetColumn(colindex - 1); if (columnTime.DataType != DataType.String || !columnDate.Name.NoSpecials().ToUpperInvariant().Replace("DATE", string.Empty).Equals(columnTime.Name.NoSpecials().ToUpperInvariant().Replace("TIME", string.Empty), StringComparison.Ordinal)) { continue; } columnDate.TimePart = columnTime.Name; { var samples = GetSampleValues(fileReader, 1, colindex - 1, 1, fileSetting.TreatTextAsNull, processDisplay.CancellationToken); var first = samples.FirstOrDefault(); if (first != null) { if (first.Length == 8 || first.Length == 5) { var val = new ValueFormat(DataType.DateTime) { DateFormat = first.Length == 8 ? "HH:mm:ss" : "HH:mm" }; fileSetting.ColumnAdd(columnTime); columnTime.ValueFormat = val; result.Add($"{columnTime.Name} – Format : {columnTime.GetTypeAndFormatDescription()}"); } } } result.Add($"{columnDate.Name} – Added Time Part : {columnTime.Name}"); } } } // Sort the columns in fileSetting by order in file fileSetting.SortColumnByName(columnNamesInFile); } } finally { if (resetSkipRows) { fileSetting.SkipRows = 0; } } return(result); }