// Return false if required CSV column are missing or there is a problem matching the CSV file headers against the DB headers. static private bool VerifyCSVHeaders(FileDatabase fileDatabase, List <string> dataLabelsFromCSV, List <string> importErrors) { bool abort = false; // Get the dataLabels from the database and from the headers in the CSV files (and remove any empty trailing headers from the CSV file list) List <string> dataLabelsFromDB = fileDatabase.GetDataLabelsExceptIDInSpreadsheetOrder(); List <string> dataLabelsInHeaderButNotFileDatabase = dataLabelsFromCSV.Except(dataLabelsFromDB).ToList(); // Abort if the File and Relative Path columns are missing from the CSV file // While the CSV data labels can be a subset of the DB data labels, // the File and Relative Path are a required CSV datalabel, as we can't match the DB data row without it. if (dataLabelsFromCSV.Contains(Constant.DatabaseColumn.File) == false || dataLabelsFromCSV.Contains(Constant.DatabaseColumn.RelativePath) == false) { importErrors.Add("CSV columns necessary to locate your image or video files are missing: "); if (dataLabelsFromCSV.Contains(Constant.DatabaseColumn.File) == false) { importErrors.Add(String.Format("- the '{0}' column.", Constant.DatabaseColumn.File)); } if (dataLabelsFromCSV.Contains(Constant.DatabaseColumn.RelativePath) == false) { importErrors.Add(String.Format("- the '{0}' column (You still need it even if your files are all in your root folder).", Constant.DatabaseColumn.RelativePath)); } abort = true; } // Abort if a column header in the CSV file does not exist in the template // NOTE: could do this as a warning rather than as an abort, but... if (dataLabelsInHeaderButNotFileDatabase.Count != 0) { importErrors.Add("These CSV column headings do not match any of the template'sDataLabels:"); foreach (string dataLabel in dataLabelsInHeaderButNotFileDatabase) { importErrors.Add(String.Format("- {0}", dataLabel)); abort = true; } } if (abort) { // We failed. abort. return(false); } return(true); }
/// <summary> /// Export all the database data associated with the selected view to the .csv file indicated in the file path so that spreadsheet applications (like Excel) can display it. /// </summary> public static void ExportToCsv(FileDatabase database, string filePath, bool excludeDateTimeAndUTCOffset) { using (TextWriter fileWriter = new StreamWriter(filePath, false)) { // Write the header as defined by the data labels in the template file // If the data label is an empty string, we use the label instead. // The append sequence results in a trailing comma which is retained when writing the line. StringBuilder header = new StringBuilder(); List <string> dataLabels = database.GetDataLabelsExceptIDInSpreadsheetOrder(); foreach (string dataLabel in dataLabels) { // Skip the DateTime and Utc offset column headers if (excludeDateTimeAndUTCOffset == true && (dataLabel == Constant.DatabaseColumn.DateTime || dataLabel == Constant.DatabaseColumn.UtcOffset)) { continue; } header.Append(AddColumnValue(dataLabel)); } fileWriter.WriteLine(header.ToString()); // For each row in the data table, write out the columns in the same order as the // data labels in the template file int countAllCurrentlySelectedFiles = database.CountAllCurrentlySelectedFiles; for (int row = 0; row < countAllCurrentlySelectedFiles; row++) { StringBuilder csvRow = new StringBuilder(); ImageRow image = database.FileTable[row]; foreach (string dataLabel in dataLabels) { // Skip the DateTime and Utc offset data if (excludeDateTimeAndUTCOffset == true && (dataLabel == Constant.DatabaseColumn.DateTime || dataLabel == Constant.DatabaseColumn.UtcOffset)) { continue; } csvRow.Append(AddColumnValue(image.GetValueDatabaseString(dataLabel))); } fileWriter.WriteLine(csvRow.ToString()); } } }
// Try importing a CSV file, checking its headers and values against the template's DataLabels and data types. // Return a list of errors if needed. public static async Task <Tuple <bool, List <string> > > TryImportFromCsv(string filePath, FileDatabase fileDatabase) { // Set up a progress handler that will update the progress bar Progress <ProgressBarArguments> progressHandler = new Progress <ProgressBarArguments>(value => { // Update the progress bar CsvReaderWriter.UpdateProgressBar(GlobalReferences.BusyCancelIndicator, value.PercentDone, value.Message, value.IsCancelEnabled, value.IsIndeterminate); }); IProgress <ProgressBarArguments> progress = progressHandler; bool abort = false; List <string> importErrors = new List <string>(); return(await Task.Run(() => { progress.Report(new ProgressBarArguments(0, "Reading the CSV file. Please wait", false, true)); List <List <string> > parsedFile = ReadAndParseCSVFile(filePath); if (parsedFile == null) { // Could not open the file importErrors.Add(String.Format("The file '{0}' could not be read. To check: Is opened by another application? Is it a valid CSV file?", Path.GetFileName(filePath))); return new Tuple <bool, List <string> >(false, importErrors); } if (parsedFile.Count < 2) { // The CSV file is empty or only contains a header row importErrors.Add(String.Format("The file '{0}' does not contain any data.", Path.GetFileName(filePath))); return new Tuple <bool, List <string> >(false, importErrors); } List <string> dataLabels = fileDatabase.GetDataLabelsExceptIDInSpreadsheetOrder(); // Get the header (and remove any empty trailing headers from the list) List <string> dataLabelsFromHeader = parsedFile[0].Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToList(); // validate .csv file headers against the database List <string> dataLabelsInHeaderButNotFileDatabase = dataLabelsFromHeader.Except(dataLabels).ToList(); // File - Required datalabel and contents as we can't update the file's data row without it. if (dataLabelsFromHeader.Contains(Constant.DatabaseColumn.File) == false) { importErrors.Add(String.Format("A '{0}' column containing matching file names to your images is required to do the update.", Constant.DatabaseColumn.File)); abort = true; } // Required: the column headers must exist in the template as valid DataLabels // Note: could do this as a warning rather than as an abort, but... foreach (string dataLabel in dataLabelsInHeaderButNotFileDatabase) { importErrors.Add(String.Format("The column heading '{0}' in the CSV file does not match any DataLabel in the template.", dataLabel)); abort = true; } if (abort) { // We failed. abort. return new Tuple <bool, List <string> >(false, importErrors); } // Create a List of all data rows, where each row is a dictionary containing the header and that row's valued for the header List <Dictionary <string, string> > rowDictionaryList = new List <Dictionary <string, string> >(); int rowNumber = 0; int numberOfHeaders = dataLabelsFromHeader.Count; foreach (List <string> parsedRow in parsedFile) { // For each data row rowNumber++; if (rowNumber == 1) { // Skip the 1st header row continue; } // for this row, create a dictionary of matching the CSV column Header and that column's value Dictionary <string, string> rowDictionary = new Dictionary <string, string>(); for (int i = 0; i < numberOfHeaders; i++) { string valueToAdd = (i < parsedRow.Count) ? parsedRow[i] : String.Empty; rowDictionary.Add(dataLabelsFromHeader[i], parsedRow[i]); } rowDictionaryList.Add(rowDictionary); } // Validate each value in the dictionary against the Header type and expected foreach (string header in dataLabelsFromHeader) { ControlRow controlRow = fileDatabase.GetControlFromTemplateTable(header); // We don't need to worry about File-related or Date-related controls as they are mot updated if (controlRow.Type == Constant.Control.Flag || controlRow.Type == Constant.DatabaseColumn.DeleteFlag || controlRow.Type == Constant.Control.Counter || controlRow.Type == Constant.Control.FixedChoice || controlRow.Type == Constant.DatabaseColumn.ImageQuality ) { rowNumber = 0; foreach (Dictionary <string, string> rowDict in rowDictionaryList) { rowNumber++; switch (controlRow.Type) { case Constant.Control.Flag: case Constant.DatabaseColumn.DeleteFlag: if (!Boolean.TryParse(rowDict[header], out _)) { // Flag values must be true or false, but its not. So raise an error importErrors.Add(String.Format("Error in row {1}. {0} values must be true or false, but is '{2}'", header, rowNumber, rowDict[header])); abort = true; } break; case Constant.Control.Counter: if (!String.IsNullOrWhiteSpace(rowDict[header]) && !Int32.TryParse(rowDict[header], out _)) { // Counters must be integers / blanks importErrors.Add(String.Format("Error in row {1}. {0} values must be blank or a number, but is '{2}'", header, rowNumber, rowDict[header])); abort = true; } break; case Constant.Control.FixedChoice: case Constant.DatabaseColumn.ImageQuality: if (controlRow.List.Contains(rowDict[header]) == false) { // Fixed Choices must be in the Choice List importErrors.Add(String.Format("Error in row {1}. {0} values must be in the template's choice list, but '{2}' isn't in it.", header, rowNumber, rowDict[header])); abort = true; } break; default: break; } } } } if (abort) { // We failed. abort. return new Tuple <bool, List <string> >(false, importErrors); } // Create the data structure for the query // Update the database 100 rows at a time. List <ColumnTuplesWithWhere> imagesToUpdate = new List <ColumnTuplesWithWhere>(); foreach (Dictionary <string, string> rowDict in rowDictionaryList) { // Process each row ColumnTuplesWithWhere imageToUpdate = new ColumnTuplesWithWhere(); foreach (string header in rowDict.Keys) { ControlRow controlRow = fileDatabase.GetControlFromTemplateTable(header); // process each column but only if its off the specific type if (controlRow.Type == Constant.Control.Flag || controlRow.Type != Constant.DatabaseColumn.DeleteFlag || controlRow.Type == Constant.Control.Counter || controlRow.Type == Constant.Control.FixedChoice || controlRow.Type == Constant.DatabaseColumn.ImageQuality ) { imageToUpdate.Columns.Add(new ColumnTuple(header, rowDict[header])); } } // Add to the query only if there are columns to add! if (imageToUpdate.Columns.Count > 0) { if (rowDict.ContainsKey(Constant.DatabaseColumn.RelativePath) && !String.IsNullOrWhiteSpace(rowDict[Constant.DatabaseColumn.RelativePath])) { imageToUpdate.SetWhere(rowDict[Constant.DatabaseColumn.RelativePath], rowDict[Constant.DatabaseColumn.File]); } else { imageToUpdate.SetWhere(rowDict[Constant.DatabaseColumn.File]); } imagesToUpdate.Add(imageToUpdate); } // write current batch of updates to database if (imagesToUpdate.Count >= 100) { fileDatabase.UpdateFiles(imagesToUpdate); imagesToUpdate.Clear(); } } // perform any remaining updates fileDatabase.UpdateFiles(imagesToUpdate); return new Tuple <bool, List <string> >(true, importErrors); }).ConfigureAwait(true)); }
/// <summary> /// Export all the database data associated with the selected view to the .csv file indicated in the file path so that spreadsheet applications (like Excel) can display it. /// </summary> public static async Task <bool> ExportToCsv(FileDatabase database, string filePath, CSVDateTimeOptionsEnum csvDateTimeOptions, bool csvInsertSpaceBeforeDates) { // Set up a progress handler that will update the progress bar Progress <ProgressBarArguments> progressHandler = new Progress <ProgressBarArguments>(value => { // Update the progress bar CsvReaderWriter.UpdateProgressBar(GlobalReferences.BusyCancelIndicator, value.PercentDone, value.Message, value.IsCancelEnabled, value.IsIndeterminate); }); IProgress <ProgressBarArguments> progress = progressHandler; return(await Task.Run(() => { //The separator, while normally a comma, can be different in some countries // We special case the separator as a ';' for countries that us a comma for a decimal point, e.g., Germany String separator = String.Equals(",", System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator) ? ";" : ","; try { progress.Report(new ProgressBarArguments(0, "Writing the CSV file. Please wait", false, true)); using (StreamWriter fileWriter = new StreamWriter(filePath, false)) { // Write the header as defined by the data labels in the template file // If the data label is an empty string, we use the label instead. // The append sequence results in a trailing comma which is retained when writing the line. StringBuilder header = new StringBuilder(); List <string> dataLabels = database.GetDataLabelsExceptIDInSpreadsheetOrder(); foreach (string dataLabel in dataLabels) { if (dataLabel == Constant.DatabaseColumn.UtcOffset) { // Always skip UTC Offset, as the user has the option of including that in the DateTime column instead continue; } // Skip the DateTime and Utc offset column headers //if (excludeDateTimeAndUTCOffset == true && (dataLabel == Constant.DatabaseColumn.DateTime || dataLabel == Constant.DatabaseColumn.UtcOffset)) if ((dataLabel == Constant.DatabaseColumn.Date || dataLabel == Constant.DatabaseColumn.Time) && csvDateTimeOptions != CSVDateTimeOptionsEnum.DateAndTimeColumns) { // Skip the Date column and Time column if the CSVDateTimeOptions are set to a parameter other than the two Date / Time columns continue; } if (dataLabel == Constant.DatabaseColumn.DateTime && csvDateTimeOptions == CSVDateTimeOptionsEnum.DateAndTimeColumns) { // Skip the DateTime column if the CSVDateTimeOptions is set to show the two Date / Time columns instead continue; } header.Append(AddColumnValue(dataLabel, separator)); } fileWriter.WriteLine(header.ToString()); // For each row in the data table, write out the columns in the same order as the // data labels in the template file int countAllCurrentlySelectedFiles = database.CountAllCurrentlySelectedFiles; for (int row = 0; row < countAllCurrentlySelectedFiles; row++) { StringBuilder csvRow = new StringBuilder(); ImageRow image = database.FileTable[row]; foreach (string dataLabel in dataLabels) { if (dataLabel == Constant.DatabaseColumn.UtcOffset) { // Always skip UTC Offset, as the user has the option of including that in the DateTime column instead continue; } if ((dataLabel == Constant.DatabaseColumn.Date || dataLabel == Constant.DatabaseColumn.Time) && csvDateTimeOptions != CSVDateTimeOptionsEnum.DateAndTimeColumns) { // Skip the Date column and Time column if the CSVDateTimeOptions are set to a parameter other than the two Date / Time columns continue; } if (dataLabel == Constant.DatabaseColumn.DateTime) { if (csvDateTimeOptions == CSVDateTimeOptionsEnum.DateAndTimeColumns) { // Skip the DateTime column if the CSVDateTimeOptions is set to show the two Date / Time columns instead continue; } else { string prefix = csvInsertSpaceBeforeDates ? " " : String.Empty; if (csvDateTimeOptions == CSVDateTimeOptionsEnum.DateTimeColumnWithTSeparator) { csvRow.Append(prefix + AddColumnValue(image.GetValueCSVDateTimeWithTSeparatorString(), separator)); } else if (csvDateTimeOptions == CSVDateTimeOptionsEnum.DateTimeWithoutTSeparatorColumn) { csvRow.Append(prefix + AddColumnValue(image.GetValueCSVDateTimeWithoutTSeparatorString(), separator)); } else { //Defunct, no longer used, should not get here System.Diagnostics.Debug.Print("In CSVWriter: Should not be trying to write a UTC Offset formatted date!"); //if (csvDateTimeOptions == CSVDateTimeOptionsEnum.DateTimeUTCWithOffset) //{ // csvRow.Append(prefix + AddColumnValue(image.GetValueCSVDateTimeUTCWithOffsetString())); //} } } } else if (dataLabel == Constant.DatabaseColumn.Date || dataLabel == Constant.DatabaseColumn.Time) { string prefix = csvInsertSpaceBeforeDates ? " " : String.Empty; csvRow.Append(prefix + AddColumnValue(image.GetValueDatabaseString(dataLabel), separator)); } else { csvRow.Append(AddColumnValue(image.GetValueDatabaseString(dataLabel), separator)); } } fileWriter.WriteLine(csvRow.ToString()); if (row % 5000 == 0) { progress.Report(new ProgressBarArguments(Convert.ToInt32(((double)row) / countAllCurrentlySelectedFiles * 100.0), String.Format("Writing {0}/{1} file entries to CSV file. Please wait...", row, countAllCurrentlySelectedFiles), false, false)); } } } return true; } catch { return false; } }).ConfigureAwait(true)); }