En este artículo te vamos a explicar cómo desarrollar un IDataReader para leer archivos CSV en .NET. A menudo, como desarrolladores, nos encargan la tarea de parsear un archivo CSV para obtener una información que necesitamos, ya sean productos de una tienda, los clientes de una empresa o cualquier tipo de contenido. Para empresas externas es más fácil proporcionarnos este tipo de archivos que una base de datos en sí, ya que están más acostumbrados a trabajar con software de ofimática y más concretamente con archivos excel. Como ya sabéis, los archivos excel se pueden guardar como archivos CSV, que son archivos con la información que contenía el excel comprimida y delimitada por caracteres clave.
.NET actualmente cuenta con un parseador CSV, pero por desgracia se encuentra oculto dentro de los namespaces de VB.NET. Además, es muy básico y no proporciona interoperabilidad de datos directa. A menudo, los programadores se toman el trabajo de analizar archivos CSV como una tarea simple y terminan desarrollando tareas mucho más complejas e implementando módulos que no funcionan con las librerías de enlace de datos .NET y ADO.NET incorporadas.
Todo esto, usualmente, resulta en una librería que no se puede transportar, que es baja en eficiencia y no sigue los requisitos del manejo de nombres de columna para que coincidan con los campo de base de datos. La librería se crea como un esfuerzo para crear un módulo generalizado que soporte los requerimientos más comunes que un parser CSV puede conllevar.
- Sigue la interfaz de IDataReader la cual agrega capacidades para usarse en conjunto con las librerías de ADO.NET comunes como SqlBulkCopy.
- El módulo contiene disposiciones para añadir un campo ficticio adicional si es necesario y cambiar el nombre de un encabezado existente para que coincida con la tabla de base de datos.
- El módulo proporciona un algoritmo de alta velocidad para leer archivos CSV de gran tamaño. He testeado este módulo con archivos de 2 GB y mostró un rendimiento igual o mejor que cualquiera de las librerías que podemos encontrar en la web.
Aquí os dejo el código del módulo para parsear archivos CSV. Espero que sea de utilidad.
El código
/*Regular read from CSV file and do whatever you usually do. */ CSVDataReader rdr = new CSVDataReader(@"......Data.csv"); rdr.AddVirtualColumn("Virtual_Column1", "1"); string header = ""; foreach (var item in rdr.Header) { header = header + " - " + item; } Console.WriteLine(header); int i = 1; while (rdr.Read()) { Console.WriteLine(rdr[0] + " - " + rdr[rdr.Header[1]] + " - " + rdr[rdr.Header[2]] + " - " + rdr[rdr.Header[3]]); rdr.UpdateVirtualColumnValue("Virtual_Column1", (i++).ToString()); rdr.Read(); } Console.ReadLine(); rdr.Close(); Console.Clear(); /* Open the CSV datareader and pass the data to SQL bulkcopy object to bulkcopy to * dump the CSV file to db. This is the reason why I developed this utility class and * it works blazingly fast for my purpose, faster than any modules * I could find online todate. * To make bulkcopy work, the column mappings must be exactly same. Use rdr.RenameCSVHeader * for renaming the columns. Default CSV header('COL_'+ X) is different or header * is missing from file. Use rdr.AddVirtualColumn for adding a CSV column * if you want to add custom columns. */ rdr = new CSVDataReader(@"......Data.csv"); System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(destconnectionStr, SqlBulkCopyOptions.UseInternalTransaction); bcp.BatchSize = 500; bcp.DestinationTableName = destinationTableName; bcp.NotifyAfter = 500; bcp.SqlRowsCopied += (sender,e) => { Console.WriteLine("Written: " + e.RowsCopied.ToString()); }; bcp.WriteToServer(rdr);
Implementación
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Data; namespace System.Data { public class CSVDataReader : IDataReader, IDisposable { private StreamReader _file; private char _delimiter; /* stores the header and values of csv and also virtual*/ private string _virtualHeaderString = "", _csvHeaderstring = "", _csvlinestring = "", _virtuallineString = ""; private bool _firstRowHeader = true; private string[] _header; /// /// Returns an array of header names as string in the order of columns /// from left to right of csv file. If CSV file doesn't have header then a dummy header /// with 'COL_' + 'column position' will be returned. This can be manually renamed calling /// 'RenameCSVHeader' /// public string[] Header { get { return _header; } } /* * The values of header and values must be in same order. So using this collection. * This collection stores header key as header name and its related value as value. * When the value of a specific * header is updated the specific key value will be updated. * For Original Header values from the csv file the values will be null. * This is used as a check and identify this is a csv value or not. */ private System.Collections.Specialized.OrderedDictionary headercollection = new System.Collections.Specialized.OrderedDictionary(); private string[] _line; /// /// Returns an array of strings from the current line of csv file. /// Call Read() method to read the next line/record of csv file. /// public string[] Line { get { return _line; } } private int recordsaffected; private bool _iscolumnlocked = false; /// /// Creates an instance of CSV reader /// ///Path to the csv file. ///delimiter character used in csv file. ///specify the csv got a header in first row or not. /// Default is true and if argument is false then auto header 'ROW_xx will be used as per /// the order of columns. public CSVDataReader(string filePath, char delimiter = ',', bool firstRowHeader = true) { _file = File.OpenText(filePath); _delimiter = delimiter; _firstRowHeader = firstRowHeader; if (_firstRowHeader == true) { Read(); _csvHeaderstring = _csvlinestring; _header = ReadRow(_csvHeaderstring); foreach (var item in _header) //check for duplicate headers and create a header record. { if (headercollection.Contains(item) == true) throw new Exception("Duplicate found in CSV header. Cannot create a CSV reader instance with duplicate header"); headercollection.Add(item, null); } } else { //just open and close the file with read of first line to determine how many //rows are there and then add default rows as row1,row2 etc to collection. Read(); _csvHeaderstring = _csvlinestring; _header = ReadRow(_csvHeaderstring); int i = 0; _csvHeaderstring = ""; foreach (var item in _header)//read each column and create a dummy header. { headercollection.Add("COL_" + i.ToString(), null); _csvHeaderstring = _csvHeaderstring + "COL_" + i.ToString() + _delimiter; i++; } _csvHeaderstring.TrimEnd(_delimiter); _header = ReadRow(_csvHeaderstring); Close(); //close and repoen to get the record position to beginning. _file = File.OpenText(filePath); } _iscolumnlocked = false; //setting this to false since above read is called //internally during constructor and actual user read() didnot start. _csvlinestring = ""; _line = null; recordsaffected = 0; } public bool Read() { var result = !_file.EndOfStream; if (result == true) { _csvlinestring = _file.ReadLine(); if (_virtuallineString == "") _line = ReadRow(_csvlinestring); else _line = ReadRow(_virtuallineString + _delimiter + _csvlinestring); recordsaffected++; } if (_iscolumnlocked == false) _iscolumnlocked = true; return result; } /// /// Adds a new virtual column at the beginning of each row. /// If a virtual column exists then the new one is placed left of the first one. /// Adding virtual column is possible only before read is made. /// ///Name of the header of column ///Value for this column. This will be returned for every row /// for this column until the value for this column is changed through method /// 'UpdateVirtualcolumnValues' /// Success status public bool AddVirtualColumn(string columnName, string value) { if (value == null) return false; if (_iscolumnlocked == true) throw new Exception("Cannot add new records after Read() is called."); if (headercollection.Contains(columnName) == true) throw new Exception("Duplicate found in CSV header. Cannot create a CSV readerinstance with duplicate header"); headercollection.Add(columnName, value); //add this to main collection so that //we can check for duplicates next time col is added. if (_virtualHeaderString == "") _virtualHeaderString = columnName; else _virtualHeaderString = columnName + _delimiter + _virtualHeaderString; _header = ReadRow(_virtualHeaderString + _delimiter + _csvHeaderstring); if (_virtuallineString == "") _virtuallineString = value; else _virtuallineString = value + _delimiter + _virtuallineString; _line = ReadRow(_virtuallineString + _delimiter + _csvlinestring); return true; } /// /// Update the column header. This method must be called before Read() method is called. /// Otherwise it will throw an exception. /// ///Name of the header of column ///Value for this column. This will be returned for every row /// for this column until the value for this column is changed through method /// 'UpdateVirtualcolumnValues' /// Success status public bool RenameCSVHeader(string oldColumnName, string newColumnName) { if (_iscolumnlocked == true) throw new Exception("Cannot update header after Read() is called."); if (headercollection.Contains(oldColumnName) == false) throw new Exception("CSV header not found. Cannot update."); string value = headercollection[oldColumnName] == null ? null : headercollection[oldColumnName].ToString(); int i = 0; foreach (var item in headercollection.Keys) //this collection does no have a position //location property so using this way assuming the key is ordered { if (item.ToString() == oldColumnName) break; i++; } headercollection.RemoveAt(i); headercollection.Insert(i, newColumnName, value); if (value == null) //csv header update. { _csvHeaderstring = _csvHeaderstring.Replace(oldColumnName, newColumnName); _header = ReadRow(_virtualHeaderString + _delimiter + _csvHeaderstring); } else //virtual header update { _virtualHeaderString = _virtualHeaderString.Replace(oldColumnName, newColumnName); _header = ReadRow(_virtualHeaderString + _delimiter + _csvHeaderstring); } return true; } /// /// Updates the value of the virtual column if it exists. Else throws exception. /// ///Name of the header of column ///Value for this column. /// This new value will be returned for every row for this column until /// the value for this column is changed again /// Success status public bool UpdateVirtualColumnValue(string columnName, string value) { if (value == null) return false; if (headercollection.Contains(columnName) == false) throw new Exception("Unable to find the csv header. Cannot update value."); if (headercollection.Contains(columnName) == true && headercollection[columnName] == null) throw new Exception("Cannot update values for default csv based columns."); headercollection[columnName] = value; //add this to main collection so that //we can check for duplicates next time col is added. _virtuallineString = ""; foreach (var item in headercollection.Values) //cannot use string.replace since //values may be duplicated and can update wrong column. So rebuilding the string. { if (item != null) { _virtuallineString = (string)item + _delimiter + _virtuallineString; } } _virtuallineString = _virtuallineString.TrimEnd(','); _line = ReadRow(_virtuallineString + _delimiter + _csvlinestring); return true; } /// /// Reads a row of data from a CSV file /// /// array of strings from csv line private string[] ReadRow(string line) { List lines = new List(); if (String.IsNullOrEmpty(line) == true) return null; int pos = 0; int rows = 0; while (pos < line.Length) { string value; // Special handling for quoted field if (line[pos] == '"') { // Skip initial quote pos++; // Parse quoted value int start = pos; while (pos < line.Length) { // Test for quote character if (line[pos] == '"') { // Found one pos++; // If two quotes together, keep one // Otherwise, indicates end of value if (pos >= line.Length || line[pos] != '"') { pos--; break; } } pos++; } value = line.Substring(start, pos - start); value = value.Replace("""", """); } else { // Parse unquoted value int start = pos; while (pos < line.Length && line[pos] != _delimiter) pos++; value = line.Substring(start, pos - start); } // Add field to list if (rows < lines.Count) lines[rows] = value; else lines.Add(value); rows++; // Eat up to and including next comma while (pos < line.Length && line[pos] != _delimiter) pos++; if (pos < line.Length) pos++; } return lines.ToArray(); } public void Close() { _file.Close(); _file.Dispose(); _file = null; } /// /// Gets a value that indicates the depth of nesting for the current row. /// public int Depth { get { return 1; } } public DataTable GetSchemaTable() { DataTable t = new DataTable(); t.Rows.Add(Header); return t; } public bool IsClosed { get { return _file == null; } } public bool NextResult() { return Read(); } /// /// Returns how many records read so far. /// public int RecordsAffected { get { return recordsaffected; } } public void Dispose() { if (_file != null) { _file.Dispose(); _file = null; } } /// /// Gets the number of columns in the current row. /// public int FieldCount { get { return Header.Length; } } public bool GetBoolean(int i) { return Boolean.Parse(Line[i]); } public byte GetByte(int i) { return Byte.Parse(Line[i]); } public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) { throw new NotImplementedException(); } public char GetChar(int i) { return Char.Parse(Line[i]); } public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length) { throw new NotImplementedException(); } public IDataReader GetData(int i) { return (IDataReader)this; } public string GetDataTypeName(int i) { throw new NotImplementedException(); } public DateTime GetDateTime(int i) { return DateTime.Parse(Line[i]); } public decimal GetDecimal(int i) { return Decimal.Parse(Line[i]); } public double GetDouble(int i) { return Double.Parse(Line[i]); } public Type GetFieldType(int i) { return typeof(String); } public float GetFloat(int i) { return float.Parse(Line[i]); } public Guid GetGuid(int i) { return Guid.Parse(Line[i]); } public short GetInt16(int i) { return Int16.Parse(Line[i]); } public int GetInt32(int i) { return Int32.Parse(Line[i]); } public long GetInt64(int i) { return Int64.Parse(Line[i]); } public string GetName(int i) { return Header[i]; } public int GetOrdinal(string name) { int result = -1; for (int i = 0; i < Header.Length; i++) if (Header[i] == name) { result = i; break; } return result; } public string GetString(int i) { return Line[i]; } public object GetValue(int i) { return Line[i]; } public int GetValues(object[] values) { values = Line; return 1; } public bool IsDBNull(int i) { return string.IsNullOrWhiteSpace(Line[i]); } public object this[string name] { get { return Line[GetOrdinal(name)]; } } public object this[int i] { get { return GetValue(i); } } } }
Espero que os haya gustado este tutorial y que a partir de ahora sepáis que existe otro modo de parsear archivos CSV con el lenguaje .NET. Una de las prácticas primordiales para un programador es escribir código, y, aunque las librerías nos ayuden mucho en nuestras tareas cotidianas, también esta muy bien el que, como ejercicio, creemos nuestras propias funciones para llevar a cabo una acción. Y si encima funciona mejor que la original, como es el caso, pues mejor que mejor. Si tienes alguna duda respecto al tutorial, no dudes decírnoslo en el formulario de más abajo.