public IActionResult MetricToDimension([FromQuery] int fileId, [FromQuery] string colName, [FromQuery] int colId)
{
var userId = User.FindFirstValue(ClaimTypes.NameIdentifier); // will give the user's userId
CsvFile file = _context.CsvFile.Where(f => f.UserId == userId && f.CsvFileId == fileId).FirstOrDefault();
if (file == null)
{
return(NotFound());
}
string filePath = Path.Combine(_targetFilePath, Path.Combine(userId, file.FileNameStorage, file.FileNameStorage));
DataTable data = new DataTable();
using (GenericParserAdapter parser = new GenericParserAdapter())
{
parser.SetDataSource(filePath);
parser.ColumnDelimiter = ';';
parser.FirstRowHasHeader = true;
data = parser.GetDataTable();
}
if (colId > data.Columns.Count - 1)
{
return(StatusCode(500, "index of column out of bounds"));
}
CsvColumn csvColumn = new CsvColumn(file, colName, colId, _context);
csvColumn.AnalyseDimension(data.Rows);
return(Ok(new MD_Dimensao(csvColumn)));
}
/// <summary>
/// Centralizes creation of parsers with all the properties for CSV files
/// </summary>
private static GenericParserAdapter CsvParserFactory(string FileName)
{
GenericParserAdapter parser = new GenericParserAdapter();
parser.SetDataSource(FileName);
parser.FirstRowHasHeader = true;
parser.FirstRowSetsExpectedColumnCount = true;
return parser;
}
public DataTable GetFileAsDataTable(string filepath, char columnDelimiter = ',')
{
using var parser = new GenericParserAdapter();
parser.SetDataSource(filepath);
return(FileToDataTable(parser, columnDelimiter));
}
/// <summary>
/// Centralizes creation of parsers with all the properties for CSV files
/// </summary>
private static GenericParserAdapter CsvParserFactory(string FileName)
{
GenericParserAdapter parser = new GenericParserAdapter();
parser.SetDataSource(FileName);
parser.FirstRowHasHeader = true;
parser.FirstRowSetsExpectedColumnCount = true;
return(parser);
}
public DataTable GetFileStreamAsDataTable(Stream stream, char columnDelimiter = ',')
{
stream.Position = 0;
using var parser = new GenericParserAdapter();
using StreamReader streamReader = new StreamReader(stream);
parser.SetDataSource(streamReader);
return(FileToDataTable(parser, columnDelimiter));
}
public static System.Data.DataTable FlatToDt(string filepath, char delimiter = ',', int maxrownumber = 0)
{
//READS FLAT FILES INTO DATATABLE
GenericParserAdapter parserAdapter = new GenericParserAdapter();
parserAdapter.ColumnDelimiter = delimiter;
parserAdapter.SkipEmptyRows = true;
parserAdapter.SetDataSource(filepath);
parserAdapter.FirstRowHasHeader = true;
parserAdapter.MaxRows = maxrownumber > 0 ? maxrownumber : 1000000;
return(parserAdapter.GetDataTable());
}
private void Work(string message)
{
int fileId = int.Parse(message.Split("|")[0]);
string filePath = message.Split("|")[1];
Metadata metadata;
DataAnnotationDBContext _context;
var optionsBuilder = new DbContextOptionsBuilder <DataAnnotationDBContext>();
optionsBuilder.UseSqlServer(options.DefaultConnection);
using (_context = new DataAnnotationDBContext(optionsBuilder.Options))
{
CsvFile file = _context.CsvFile.Where(f => f.CsvFileId == fileId).FirstOrDefault();
if (file.CsvFileId == 0)
{
_logger.LogInformation("Message {0} - File not found in database", message);
return;
}
DateTime timeInit = DateTime.Now;
DataTable data = new DataTable();
using (GenericParserAdapter parser = new GenericParserAdapter())
{
parser.SetDataSource(filePath);
parser.ColumnDelimiter = ';';
parser.FirstRowHasHeader = true;
data = parser.GetDataTable();
}
file.ColumnsCount = data.Columns.Count;
file.RowsCount = data.Rows.Count;
_context.CsvFile.Update(file);
_context.SaveChanges();
CsvFileEx fileEx = new CsvFileEx(data, file, _context);
fileEx.InitIntraAnalysis();
fileEx.InitDivisoesCompare();
fileEx.CheckMetricsRelations();
metadata = new Metadata(file, fileEx, timeInit, _context);
}
var json = JsonSerializer.Serialize(metadata);
string fileFolderPath = Directory.GetParent(filePath).FullName;
string fileName = Path.GetFileName(filePath);
fileFolderPath = Path.Combine(fileFolderPath, "analysis");
Directory.CreateDirectory(fileFolderPath);
filePath = Path.Combine(fileFolderPath, "analysis_v1");
System.IO.File.WriteAllText(filePath, json);
_logger.LogInformation("Message {0} - Work Complete", message);
}
public static System.Data.DataTable LoadCSVFile(string file, char ColumnDelimiter = ',', char TextQualifier = '"', int MaxRows = 15000)
{
using (GenericParserAdapter parser = new GenericParserAdapter(file))
{
parser.SetDataSource(file);
parser.ColumnDelimiter = ColumnDelimiter;
parser.FirstRowHasHeader = true;
//parser.SkipStartingDataRows = 10;
parser.MaxBufferSize = 8096;
parser.MaxRows = MaxRows;
parser.TextQualifier = TextQualifier;
return(parser.GetDataTable());
}
}
private DataSet FlatFileToDataSet(string filePath)
{
_log.DebugFormat(@"FlatFileToDataSet");
DataSet result = new DataSet();
using (GenericParserAdapter parser = new GenericParserAdapter()) {
parser.SetDataSource(filePath);
parser.ColumnDelimiter = '\t';
parser.FirstRowHasHeader = true;
parser.SkipEmptyRows = false;
result = parser.GetDataSet();
}
return(result);
}
public IActionResult GetFirstValues([FromQuery] int fileId, [FromQuery] int colId) //gets first values of a specified column
{
var userId = User.FindFirstValue(ClaimTypes.NameIdentifier); // will give the user's userId
CsvFile file = _context.CsvFile.Where(f => f.UserId == userId && f.CsvFileId == fileId).FirstOrDefault();
if (file == null)
{
return(NotFound());
}
string filePath = Path.Combine(_targetFilePath, Path.Combine(userId, file.FileNameStorage, file.FileNameStorage));
DataTable data = new DataTable();
using (GenericParserAdapter parser = new GenericParserAdapter())
{
parser.SetDataSource(filePath);
parser.ColumnDelimiter = ';';
parser.FirstRowHasHeader = true;
data = parser.GetDataTable();
}
if (colId > data.Columns.Count - 1)
{
return(StatusCode(500, "index of column out of bounds"));
}
int rowcount = data.Rows.Count;
if (rowcount > 10)
{
rowcount = 10;
}
List <string> values = new List <string>(rowcount);
foreach (DataRow row in data.Rows)
{
values.Add((string)row[colId]);
if (--rowcount == 0)
{
break;
}
}
return(Ok(values.ToArray()));
}
static public DataSet loadTxtToDataset(string path, string name)
{
// Setup the DataSet
DataSet ds = new DataSet();
// Use Generic Txt Parser to load csv to dataset
using (GenericParserAdapter parser = new GenericParserAdapter()) {
parser.SetDataSource(path);
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.TextQualifier = '\"';
ds = parser.GetDataSet();
}
ds.Tables[0].TableName = name;
return(ds);
}
// Unused for Now, could be useful in teh future
static public DataSet loadExcelData()
{
// Setup the DataSet
DataSet ds = new DataSet();
// Use Generic Txt Parser to load source_sphere.csv to a dataset.
using (GenericParserAdapter parser = new GenericParserAdapter()) {
parser.SetDataSource(Path.Combine(root, @".\data\source_sphere.csv"));
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.TextQualifier = '\"';
ds = parser.GetDataSet();
}
ds.Tables[0].TableName = "data";
return(ds);
}
/// <summary>
/// Parse input to xml
/// </summary>
/// <returns>Object {string Result } </returns>
public static Output ConvertToXML(Parameters parameters, [PropertyTab] CsvInputParameters csvInputParameters, [PropertyTab] JsonInputParameters jsonInputParameters)
{
if (parameters.Input.GetType() != typeof(string))
{
throw new InvalidDataException("The input data string was not in correct format. Supported formats are JSON, CSV and fixed length.");
}
if (parameters.Input.StartsWith("{") || parameters.Input.StartsWith("["))
{
if (string.IsNullOrEmpty(jsonInputParameters.XMLRootElementName))
{
throw new MissingFieldException("Root element name missing. Required with JSON input");
}
if (jsonInputParameters.AppendToFieldName == null)
{
return new Output {
Result = JsonConvert.DeserializeXmlNode(parameters.Input, jsonInputParameters.XMLRootElementName).OuterXml
}
}
;
var jsonObject = (JObject)JsonConvert.DeserializeObject(parameters.Input);
var newObject = ChangeNumericKeys(jsonObject, jsonInputParameters.AppendToFieldName);
return(new Output {
Result = JsonConvert.DeserializeXmlNode(JsonConvert.SerializeObject(newObject), jsonInputParameters.XMLRootElementName).OuterXml
});
}
if (!string.IsNullOrEmpty(csvInputParameters.CSVSeparator) && parameters.Input.Contains(csvInputParameters.CSVSeparator))
{
using (var parser = new GenericParserAdapter())
{
char?separator = Convert.ToChar(csvInputParameters.CSVSeparator);
parser.SetDataSource(new StringReader(parameters.Input));
parser.ColumnDelimiter = separator;
parser.FirstRowHasHeader = csvInputParameters.InputHasHeaderRow;
parser.MaxBufferSize = 4096;
parser.TrimResults = csvInputParameters.TrimOuputColumns;
return(new Output {
Result = parser.GetXml().OuterXml
});
}
}
if (csvInputParameters.ColumnLengths == null)
{
throw new InvalidDataException("The input was recognized as fixed length file, but no column lengths were supplied.");
}
using (var parser = new GenericParserAdapter())
{
var headerList = new List <int>();
foreach (var column in csvInputParameters.ColumnLengths)
{
headerList.Add(column.Length);
}
var headerArray = headerList.ToArray();
parser.SetDataSource(new StringReader(parameters.Input));
parser.ColumnWidths = headerArray;
parser.FirstRowHasHeader = csvInputParameters.InputHasHeaderRow;
parser.MaxBufferSize = 4096;
parser.TrimResults = csvInputParameters.TrimOuputColumns;
return(new Output {
Result = parser.GetXml().OuterXml
});
}
}