Bueno, dado que en mi empresa soy el desarrollador de integraciones y afines me solicitaron hacer un ingreso masivo de datos a una BD, hacerle un tratamiento de limpieza y tomar todos los datos a un consumo mounstruoso de un WCF. Todo pintaba bien, una solución SSIS que hiciera todo el proceso y una vez finalizara la limpieza empezar a darle a ese wcf como si no hubiera un mañana. Obvio, primero me aseguré que no hubiese un cuello de botella en el servicio wcf, pero después de ver que el servicio estaba en una plataforma de alta disponibilidad y dado que es un proceso nocturno, no le ví problema. Lástima que el acceso a la BD estuviese restringido, todo hubiese sido más chévere.
En fin inicié el proceso así que lo primero que hice fué revisar la fuente y oh sorpresa!!! un archivo excel. No era novedad para mí, pero siempre he desconfiado de cuando la fuente es un excel, no es la primera vez que sufro con el tema.
Lo primero que me advirtieron es que el archivo no se podía manipular de manera manual (usuarios, esas bellas criaturas que nos proveen el salario pero que nos hacen salir canas verdes....). Esta parte del proceso me auguraba cosas muy complicadas en mi integración. En efecto me salió un problemita bastante molesto de solucionar.
Al crear un Excel Source, el Excel Connection Manager tomaba aparentemente bien los campos, sin embargo aveces me traía unos datos nulos que en el archivo si venían con información. Cuando empecé a diagnosticar el problema, lo primero que noté es que la columna que generaba el lío con los nulos tiene una combinación entre datos numéricos y datos alfanuméricos. Resulta que según el siguiente post, el amigo Excel Source revisa los primeros ocho campos y decide el tipo de dato por mí (tan proactivo el amigo).
Resulta que si el tipo de dato que detecta es numérico, los datos alfanuméricos los vuelve null. La solución obvia era manipular el archivo, pasar la columna a texto y todos somos felices... pero en mi caso no es posible, el archivo no puede tener interacción humana.
Intenté con las soluciones de este post https://social.msdn.microsoft.com/Forums/sqlserver/en-US/54dc94c5-280b-41e5-82fe-26925f9eb95b/ssis-excel-data-source-numeric-values-returned-as-null
primero con la cadena de conexión agregando la línea IMEX=1; pero no funcionó... seguí revisando y la otra opción era hacer un cambio en la llave HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel en el registro de la máquina.
http://sqlcastle.weebly.com/ssis/in-ssis-excel-data-source-column-values-are-returned-as-null
Ya me imaginaba yo a los de IT peleando por la manipulación del registro, por lo que tampoco representaba una solución viable para mí.
Continuando con mi investigación, encontré que tanto en stack overflow como en MS me daban solo la solución de manipular el archivo. Aquí los links:
Dado que el tiempo apremiaba solo se me ocurrió manipular el archivo de manera automática. Encontré entonces algo muy interesante.
Pensé en crear una aplicación de consola que me permitiera la manipulación del archivo trnsformádolo en uno plano (csv o txt separado por tabs). encontré entonces una hermosa librería llamada LinqToExcel.
Esta librería es genial por que me permite hacer una manipulación brutal del archivo sin mayor complique. El desarrollo del componente no me tomó más de dos horas (contando la curva de aprendizaje del uso del componente) así que solo me quedaba enfrentarme al tema del rendimiento. Cual sería mi sorpresa cuando leyendo registro por registro, campo por campo, logré pasar 31.118 registros en 10 segundos aproximadamente (contando con que mi máquina de desarrollo no es un servidor realmente).
creé la librería para la manipulación de acuerdo a mis necesidades y funcionó correctamente. Aquí el código:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LinqToExcel;
using System.IO;
using System.Web;
namespace ExcelManager
{
public class ExcelManager
{
public string FilePath { get; set; }
public string FlatFilePath { get; set; }
public string SheetName { get; set; }
public string FileExtension { get; set; }
public string Delimitator { get; set; }
public string CreateCsv()
{
string sResult = string.Empty;
try
{
DateTime horainicio = DateTime.Now;
FileInfo resultFile = new FileInfo(FlatFilePath + "." + FileExtension);
StreamWriter writer;
if (!resultFile.Exists)
{
writer = resultFile.CreateText();
writer.Close();
}
else
{
resultFile.Delete();
}
var excelFile = new ExcelQueryFactory(FilePath);
List<string> workSheets = excelFile.GetWorksheetNames().ToList();
var data = (from a in excelFile.Worksheet(workSheets[0])
select a).ToList();
string sdata = string.Empty;
writer = new StreamWriter(resultFile.FullName);
foreach (var item in data)
{
for (int i = 0; i < item.Count - 1; i++)
{
if (i < item.Count - 2)
sdata += fixText(item[i]) + Delimitator.ToString();
else
sdata += fixText(item[i]);
}
writer.WriteLine(sdata);
sdata = string.Empty;
}
DateTime horafin = DateTime.Now;
TimeSpan tspan = horafin.Subtract(horainicio);
sResult = writer.Encoding.EncodingName + " " +tspan.Seconds.ToString();
writer.Close();
}
catch (Exception ex)
{
sResult = ex.Message;
//throw;
}
return sResult;
}
private string fixText(Cell cell)
{
string sResultText = string.Empty;
sResultText = System.Web.HttpUtility.HtmlDecode(cell.Value.ToString()).Trim();
return sResultText;
}
}
}
La librería LinqToExcel se puede instalar desde NuGet... así o mas contento?
Ahora verán un método llamado fixText. Resulta que el archivo traía caracteres locos en lugar de tildes o el caracter "Ñ" o "ñ". revisando, el caracter es de tipo html encoding. Cinco minutos de revisión encontré como solucionar el tema usando la System.Web.HttpUtility que con el método HtmlDecode me convertía el campo en un string normalito. Con esto pude solucionar el inconveniente.
Sigo insistiendo para ver si MS da una solución más "natural" al problema.
cualquier inquietud adicional, siéntanse libres de dejar sus comentarios.
Un abrazo
Piojo Dev
