Linq To CSV

This was originally just a temporary post to make sure my syntax highlighting was working. I decided to go ahead and flesh it out and keep it here.

The code below is some C# I’ve used recently to do some custom querying against a SQL database. I was given a list of about 1600 devices that needed to be imported into the database. Some of the work had been done, so I needed to get a list of devices that weren’t already there.

The code uses a library called LINQtoCSV. You can read more about that library here. In fact, you will find all the information you need there, and then some. This snippet is a VERY basic use of the library.

Depending on what I’m working on, I usually start by writing my “proof of concept” snippets in LINQPad. In this case, I only needed this to work once. In other words, I had no need to keep this code running, no need to optimize it, support it, etc… I just needed it to do the job once.

Here’s a few lines from the CSV file I was given. I needed to query my database for devices using the names below (’2B5-A-1′ for example):

Name,Description,ServiceTag,Model,Owner,Zone,ExistingLocation,Building,Location,SRN,MoveDate
2B5-A-1,110V,,1587H24A1,ATC,1,,QTS,100.17,RIGHT,4/26/2010
2A8-A-1,110V,,1587H24A1,ATC,1,,QTS,100.17,LEFT,4/26/2010
AT-EDBO-3560-D15,SWITCH,FOC1036Z0V1,3560G,ATC,1,,QTS,100.17,46,4/26/2010

Diving into the code, I first defined a class that fit each CSV row, essentially defining my column contents:

class Device {
  [CsvColumn(FieldIndex = 1, CanBeNull = true)]
  public string Name { get; set; }

  [CsvColumn(FieldIndex = 2, CanBeNull = true)]
  public string Description { get; set; }

  [CsvColumn(FieldIndex = 3, CanBeNull = true)]
  public string ServiceTag { get; set; }

  [CsvColumn(FieldIndex = 4, CanBeNull = true)]
  public string Model { get; set; }

  [CsvColumn(FieldIndex = 5, CanBeNull = true)]
  public string Owner { get; set; }

  [CsvColumn(FieldIndex = 6, CanBeNull = true)]
  public string Zone { get; set; }

  [CsvColumn(FieldIndex = 7, CanBeNull = true)]
  public string ExistingLocation { get; set; }

  [CsvColumn(FieldIndex = 8, CanBeNull = true)]
  public string Building { get; set; }

  [CsvColumn(FieldIndex = 9, CanBeNull = true)]
  public string Location { get; set; }

  [CsvColumn(FieldIndex = 10, CanBeNull = true)]
  public string SRN { get; set; }

  [CsvColumn(FieldIndex = 11, CanBeNull = true)]
  public string MoveDate { get; set; }
}

Then I created a file description defining a comma as the separator and that the first line has column names. Then I created a context and read the file into an IEnumerable using the class from above:

CsvFileDescription inputFileDescription = new CsvFileDescription {
  SeparatorChar = ',',
  FirstLineHasColumnNames = true
};

CsvContext cc = new CsvContext();

IEnumerable<Device> devices = cc.Read<Device>(@"c:\path\to\Book2.csv", inputFileDescription);

Now that the rows are in an IEnumerable, I can loop through them. For each row, I query my SQL database and get the name of the device found. If no device was found, I populate a new List<>. Last, dump that list to the console (this is a LINQPad function), and write it to a new CSV file.

List<Device> notFound = new List<Device>();

foreach(var device in devices) {

  string name = (
    from e in tblEquipment
    where e.Name == device.Name
    select e.Name
  ).FirstOrDefault();

  if(string.IsNullOrEmpty(name)) {
    notFound.Add(device);
  }
}

notFound.Dump();

cc.Write(notFound, @"C:\path\to\new\file.csv", inputFileDescription);

I’m sure there are better ways to do this, but this wasn’t for any sort of a long term project. This is a quick one-off script I needed for a one-time use purpose.