/// <summary>
/// Writes a single row to a CSV file.
/// </summary>
/// <param name="row">The row to be written</param>
public void WriteRow(CSVRow row)
{
StringBuilder builder = new StringBuilder();
bool firstColumn = true;
foreach (string value in row)
{
// Add separator if this isn't the first value
if (!firstColumn)
{
builder.Append(',');
}
// Implement special handling for values that contain comma, quote or new line characters
// Enclose in quotes and double up any double quotes
if (value.IndexOfAny(new char[] { '"', ',', (char)10, (char)13 }) != -1)
{
builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
}
else
{
builder.Append(value);
}
firstColumn = false;
}
row.LineText = builder.ToString();
WriteLine(row.LineText);
}
/// <summary>
/// Generates a CSV file from the passed datatable.
/// </summary>
/// <param name="dt">The datatable to convert to the CSV file. Data column names will be in the first row. Note: Currently, newline characters will be replaced by spaces.</param>
/// <param name="outputFile">The full path to the output file.</param>
/// <param name="overwrite">If true, the current file will be erased if it exists. Otherwise, it will be appended.</param>
/// <param name="maxRows">The maximum number of rows to output from the DataTable.</param>
/// <param name="maxCols">The maximum number of columns to output from the DataTable.</param>
public static bool DataTableToCSV(this DataTable dt, string outputFile, bool overwrite, int maxRows, int maxCols)
{
if (dt == null || String.IsNullOrEmpty(outputFile))
{
throw new ArgumentException("The datatable cannot be null and outputFile must be a valid string.");
}
bool rtrn = false;
if (overwrite && File.Exists(outputFile))
{
File.Delete(outputFile);
}
try {
using (CSVWriter csv = new CSVWriter(outputFile)) {
int colCount = 0;
//Get header row
CSVRow header = new CSVRow();
foreach (DataColumn dc in dt.Columns)
{
//Check if we've reached the max col count
colCount++;
if (colCount > maxCols)
{
break;
}
//Add the column name
header.Add(dc.ColumnName.Replace("\r", "").Replace("\n", " "));
}
csv.WriteRow(header);
//Insert the data
int cntr = 0;
foreach (DataRow dr in dt.Rows)
{
CSVRow row = new CSVRow();
for (int cind = 0; cind < dt.Columns.Count; cind++)
{
row.Add(dr[cind].ToString().Replace("\r", "").Replace("\n", " "));
}
csv.WriteRow(row);
cntr++;
if (cntr >= maxRows)
{
break;
}
}
rtrn = true;
}
} catch {
throw;
}
return(rtrn);
}
/// <summary>
/// Takes a CSVReader and converts it into a data table.
/// </summary>
/// <param name="csv">The CSVReader object to convert.</param>
/// <param name="columnNamesInFirstRow">If true, the first row's values will be treated as the data tables column names. Otherwise, they will be loaded as "Column 1", "Column 2", etc.</param>
/// <returns>The converted datatable.</returns>
private static DataTable ConvertCSVToDataTable(CSVReader csv, bool columnNamesInFirstRow)
{
CSVRow row = new CSVRow();
bool firstRow = true;
DataTable dt = new DataTable();
while (csv.ReadRow(row))
{
if (firstRow)
{
//Load the columns
for (int i = 0; i < row.Count; i++)
{
if (columnNamesInFirstRow)
{
dt.Columns.Add(row[i]); //Load from first row
}
else
{
dt.Columns.Add("Column " + (i + 1)); //Load basic name
}
}
if (columnNamesInFirstRow)
{
//If we loaded the column names from the first row, we need to get the next row to load.
if (!csv.ReadRow(row))
{
break;
}
}
firstRow = false;
}
//Load the columns
DataRow dr = dt.NewRow();
for (int col = 0; col < row.Count; col++)
{
dr[col] = row[col];
}
dt.Rows.Add(dr);
}
return(dt);
}
/// <summary>
/// Reads a row of data from a CSV file
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public bool ReadRow(CSVRow row)
{
row.LineText = ReadLine();
if (String.IsNullOrEmpty(row.LineText))
{
return(false);
}
int pos = 0;
int rows = 0;
while (pos < row.LineText.Length)
{
string value;
// Special handling for quoted field
if (row.LineText[pos] == '"')
{
// Skip initial quote
pos++;
// Parse quoted value
int start = pos;
while (pos < row.LineText.Length)
{
// Test for quote character
if (row.LineText[pos] == '"')
{
// Found one
pos++;
// If two quotes together, keep one
// Otherwise, indicates end of value
if (pos >= row.LineText.Length || row.LineText[pos] != '"')
{
pos--;
break;
}
}
pos++;
}
value = row.LineText.Substring(start, pos - start);
value = value.Replace("\"\"", "\"");
}
else
{
// Parse unquoted value
int start = pos;
while (pos < row.LineText.Length && row.LineText[pos] != ',')
{
pos++;
}
value = row.LineText.Substring(start, pos - start);
}
// Add field to list
if (rows < row.Count)
{
row[rows] = value;
}
else
{
row.Add(value);
}
rows++;
// Eat up to and including next comma
while (pos < row.LineText.Length && row.LineText[pos] != ',')
{
pos++;
}
if (pos < row.LineText.Length)
{
pos++;
}
}
// Delete any unused items
while (row.Count > rows)
{
row.RemoveAt(rows);
}
// Return true if any columns read
return(row.Count > 0);
}