public override JsonProcessorResult Process(ImporterResult <string> input, ContentProcessorContext context)
{
context.Logger.LogMessage("Processing JSON");
var result = new JsonProcessorResult()
{
ContentType = ContentType,
Json = input.Data
};
return(result);
}
public static ImporterResult Load(SqlDataReader reader)
{
try
{
ImporterResult item = new ImporterResult
{
Description = reader["Description"].ToString(),
Param1 = reader["Param1"].ToString(),
Param2 = reader["Param2"].ToString(),
Param3 = reader["Param3"].ToString(),
Param4 = reader["Param4"].ToString(),
Param5 = reader["Param5"].ToString(),
Row = Convert.ToInt32(reader["Row"]),
TipoError = (TipoError)Convert.ToInt32(reader["TipoError"]),
Severity = (Severity)Convert.ToInt32(reader["Severity"]),
};
return(item);
}
catch (Exception ex)
{
return(null);
}
}
public static List <ImporterResult> ImportEnsayosStep2() //Crea tabla temporal e importar datos
{
List <ImporterResult> errors = new List <ImporterResult>();
int count = 0;
int rowCount = 0;
int tableCount = 0;
int columnCount = 0;
IExcelDataReader excelReader = null;
try
{
string sourceDirectoryPath = Configuration.ImportersPath;
DirectoryInfo di = new DirectoryInfo(sourceDirectoryPath);
FileInfo[] files = di.GetFiles("*.xls*", SearchOption.TopDirectoryOnly);
DataTable sourceTable = new DataTable();
sourceTable.Columns.Add(new DataColumn("Fuente", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Provincia", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Localidad", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Mal", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Establecimiento", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Campana", typeof(string)));
sourceTable.Columns.Add(new DataColumn("FechaSiembra", typeof(DateTime)));
sourceTable.Columns.Add(new DataColumn("FechaCosecha", typeof(DateTime)));
sourceTable.Columns.Add(new DataColumn("Producto", typeof(string)));
sourceTable.Columns.Add(new DataColumn("ColumnaVacia", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Rinde", typeof(float)));
sourceTable.Columns.Add(new DataColumn("Indice", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Quebrado", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Vuelco", typeof(string)));
sourceTable.Columns.Add(new DataColumn("AlturaPlanta", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Humedad", typeof(string)));
sourceTable.Columns.Add(new DataColumn("EsPromedio", typeof(string)));
sourceTable.Columns.Add(new DataColumn("PlantasXHectarea", typeof(string)));
sourceTable.Columns.Add(new DataColumn("DiasFloracion", typeof(string)));
sourceTable.Columns.Add(new DataColumn("EmergenciaFloracion", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Observaciones", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Archivo", typeof(string)));
sourceTable.Columns.Add(new DataColumn("Row", typeof(string)));
foreach (FileInfo info in files)
{
FileStream stream = File.Open(info.FullName, FileMode.Open, FileAccess.Read);
if (info.Extension.Equals(".xls"))
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (info.Extension.Equals(".xlsx"))
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
excelReader.IsFirstRowAsColumnNames = false;
DataSet xlsResults = excelReader.AsDataSet();
for (int t = 0; t < xlsResults.Tables.Count; t++)
{
tableCount = t;
for (int r = 1; r < xlsResults.Tables[t].Rows.Count; r++)
{
rowCount = r + 1;
DataRow row = sourceTable.NewRow();
for (int c = columnCount; c <= 21; c++)
{
if (c == 6 || c == 7) //Si viene con formato fecha en excel, lo cambio
{
string stringDate = (xlsResults.Tables[t].Rows[r][c]).ToString();
if (!string.IsNullOrEmpty(stringDate))
{
double doubleDate = double.Parse(stringDate);
row[c] = DateTime.FromOADate(doubleDate);
}
else
{
row[c] = xlsResults.Tables[t].Rows[r][c];
}
}
else
{
row[c] = xlsResults.Tables[t].Rows[r][c];
}
}
row[22] = rowCount;
sourceTable.Rows.Add(row);
}
}
}
// new method: SQLBulkCopy:
using (SqlConnection conn = new SqlConnection(Configuration.ConnectionString))
{
conn.Open();
using (SqlBulkCopy s = new SqlBulkCopy(conn))
{
s.DestinationTableName = "temp_ensayos";
s.BulkCopyTimeout = 120;
s.WriteToServer(sourceTable);
s.Close();
}
conn.Close();
}
}
catch (Exception ex)
{
count++;
ImporterResult error = new ImporterResult
{
Description = string.Format("Error: {0}. Fila {1}, Columna {2}", ex.Message, rowCount, columnCount),
TipoError = TipoError.FormatoIncorrecto,
Severity = Severity.Error,
};
errors.Add(error);
}
finally
{
//7. Free resources (IExcelDataReader is IDisposable)
if (excelReader != null)
{
excelReader.Close();
}
}
return(errors);
}
