How to Create a CSV file Writer

Here's how you create a CSV file that will open in Excel. It is a csv (Comma spaced Value) file with double quotes " as identifiers.

Notice that fileStream.setLength(0) makes it so you will overwrite whatever data is in the file you are saving to.

private void ButtonRunCSV_Click(object sender, EventArgs e)
{


if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
String saveFileName = saveFileDialog1.FileName;

FileStream fileStream = new FileStream(saveFileName, System.IO.FileMode.OpenOrCreate);
fileStream.SetLength(0);

StreamWriter sw = new StreamWriter(fileStream);

sw.WriteLine("Company Name,"
+ "Phone Number,"
+ "Call Date,"
+ "Call Length,"
+ "First Name,"
+ "Last Name,"
+ "Contact Title,"
+ "Gender,"
+ "Address,"
+ "City,"
+ "State,"
+ "Zip,"
+ "Country,"
+ "Email,"
+ "Fax,"
+ "Second Phone Number,"
+ "Website,"
+ "Google Verified,"
+ "Yahoo Verified,"
+ "Indexed Pages Online,"
+ "Site Links,"
+ "Site References,"
+ "Google Reviews,"
+ "Yahoo Reviews,"
+ "Web Reviews,"
+ "Annual Sales,"
+ "Employees,"
+ "SIC Code,"
+ "Industry,"
+ "Company Description,"
+ "Latitude,"
+ "Longitude");

foreach (Lead l in leads)
{
lead = l;

findLeadInfo();

if (!lead.IsClient)
{

string newLine = "";

newLine += (!String.IsNullOrEmpty(lead.CompanyName)) ? ToCSVDelimitedString(lead.CompanyName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.PhoneNumber)) ? ToCSVDelimitedString(lead.PhoneNumber) + "," : ",";
newLine += (lead.CallDate != DateTime.MinValue) ? ToCSVDelimitedString(lead.CallDate.ToString("MMM d, yyyy h:mm tttt") + " PST") + "," : ",";
newLine += (lead.SecondsOnPhone != -1) ? ToCSVDelimitedString(TimeSpan.FromSeconds(lead.SecondsOnPhone).Minutes.ToString() + " Min") + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactFirstName)) ? ToCSVDelimitedString(lead.ContactFirstName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactLastName)) ? ToCSVDelimitedString(lead.ContactLastName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactTitle)) ? ToCSVDelimitedString(lead.ContactTitle) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactGender)) ? ToCSVDelimitedString(lead.ContactGender) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Address)) ? ToCSVDelimitedString(lead.Address) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.City)) ? ToCSVDelimitedString(lead.City) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.State)) ? ToCSVDelimitedString(lead.State) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ZipCode)) ? ToCSVDelimitedString(lead.ZipCode) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Country)) ? ToCSVDelimitedString(lead.Country) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Email)) ? ToCSVDelimitedString(lead.Email) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.FaxNumber)) ? ToCSVDelimitedString(lead.FaxNumber) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.PhoneNumber2)) ? ToCSVDelimitedString(lead.PhoneNumber2) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Website)) ? ToCSVDelimitedString(lead.Website) + "," : ",";
newLine += ((lead.GoogleVerified)) ? "Yes," : ",";
newLine += ((lead.YahooVerified)) ? "Yes," : ",";
newLine += (!String.IsNullOrEmpty(lead.IndexedPages)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.IndexedPages)) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SiteLinks)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.SiteLinks)) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SiteReferences)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.SiteReferences)) + "," : ",";
newLine += (lead.GoogleReviewsCount > 0) ? ToCSVDelimitedString(lead.GoogleReviewsCount.ToString()) + "," : ",";
newLine += (lead.YahooReviewsCount > 0) ? ToCSVDelimitedString(lead.YahooReviewsCount.ToString()) + "," : ",";
newLine += (lead.WebReviewsCount > 0) ? ToCSVDelimitedString(lead.WebReviewsCount.ToString()) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.AnnualSales)) ? ToCSVDelimitedString(lead.AnnualSales) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Employees)) ? ToCSVDelimitedString(lead.Employees) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SICCode)) ? ToCSVDelimitedString(lead.SICCode) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Industry)) ? ToCSVDelimitedString(lead.Industry) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Description)) ? ToCSVDelimitedString(lead.Description) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Latitude)) ? ToCSVDelimitedString(lead.Latitude) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Longitude)) ? ToCSVDelimitedString(lead.Longitude) + "," : "";


sw.WriteLine(newLine);
}
}

sw.Close();
}



}


private String ToCSVDelimitedString(String field)
{
return "\"" + field.Replace("\"", "\"\"") + "\"";
}


private String GetTheNumberWithinTheTag(String tag)
{
int last = 0;
last = tag.IndexOf('<', 1);

if (last > 0)
return tag.Substring(tag.IndexOf('>') + 1, last - tag.IndexOf('>') - 1);
else
return tag.Substring(tag.IndexOf('>') + 1, tag.Length - tag.IndexOf('>') - 1);
}

Connecting Two Remote Databases in a single SQL Query

Background

Every Microsoft SQL Server database, there is a folder called Server Objects. Within that folder there is a folder called "Linked Servers". If you right click on that folder and hit New Linked Server, it will open up a new linked server wizard.

New Linked Server Wizard

By following the steps there, you can setup a new linked server.

First, type in the name or IP address of the server where it says "Linked server". For example: "1.0.9.46".

For a remote SQL database choose SQL Server as the server type. Then select the security page from the left, and enter the login credentials under the "Be made using this security context" option: Enter the username where it says remote login, and the password below where it says "With password."


Creating a Query using the Linked server.

Once your linked server is setup, you can execute a query like the following:

SELECT *
FROM
[1.0.9.46].DatabaseName.dbo.tableName t
JOIN users ON user.ID = t.userID;

Enjoy!!!

The Ultimate Phone Number Parsing method

The ToNumbers method basically takes a string and turns it into only numbers using the fact that numbers are characters between 47 and 58.

public static string toDataBasePhoneNumber(string phoneNumber)

{

String cleanPhone = String.Empty;

cleanPhone = ToNumbers(phoneNumber);

if (cleanPhone.StartsWith("1"))

cleanPhone = cleanPhone.Substring(1);

return cleanPhone;

}

public static string UltimateToNumbers(string phone)

{

string str = phone;

str = str.Trim().ToUpper();

string check = toDataBasePhoneNumber(str);

if (check.Length == 10)

return check;

else if (check.Length less than 3)

return str;

if (str.Length less than 10)

return str;

if (str.Contains("/"))

str = str.Substring(0, str.IndexOf("/"));

if (str.Contains(","))

str = str.Substring(0, str.IndexOf(","));

if (str.Contains(" OR"))

str = str.Substring(0, str.IndexOf(" OR"));

check = toDataBasePhoneNumber(str);

if (check.Length == 10)

return check;

if (str.Contains("EXT"))

str = str.Substring(0, str.IndexOf("EXT"));

else if (str.Contains("POSTE"))

str = str.Substring(0, str.IndexOf("POSTE"));

else if (str.Contains(" X"))

str = str.Substring(0, str.IndexOf(" X"));

else if (str.Contains(" #"))

str = str.Substring(0, str.IndexOf(" #"));

check = ToNumbers(str);

if (check.Length == 10)

return check;

if (str.Substring(9).Contains("("))

{

check = toDataBasePhoneNumber(str.Substring(9));

if (check.Length == 10)

return check;

}

str = FromLettersToNumbers(str);

return toDataBasePhoneNumber(str);

}

///

/// Takes any 1 character string and if the character is a letter it will change it to the number

/// that it would be on the keypad e.g. FREE = 3733.

///

/// A string converted

/// a string of all numbers

public static string FromLettersToNumbers(string numberWithUpperCaseCharacters)

{

string s = numberWithUpperCaseCharacters;

s = s.Replace("A", "2");

s = s.Replace("B", "2");

s = s.Replace("C", "2");

s = s.Replace("D", "3");

s = s.Replace("E", "3");

s = s.Replace("F", "3");

s = s.Replace("G", "4");

s = s.Replace("H", "4");

s = s.Replace("I", "4");

s = s.Replace("J", "5");

s = s.Replace("K", "5");

s = s.Replace("L", "5");

s = s.Replace("M", "6");

s = s.Replace("N", "6");

s = s.Replace("O", "6");

s = s.Replace("P", "7");

s = s.Replace("Q", "7");

s = s.Replace("R", "7");

s = s.Replace("S", "7");

s = s.Replace("T", "8");

s = s.Replace("U", "8");

s = s.Replace("V", "8");

s = s.Replace("W", "9");

s = s.Replace("X", "9");

s = s.Replace("Y", "9");

s = s.Replace("Z", "9");

return s;

}

Testing It:

string[] separator = new string[] { "<;>" };

string colonSeparatedNumbers = "866-SYNLawn (866-796-5296)<;>(250) 881-7684 / (250) 361-6121<;>780-818-5919 or 306-468-7044<;>1-888-SILPADA (745-7232)<;>(778)233-1235 / (604) 729-9787<;>Fax: (262) 246-4528<;>678-740-0800<;>+91 80 41571900,+91 98450 08720<;>1-868-638-2633 Ext. 2262<;>(604) 730-0321 / (604) 218-0108<;>1-918-712-RIBS<;>(888) 267-0986 x702<;>514 341-5544 poste 280<;>301-475-2200, Ext 100<;>215-541-3211 X108<;>(512)795-9684 ext.101<;>305-532-LIME (5463)<;>(450) 420-9333 #225<;>1-877-KOKOFIT<;>(800) CORN-COB<;>505-766-KEVA<;>403.207.5850 ext. 232<;>1-800-995-JUNK(5865)<;>1-866-35 JUNGA<;>Fax: 212.851.8198<;>513.831.2100<;>(323)936-3300 Ext. 107<;>(416)291-8551 / (647) 886-6863<;>1-888-Gel-Bedz (435-2339)<;>1-817-4161-INK (465)<;>(888) 64-LASER<;>513-563-8339, ext. 135<;>(877) GET-HSWT<;>(61) 02 9362 4<;>0000000<;>Schiller Park<;>1 905 599 6476<;>800-367-5159<;>(410) 822-8879<;>";

string[] spliter = colonSeparatedNumbers.Split(separator, StringSplitOptions.RemoveEmptyEntries);

foreach (String s in spliter)

{

Console.WriteLine("Number: {0} Converted to: -> {1}", s, Text.UltimateToNumbers(s));

}

Parsing DateTime strings

Here is how to parse a date that is in the format of mm/dd/yyyy:

// Parse string with date but no time component.

string dateString, format;

DateTime result;

CultureInfo provider = CultureInfo.InvariantCulture;

// Parse date-only value with invariant culture.

dateString = "06/15/2008";

format = "d";

try

{

result = DateTime.ParseExact(dateString, format, provider);

Console.WriteLine("{0} converts to {1}.", dateString, result.ToString());

}

catch (FormatException)

{

Console.WriteLine("{0} is not in the correct format.", dateString);

}

Copying Code

The object of copying code is to save time. However, I have discovered by sad and repeated experience that copying code more often than not will result in bugs and hard to catch problems that will cause 10 -12 times loss in time trying to solve it. In other words, every time you copy code, you may be able to write code a lot faster, but you will in the end spend 10 times the amount of time you would have if you had simply written every line essentially from scratch.

That being said, there are a few safe ways to reuse portions of code that is essentially guaranteed not to cause unintended glitches down the road (as opposed to those intended glitches that developers like to put in their code for fun!)

Here are some suggestions to effectively copy code:

1. Copy smaller portions. Instead of copying large blocks of code with many variables logical structures and the like, copy the methods that you will reuse a lot, and paste those whenever needed.

2. Write in example basic types and classes that will cause errors unless given relevant names. This is probably the most effective suggestion. The most common problem that I have found in copying code is when I copy a variable from one portion of the code elsewhere, and then forget to change its name to fit the context.

For example,

Let's say you have a class that parses the html of a web page, called parser.

Parser parser = new Parser(html);

you grab some data

parser.MoveToEndOf("");

parser.ReadToStartOf("

");

string tableData = parser.Selected;

Then you go to another page and want to scrap the same data there;

Parser parser2 = new Parser(newHtml);

Then you paste your code

parser.MoveToEndOf("");

parser.ReadToStartOf("

");

Forgetting that your parser for the new page is called parser2, not parser. Well, you may have saved yourself 2 minutes of time by pasting that code. But, in the end you will spend 20 minutes to a half hour hunting down where your code is going wrong.

Instead if you were to create some code like

exampleParser.MoveToEndOf("");

exampleParser.ReadToStartOf("

");

and then paste that around, the code would not compile until you changed the name. It's the same reason why some people put

while (1 ==2)

in their code to make sure there is never an infinite loop until they have tested their logic.

3. Copy the logic portions of code that have no variables in them . Sometimes copying your logical structure will save you the care of having to rethink how to logically go about doing something, and focus on the details.

Conclusion

This article is designed to make the programmer rethink the way they code, and to take the long path at first so that code is built without bugs. Copying code from one portion to another is a sure way to introduce bugs into your code that will be hard to catch at a glance, since the variables you copy are existing ones. Change the names or don't copy at all. In the end you will be a more efficient coder.