// 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));
}
