Archives for January 2014

Equivalent of Oracle PL/SQL TRIM function in SQL Server (T-SQL)

In PL/SQL, when the TRIM function is called with just the data parameter it will strip the leading and trailing spaces.   You can accomplish the same functionality in t-sql by using two t-sql functions:  LTRIM (strips leading spaces) and RTRIM (strips trailing spaces).  An example of accomplishing this would be the following:

One thing to note is that TRIM with the LEADING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the LTRIM function in t-sql;  TRIM with the TRAILING parameter and ‘ ‘ as the character to be trimmed is exactly the equivalent of the RTRIM function in t-sql.

You can also create a user defined function for TRIM in your database.  The following listing below will do that for you.

After running the above script you could test it by running the following:

References

 PL/SQL TRIM

TSQL LTRIM

TSQL RTRIM

Writing CSV files using CSVHelper package (C#, IEnumerable)

This is a follow-up on my post on how to read a CSV file using Josh Close’s CsvHelper.  CsvHelper is a fast and flexible .NET library for reading and writing CSV files.

Below I will now show how to write CSV files using CsvHelper.  The example will actually be reading a CSV file and then writing the contents of that CSV into another but doing it three times.  Each time we do this we will use another method (WriteRecords for all records at once, WriteRecord for writing one record, and WriteField for writing one field).

Our sample input CSV file will look like the following.

CommonName,FormalName,TelephoneCode,CountryCode

Argentina,Argentine Republic,54,ARG
Armenia,Republic of Armenia,374,ARM
Australia,Commonwealth of Australia,61,AUS
Austria,Republic of Austria,43,AUT
“Bahamas, The”,Commonwealth of The Bahamas,-241,BHS
Bangladesh,People’s Republic of Bangladesh,880,BGD

Note this CSV file is a simpler file based on the file which is located here.

The first thing to do is to Install CsvHelper.  To do this run the following command in the Package Manager Console:

Now add CsvHelper to the program by adding:

The next step is to create a class which has properties with the same name of the column headings found in the csv file.  Below you will find an example of a class which does this:

Finally create an instance of CSVReader and CSVWriter  and invoke the GetRecords method using the DataRecord class to read the CSV file. 

Once we have the completed we are now ready to write the file out.  We first will write the entire file out by using the WriteRecords method.  Note this method will also normally write the header out automatically.

We then write the records out again but this time we will do so by iterating thru the records collection and writing the entire record using the WriteRecord method and also by writing the fields of the record using the WriteField method.

One point to note that if you were writing a file using the WriteRecord or WriteField methods, you will not get a header record in the file.   If you wish to have a header record us the WriteHeader method (example is commented out in final code).

A completed example can be found below:

 

You can download this sample code with CsvHelper and CSV file here:

%d bloggers like this: