public CSVRow ReadRow()
{
CSVRow row = new CSVRow();
int c;
string s = "";
bool inEnclosure = false;
while ((c = Read()) != -1)
{
if (inEnclosure)
{
if (c == clEnclosure)
{
inEnclosure = false;
}
else
{
s += (char)c;
}
}
else
{
if (c == clEnclosure)
{
inEnclosure = true;
}
else if (c == clDelimiter)
{
row.Add(s);
s = "";
}
else if (c == (int)'\n')
{
break;
}
}
}
if (inEnclosure)
{
throw new Exception("Unerwartetes Datei-Ende");
}
row.Add(s);
return(row);
}
protected void btnExport_Click(object sender, EventArgs e)
{
SQLDatabase sql = new SQLDatabase(); sql.CommandTimeout = 120;
SQLParamList sqlParams = new SQLParamList()
.Add("@DateRange_Begin", drDateRange.BeginDate)
.Add("@DateRange_End", Convert.ToDateTime(drDateRange.EndDate))
.Add("@SurveyType", ddlSurvey.SelectedValue);
if (ddlProperty.SelectedIndex == 0)
{
sqlParams.Add("@PropertyID", DBNull.Value);
}
else
{
sqlParams.Add("@PropertyID", ddlProperty.SelectedValue);
}
DataSet ds = sql.ExecStoredProcedureDataSet("spData_Export", sqlParams);
if (sql.HasError)
{
TopMessage.ErrorMessage = "Oops. Something went wrong when exporting the data. Please try again. (EDE100)";
}
else
{
string lFileName = string.Format("{0}_{1}", ddlSurvey.SelectedItem.Text, ReportingTools.AdjustAndDisplayDate(DateTime.Now, "yyyy-MM-dd-hh-mm-ss", User));
const string lPath = "~/Files/Cache/";
string lOutput = string.Concat(MapPath(lPath), lFileName);
using (CSVWriter csv = new CSVWriter(lOutput + ".csv"))
{
csv.WriteLine("{0} Data Extract", ddlSurvey.SelectedItem.Text);
csv.WriteLine("Date Range: {0}", drDateRange.BeginDate.HasValue ? String.Format("{0} - {1}", drDateRange.BeginDate.Value.ToString("yyyy-MM-dd"), drDateRange.EndDate.Value.ToString("yyyy-MM-dd")) : "All");
csv.WriteLine("Property: {0}", ddlProperty.SelectedItem.Text);
//CSVRow longLabelRow = new CSVRow();
//CSVRow shortLabelRow = new CSVRow();
CSVRow headerRow = new CSVRow();
foreach (DataColumn dc in ds.Tables[0].Columns)
{
headerRow.Add(dc.ColumnName);
string kv = dc.ColumnName;
//if ( QuestionLookup.ContainsKey( kv ) ) {
// string[] vals = QuestionLookup[kv].Split( '|' );
// longLabelRow.Add( vals[0] );
// if ( vals.Length > 1 ) {
// shortLabelRow.Add( vals[1] );
// } else {
// shortLabelRow.Add( vals[1] );
// }
//} else {
// longLabelRow.Add( "" );
// shortLabelRow.Add( "" );
//}
}
//csv.WriteRow( longLabelRow );
//csv.WriteRow( shortLabelRow );
csv.WriteRow(headerRow);
int colCount = ds.Tables[0].Columns.Count;
foreach (DataRow dr in ds.Tables[0].Rows)
{
CSVRow row = new CSVRow();
for (int i = 0; i < colCount; i++)
{
row.Add(dr[i].ToString());
}
csv.WriteRow(row);
}
csv.Close();
csv.Dispose();
}
//Conversion.DataTableToExcel(lResultSet, ddlQID.SelectedItem.Text, lOutput, true);
hlDownload.Text = "Download File - " + String.Concat(lFileName, ".csv");
hlDownload.NavigateUrl = String.Format("{0}{1}", lPath, String.Concat(lFileName, ".csv"));
}
}
ReadRow(CSVRow _row)
{
int pos = 0;
int rows = 0;
if (_linesQueue.Count != 0)
{
_row.LineText = _linesQueue.Dequeue() as string;
}
else
{
_row.LineText = null;
}
if (String.IsNullOrEmpty(_row.LineText))
{
return(false);
}
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);
}
// True if any row reads.
return(_linesQueue.Count > 0);
}
/// <summary>
/// Load A csv file
/// </summary>
/// <param name="file">Input file</param>
/// <param name="hasHeader">File has a header</param>
/// <param name="sep">Separator character</param>
/// <returns></returns>
public static CSVDataset LoadCSV(string file, bool hasHeader = true, char sep = ';')
{
if (!File.Exists(file))
{
throw new FileNotFoundException(file);
}
var lines = File.ReadAllLines(file);
if (lines.Length == 0)
{
return(null);
}
var headers = new Dictionary <int, string>();
int numOfHeaders;
if (hasHeader)
{
var headerLine = lines[0].Split(sep).ToArray();
numOfHeaders = headerLine.Length;
for (var i = 0; i < headerLine.Length; i++)
{
headers.Add(i, headerLine[i]);
}
}
else
{
var headerLine = lines[0].Split(sep).ToArray();
numOfHeaders = headerLine.Length;
for (var i = 0; i < headerLine.Length; i++)
{
headers.Add(i, "COLUMN" + (i + 1));
}
}
var dataset = new CSVDataset();
dataset.FileName = file;
dataset.Date = DateTime.Now;
dataset.Rows = new List <CSVRow>();
var firstLine = true;
var rowNumber = 1;
foreach (var line in lines)
{
if (hasHeader && firstLine)
{
firstLine = false;
continue;
}
var row = new CSVRow();
row.RowNumber = rowNumber;
var valueArrays = line.Split(sep).ToArray();
if (valueArrays.Length != numOfHeaders)
{
continue;
}
for (var i = 0; i < valueArrays.Length; i++)
{
row.Add(new CSVColumn {
Value = valueArrays[i], ColumnIndex = i, ColumnName = headers[i]
});
}
dataset.Rows.Add(row);
rowNumber++;
}
return(dataset);
}
