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.
