private void btnBrowseFile_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "CSV File|*.csv";
openFileDialog1.Title = "Select the VL Results file";
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (GenericParserAdapter parser = new GenericParserAdapter(openFileDialog1.FileName))
{
DataSet ds = new DataSet();
//Load the data
ds = parser.GetDataSet();
dt = ds.Tables[0];
//Format the data
foreach (DataColumn dc in dt.Columns)
{
dc.ColumnName = dt.Rows[0][dc].ToString();
}
dt.Rows[0].Delete();
//Display the data
dgvResults.DataSource = dt;
}
}
}
public override string ExtractValue(BVPImportColumnDefinition coldef, GenericParserAdapter row, Dictionary <string, List <string> > extraData)
{
var value = row[coldef.SourceColumnName];
if (String.IsNullOrEmpty(value))
{
String alternate = null;
if (coldef.SourceColumnName.Equals("decimalLatitude"))
{
alternate = row["verbatimLatitude"];
}
else if (coldef.SourceColumnName.Equals("decimalLongitude"))
{
alternate = row["verbatimLongitude"];
}
if (!String.IsNullOrEmpty(alternate))
{
double?coord = GeoUtils.ParseCoordinate(alternate);
if (coord.HasValue)
{
value = Math.Round(coord.Value, 2).ToString();
}
}
}
return(value);
}
public override string ExtractValue(BVPImportColumnDefinition coldef, GenericParserAdapter row, Dictionary <string, List <string> > extraData)
{
var value = Default(coldef, row, extraData);
var dates = value.Split('/');
return(ExtractDate(dates, value));
}
// GET: Default
public ActionResult Index()
{
using (GenericParserAdapter parser = new GenericParserAdapter(Server.MapPath("~/data.csv")))
{
parser.FirstRowHasHeader = true;
Models.ReportGrid grid = new Models.ReportGrid
{
Data = parser.GetDataSet().Tables[0],
Columns = new List <Models.ReportGrid.Column>
{
new Models.ReportGrid.Column
{
DataTableName = "Location",
FilterType = GridFilterType.Multi,
UseFilterOptions = true,
Filterable = true
},
new Models.ReportGrid.Column
{
DataTableName = "Copay",
FilterType = GridFilterType.Double,
Filterable = true
}
}
};
return(View(grid));
}
}
/// <summary>
///
/// </summary>
/// <param name="strRutaArchivo"></param>
public void EstablecerOrigenDatos(string strRutaArchivo)
{
/// Verifica si existe y/o si se encontro el archivo con la ruta proporcionada.
if (!File.Exists(strRutaArchivo))
{
throw new FileNotFoundException("El archivo no pudo ser localizado...");
}
if (this.m_adaptadorAnalizadorGenerico != null)
{
this.m_adaptadorAnalizadorGenerico = null;
this.m_adaptadorAnalizadorGenerico.Dispose();
}
if (this.m_dtTablaDatos != null)
{
this.m_dtTablaDatos = null;
this.m_dtTablaDatos.Dispose();
}
this.m_adaptadorAnalizadorGenerico = new GenericParserAdapter(strRutaArchivo);
// this.m_adaptadorAnalizadorGenerico.FirstRowHasHeader = Tabla.TieneEncabezadoPredeterminado;
this.m_dtTablaDatos = this.m_adaptadorAnalizadorGenerico.GetDataTable();
this.m_blTieneEncabezado = true;
}
private void btnLoad_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog = new OpenFileDialog();
// Default to the directory which contains our content files.
string assemblyLocation = Assembly.GetExecutingAssembly().Location;
string relativePath = Path.Combine(assemblyLocation, "../../../../Content");
string contentPath = Path.GetFullPath(relativePath);
fileDialog.InitialDirectory = contentPath;
fileDialog.Title = "Load workspace";
fileDialog.Filter = "XML Files (*.xml)" +
"All Files (*.*)|*.*";
if (fileDialog.ShowDialog() == DialogResult.OK)
{
GenericParserAdapter parser = new GenericParserAdapter(fileDialog.FileName);
parser.FirstRowHasHeader = true;
parser.ColumnDelimiter = ",".ToCharArray()[0];
parser.SkipStartingDataRows = 2;
table = parser.GetDataTable();
DataTable dtCloned = table.Clone();
dtCloned.Columns["TIME_StartTime"].DataType = typeof(Int32);
foreach (DataRow row in table.Rows)
{
dtCloned.ImportRow(row);
}
table = dtCloned;
startTime = Convert.ToDouble(table.Rows[0]["TIME_StartTime"]);
}
}
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)));
}
private static void GetCityId(string connStr)
{
var parser = new GenericParserAdapter("c:\\temp\\ny.csv");
parser.FirstRowHasHeader = true;
DataTable t = parser.GetDataTable();
DataTable newT = new DataTable();
newT.Columns.Add("ZipCode");
newT.Columns.Add("CityId");
using (SqlConnection connection = new SqlConnection(connStr))
{
connection.Open();
foreach (DataRow r in t.Rows)
{
DataRow newr = newT.NewRow();
SqlCommand tCommand = new SqlCommand(string.Format("select CityId from City where Name = '{0}' and StateId = 35", r["City"]), connection);
SqlDataReader tReader = tCommand.ExecuteReader();
tReader.Read();
newr[0] = r[0];
newr[1] = tReader[0];
tReader.Close();
newT.Rows.Add(newr);
}
connection.Close();
}
newT.ToCsv("c:\\temp\\ny_zc.csv");
}
private void loadQueriesToolStripMenuItem_Click(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
GenericParserAdapter parser = new GenericParserAdapter(openFileDialog1.FileName);
parser.FirstRowHasHeader = true;
DataTable table = parser.GetDataTable();
table.TableName = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
lvwQueries.Tag = table;
foreach (DataRow row in table.Rows)
{
ListViewItem lvi = new ListViewItem(row["Query"].ToString());
lvi.SubItems.Add(row["User"].ToString());
lvi.SubItems.Add(row["Role"].ToString());
lvi.Tag = row;
lvwQueries.Items.Add(lvi);
}
splitContainerBase.Panel1Collapsed = false;
}
catch
{
MessageBox.Show("Error! This operation accepts a csv file with 3 columns 'Instance', 'Query', 'User', 'Role'. The first row expected is header.",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="txtLector"></param>
public void EstablecerOrigenDatos(TextReader txtLector)
{
if (txtLector == null)
{
throw new ArgumentNullException("txtLector", "El TextReader no puede ser nulo.");
}
if (this.m_adaptadorAnalizadorGenerico != null)
{
this.m_adaptadorAnalizadorGenerico = null;
this.m_adaptadorAnalizadorGenerico.Dispose();
}
if (this.m_dtTablaDatos != null)
{
this.m_dtTablaDatos = null;
this.m_dtTablaDatos.Dispose();
}
this.m_adaptadorAnalizadorGenerico = new GenericParserAdapter(txtLector);
// this.m_adaptadorAnalizadorGenerico.FirstRowHasHeader = Tabla.TieneEncabezadoPredeterminado;
this.m_dtTablaDatos = this.m_adaptadorAnalizadorGenerico.GetDataTable();
this.m_blTieneEncabezado = true;
}
/// <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);
}
/// <inheritdoc />
public Task <DataTable> ReadFromCsv(string fileName)
{
using var parser = new GenericParserAdapter(fileName)
{
FirstRowHasHeader = true
};
return(Task.FromResult(parser.GetDataTable()));
}
public override ImportRowSource CreateRowSource(IProgressObserver progress)
{
if (_options == null)
{
throw new Exception("Null or incorrect options type received!");
}
ImportRowSource rowsource = null;
using (var parser = new GenericParserAdapter(_options.Filename)) {
parser.ColumnDelimiter = _options.Delimiter[0];
parser.FirstRowHasHeader = _options.FirstRowContainsNames;
parser.TextQualifier = '\"';
parser.FirstRowSetsExpectedColumnCount = true;
var service = new ImportStagingService();
var columnNames = new List <String>();
int rowCount = 0;
service.BeginTransaction();
var values = new List <string>();
while (parser.Read())
{
if (rowCount == 0)
{
for (int i = 0; i < parser.ColumnCount; ++i)
{
if (_options.FirstRowContainsNames)
{
columnNames.Add(parser.GetColumnName(i));
}
else
{
columnNames.Add("Column" + i);
}
}
service.CreateImportTable(columnNames);
}
values.Clear();
for (int i = 0; i < parser.ColumnCount; ++i)
{
values.Add(parser[i]);
}
service.InsertImportRow(values);
rowCount++;
}
service.CommitTransaction();
rowsource = new ImportRowSource(service, rowCount);
}
return(rowsource);
}
private DataTable FileToDataTable(GenericParserAdapter parser, char columnDelimiter)
{
parser.ColumnDelimiter = columnDelimiter;
parser.FirstRowHasHeader = true;
parser.MaxBufferSize = 4096;
parser.MaxRows = Int32.MaxValue;
parser.TextQualifier = '\"';
return(parser.GetDataTable());
}
public override string ExtractValue(BVPImportColumnDefinition coldef, GenericParserAdapter row, Dictionary <string, List <string> > extraData)
{
var value = Default(coldef, row, extraData);
if (!String.IsNullOrEmpty(value) && _valueMap.ContainsKey(value.ToLower()))
{
value = _valueMap[value.ToLower()];
}
return(value);
}
private bool Validate()
{
if (string.IsNullOrWhiteSpace(txtFilename.Text))
{
ErrorMessage.Show("You must specify an input file!");
return(false);
}
if (!File.Exists(txtFilename.Text))
{
ErrorMessage.Show("File does not exist!");
return(false);
}
// Extract Column Headers...
ColumnNames = new List <String>();
GenericParser parser = null;
try {
using (parser = new GenericParserAdapter(Filename)) {
parser.ColumnDelimiter = Delimiter.ToCharArray()[0];
parser.FirstRowHasHeader = IsFirstRowContainNames;
parser.MaxRows = 2;
parser.TextQualifier = _textQualifier;
if (parser.Read())
{
for (int i = 0; i < parser.ColumnCount; ++i)
{
if (IsFirstRowContainNames)
{
ColumnNames.Add(parser.GetColumnName(i));
}
else
{
ColumnNames.Add("Column" + i);
}
}
}
else
{
ErrorMessage.Show("Failed to extract column names from data source!");
return(false);
}
}
} finally {
if (parser != null)
{
System.GC.SuppressFinalize(parser);
}
}
return(true);
}
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));
}
/// <summary>
/// Creates a data
/// </summary>
/// <param name="CsvContents">A stream containing the CsvContents</param>
/// <returns></returns>
public static DataTable ParseFromCsv(string CsvContents)
{
var textReader = new StringReader(CsvContents);
using (var adapter = new GenericParserAdapter(textReader))
{
adapter.FirstRowHasHeader = true;
var table = adapter.GetDataTable();
return(table);
}
}
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());
}
public override string ExtractValue(BVPImportColumnDefinition coldef, GenericParserAdapter row, Dictionary <string, List <string> > extraData)
{
if (extraData != null && extraData.ContainsKey(coldef.SourceColumnName))
{
var mediaList = extraData[coldef.SourceColumnName];
if (mediaList != null && mediaList.Count > 0)
{
return(mediaList[0]);
}
}
return(null);
}
public static DataTable CsvToDataTable(this string csv)
{
DataTable table;
using (var parser = new GenericParserAdapter(new StringReader(csv)))
{
parser.FirstRowHasHeader = true;
table = parser.GetDataTable();
}
return(table);
}
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 DataTable GetCrewWorkPanels(string ctfLocation)
{
DataTable dtResult = new DataTable();
using (GenericParserAdapter parser = new GenericParserAdapter(ctfLocation))
{
parser.FirstRowHasHeader = true;
var dsResult = parser.GetDataSet();
dtResult = dsResult.Tables[0];
}
return(dtResult);
}
public void LoadDataFromFile(string path)
{
using (var parser = new GenericParserAdapter(path)
{
FirstRowHasHeader = true,
ColumnDelimiter = '\t',
FirstRowSetsExpectedColumnCount = true,
SkipEmptyRows = true,
})
{
Data = parser.GetDataTable();
}
}
public static DataTable GetWorkPatterns()
{
DataTable dtResult = new DataTable();
using (GenericParserAdapter parser = new GenericParserAdapter("C:\\Users\\david.bracken\\OneDrive - TUI\\Documents\\Furlough\\CCMasterRoster.csv"))
{
parser.FirstRowHasHeader = true;
var dsResult = parser.GetDataSet();
dtResult = dsResult.Tables[0];
}
return(dtResult);
}
private static async Task <DataTable> GetCSV(string filepath)
{
DataTable retval = await Task.Run(() => {
var adapter = new GenericParserAdapter(filepath)
{
FirstRowHasHeader = true
};
DataTable dt = adapter.GetDataTable();
return(dt);
});
return(retval);
}
/// <summary>
/// Gets data from csv database and saves it in a data dictionary
/// </summary>
private void InitializeData()
{
using (var parser = new GenericParserAdapter(_systemDataFilePath))
{
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.SkipStartingDataRows = 0;
parser.SkipEmptyRows = true;
parser.MaxBufferSize = 4096;
parser.MaxRows = 8000;
_dictofdata = parser.GetDataTable();
}
for (int index = 0; index < _dictofdata.Rows.Count; index++)
{
var row = _dictofdata.Rows[index];
System = row["Sistema"].ToString();
License = row["Licencia"].ToString();
ExchangeRate = decimal.Parse(row["TipoCambio"].ToString());
LastCashierAmountMxn = decimal.Parse(row["CantidadPesosCaja"].ToString());
LastCashierAmountUsd = decimal.Parse(row["CantidadDolarCaja"].ToString());
LastReceiptNumber = Int32.Parse(row["UltimoReciboNumero"].ToString());
LastCorteZNumber = Int32.Parse(row["UltimoCorteZNumero"].ToString());
LastTransactionNumber = Int32.Parse(row["UltimoTransaccionNumero"].ToString());
LastInternalNumber = Int32.Parse(row["UltimoNumeroInterno"].ToString());
LastOrderNumber = Int32.Parse(row["UltimoNumeroPedido"].ToString());
PrinterName = row["NombreImpresora"].ToString();
BusinessName = row["NombreNegocio"].ToString();
FiscalStreetAddress = row["DireccionCalleFiscal"].ToString();
FiscalCityAndZipCode = row["CiudadCodigoFiscal"].ToString();
FiscalNumber = row["RFC"].ToString();
FiscalName = row["NombreFiscal"].ToString();
FiscalPhoneNumber = row["NumeroTelefono"].ToString();
FiscalEmail = row["Email"].ToString();
FiscalType = row["RazonSocial"].ToString();
Facebook = row["Facebook"].ToString();
Instagram = row["Instagram"].ToString();
Twitter = row["Twitter"].ToString();
Website = row["PaginaInternet"].ToString();
Comments = row["Comentarios"].ToString();
FooterMessage = row["MensajePie"].ToString();
LogoName = row["LogoImagen"].ToString();
EmailSender = row["EmailSaliente"].ToString();
EmailSenderPassword = row["EmailSalientePassword"].ToString();
EmailReports = row["EmailReportes"].ToString();
EmailOrders = row["EmailPedidos"].ToString();
DiscountPercent = decimal.Parse(row["DescuentoPorcentaje"].ToString());
PointsPercent = decimal.Parse(row["PuntosPorcentaje"].ToString());
}
}
/// <summary>
/// Load CSV database into a datatable object
/// </summary>
public static DataTable LoadCsvToDataTable(string csvFilePath)
{
using (var parser = new GenericParserAdapter(csvFilePath))
{
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.SkipStartingDataRows = 0;
parser.SkipEmptyRows = true;
parser.MaxBufferSize = 4096;
parser.MaxRows = 8000;
return(parser.GetDataTable());
}
}
/// <summary>
/// Load CSV database into a datatable object
/// </summary>
public void LoadCsvToDataTable()
{
using (var parser = new GenericParserAdapter(FilePath))
{
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
parser.SkipStartingDataRows = 0;
parser.SkipEmptyRows = true;
parser.MaxBufferSize = 4096;
parser.MaxRows = 8000;
DataTable = parser.GetDataTable();
}
}
