Tuesday, September 18, 2007

Populate Dataset from CSV Delimited File

While searching and searching for the best method of importing a comma delimited file into a dataset, I ran across this blog: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=6015&SiteID=1 . This code is great, before locating this post, I had almost written a whole page of code with three methods. Not including the code written before hand where as I was attempting to first convert the csv file to xml from within code, and before that reading the file to a data table. This code made life a lot easier, link to blog located above. I also added a method for saving file locally and naming it, also going back after file read into data set and deleting file.


string FileName = SaveFile(File, ".csv");
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(FileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + Path.GetFileName(FileName), conn);
DataSet ds = new DataSet("Temp");
adapter.Fill(ds);
this.InventoryFile = ds.Tables[0];
conn.Close();
System.IO.File.Delete(FileName);


//Save File and Name
protected string SaveFile(byte[] bytes, string PassFileExtention)
{

try
{
string DestDirectory = config.GetStringValue("", "CustomBoxTempDirectory", "");

if (DestDirectory[DestDirectory.Length - 1] != '\\')
DestDirectory = DestDirectory + "\\";

if (!Directory.Exists(DestDirectory))
Directory.CreateDirectory(DestDirectory);

MemoryStream plainStream = new MemoryStream(bytes);
FileStream SaveFileSteam;
string FileName = DestDirectory + "CustomBoxUpload" + this.AssociateID + PassFileExtention;

try
{
//Make sure this file does not exist before we save off the next one.
System.IO.File.Delete(FileName);
}
catch { }
try
{
SaveFileSteam = System.IO.File.Create(FileName);
}
catch
{
System.IO.File.Delete(FileName);
SaveFileSteam = System.IO.File.Create(FileName);
}

plainStream.WriteTo(SaveFileSteam);
SaveFileSteam.Close();
return FileName;
}
catch
{
return null;
}
}

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Thanks! Works great even now with 4.5.2 - excellent stuff

    ReplyDelete

Visual Studio 2017 + Access is denied + Exited with code 5. Please verify that you have sufficient rights to run this command + nuget.exe + Cygwin + Windows 10 + chmod +x

  Visual Studio 2017 + Access is denied + Exited with code 5. Please verify that you have sufficient rights to run this command + nuget....