There are many approaches to generate CSV files from the database data. One simple way is to use string builder and just append the comma separated values from database with header in the first row. But this approach has some drawbacks as the plain strings don't handle commas in the data strings and also some speacial characters. This is where the CSV helper (https://www.nuget.org/packages/CsvHelper/) nugget package comes in handy.
But nonetheless if your data has no commas/ string data and you don't want external nugget packages to generate CSV then using string builder is preferred.
Generate CSV using String builder
private static void DownloadValidations(IList<Validation> validations )
{
var stringBuilder = new StringBuilder(); stringBuilder.AppendLine("ValidationId,ValidationTimeStamp,UserId,ImageId,ImageDate,ImageSource,Crop,CropTypeId,ImageLocation(x:y),Country,CountryIsoCode, ImageUrl");
foreach (var validation in validations)
{
var image = validation.Image;
var row =
$"{validation.Id},{validation.CreationTime:dd/MM/yyyy},{validation.User.Id},{image.Id},{image.Date:dd/MM/yyyy},{image.ImageSource},{validation.Classification.Name},{validation.Classification.Id},{$"{image.Location.X}:{image.Location.Y},{image.Country.Name},{image.Country.Code},{image.Url}"}";
stringBuilder.AppendLine(row);
}
File.WriteAllText(".\\validations.csv", stringBuilder.ToString());
}
public class Validation
{
public virtual User User { get; set; }
public virtual Image Image { get; set; }
public virtual Classification Classification { get; set; }
public string Platform { get; set; } // browser or mobile etc
public double TimeNeeded { get; set; } // comes from importer tool?
public bool IsCorrect { get; set; } // is classification correct?
public string Version { get; set; } //app versions
public string IpAddress { get; set; } // device Ip
}
Generate CSV using CSVHelper
public void GenerateCsv(IList<survey> surveys )
{
var memoryStream = new MemoryStream();
using (var streamWriter = new StreamWriter(memoryStream, Encoding.UTF8))
{
var csvWriter = new CsvSerializer(streamWriter, CultureInfo.InvariantCulture);
await csvWriter.WriteAsync(new[]
{
"Id", "CreationTime", "CreatorId", "StorageType", "x_lat", "y_long", "capacity",
"lifetime", "location",
"locationOther",
"food", "foodOther", "Protected", "ProtectedRemarks", "PercentConsumed", "PercentSold",
"Storageduration", "Storagedurationremarks", "owner",
"ownerOther", "differences", "improvements", "remarks"
});
await csvWriter.WriteLineAsync();
foreach (var survey in surveys)
{
await csvWriter.WriteAsync(new[]
{
$"{survey.Id}", $"{survey.CreationTime:s}", $"{survey.CreatorId}", $"{survey.StorageType}",
$"{survey.Location.Coordinate.X}", $"{survey.Location.Coordinate.Y}",
$"{extraData.Capacity}", $"{extraData.Lifetime}", $"{extraData.Location:G}",
$"\"{extraData.LocationOther}\"", $"{extraData.Food:G}", $"\"{extraData.FoodOther}\"",
$"{extraData.Protected:G}", $"\"{extraData.ProtectedRemarks}\"",
$"{extraData.PercentConsumed}", $"\"{extraData.PercentSold}\"",
$"{extraData.Storageduration}", $"\"{extraData.Storagedurationremarks}\"",
$"{extraData.Owner:G}", $"\"{extraData.OwnerOther}\"", $"\"{extraData.Differences}\"",
$"\"{extraData.Improvements}\"", $"\"{extraData.Remarks}\""
});
await csvWriter.WriteLineAsync();
}
await streamWriter.FlushAsync();
}
File.WriteAllText(".\\surveys.csv", stringBuilder.ToString());
}
public class survey
{
public long SurveyId { get; set; }
public DateTime CreatedOn { get; set; }
public Geometry Location { get; set; }
public double UserLat { get; set; }
public double UserLng { get; set; }
public string Crop { get; set; }
public string CropOther { get; set; }
public string Phenology { get; set; }
public string PhenologyOther { get; set; }
public string Damage { get; set; }
public string DamageOther { get; set; }
public string Manage { get; set; }
public string ManageOther { get; set; }
public string Remarks { get; set; }
public string[] Images { get; set; }
public int PlantHeight { get; set; }
public string DateObservation { get; set; }
public string DateSurveyCreation { get; set; }
public string PolygonWkt { get; set; }
}
The CSVHelper can handle strings with commas and all special characters. So based on your need if there is a requirement choose this nugget package.
Conclusion
If you need a very simple CSV generator then go for stringbuilder and append your data to generate CSV string and save it to a file
If you need a more advanced CSV generator which can handle any sort of strings, and don't mind external nugget packages then go for csvhelper, which has matured over the years and is open source.
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
