Using an XML Format File with Bulk Import in SQL Server 2008

In a previous blog post (http://wardlawclaims.blogspot.com/2010/07/introduction-to-sql-server-2008-data.html ) I provided an introduction to the BULK INSERT statement which can be used to import the contents of a CSV file into a table in a SQL Server database.  In this post I will demonstrate how a format file can be used when the columns in the source file are ordered differently than those in the target table.  Below is the T-SQL used to create the target "Contacts" table:

CREATE TABLE Contacts
(
  FirstName VARCHAR(30) NOT NULL,
  LastName VARCHAR(30) NOT NULL,
  PhoneNumber VARCHAR(25) NOT NULL
);

Next is a screen shot of the source CSV file.

While the Contacts table has columns named "FirstName" and "LastName", the CSV file has corresponding columns named "Given name" and "Surname".  The CSV file also contains an additional column, "Date of Birth" which needs to be ignored since no corresponding column exists in the Contacts table.  The default behavior of the BULK INSERT command expects that the columns in the source and target appear in the same order.  To avoid this requirement I will use a format file to specify how the columns in the source file will map to the columns in the target table.

Below is the format file's contents, note that I am using an XML format file.


<xml version="1.0" >
<BCPFORMAT 
  xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
  <FIELD ID="LastName" xsi:type="CharTerm" TERMINATOR="," />
  <FIELD ID="FirstName" xsi:type="CharTerm" TERMINATOR="," />
  <FIELD ID="BirthDate" xsi:type="CharTerm" TERMINATOR="," />
  <FIELD ID="PhoneNumber" xsi:type="CharTerm" TERMINATOR="\r\n" />
  </RECORD>
  <ROW>
  <COLUMN SOURCE="FirstName" NAME="1" xsi:type="SQLVARYCHAR" />
  <COLUMN SOURCE="LastName" NAME="2" xsi:type="SQLVARYCHAR" />
  <COLUMN SOURCE="PhoneNumber" NAME="3" xsi:type="SQLVARYCHAR" />
  </ROW>
</BCPFORMAT>

The "RECORD" element corresponds to the source file.  Each "FIELD" element maps to a column in the CSV file.  The "ROW" element corresponds to the target table.  Each "COLUMN" element corresponds to a column in the target table.  Both FIELD and COLUMN elements are listed in the format file in the same order they appear in the source file and target table.


Each FIELD has two required attributes: "ID" and "type".  The ID attribute uniquely identifies each FIELD element and the type specifies the data type contained in the column.  The ID value is arbitrary; it does not need to match the column name specified in the source file.  In this case I have substituted "LastName" in the format file for "Surname" in the source file and so on.  I have also included the "TERMINATOR" attribute to specify that columns are delimited by commas, except for the final column in each row which is terminated by a Windows new line.


Each COLUMN has two required attributes: "SOURCE" and "NAME".  The SOURCE value matches the ID value of the FIELD that the SOURCE row corresponds to.  In this case the FirstName column appears first in the target table so it is listed first in the ROW element.  The NAME value is an arbitrary value to uniquely identify each COLUMN element.  The "type" attribute is optional and specifies the data type for the column in the target table.  All columns in the target table are of type VARCHAR so the format file type of SQLVARYCHAR is used.  Because the target table does not contain a column to store the date of birth there is no COLUMN that corresponds to the BirthDate FIELD.


Here is the T-SQL statement used to execute a bulk import using an XML format file.

BULK INSERT Contacts 
FROM 'C:\sampledata\source.csv' 
WITH  
(
  FORMATFILE = 'C:\sampledata\contacts_format_file.xml',
  FIRSTROW = 2
);

The FORMATFILE value is the path to the XML format file and the FIRSTROW value of 2 instructs the server to ignore the first record in the source file which contains our column headings.


Below is a screen shot of the contents of the "Contacts" table after the import.

The format file I used in this post was in XML format, you can also use the bcp (Bulk Copy Program) utility included with SQL Server to create a plain text format file.

References

Schema Syntax for XML Format Files from SQL Server Books Online:

http://msdn.microsoft.com/en-us/library/ms189327.aspx 

The BULK INSERT statement from SQL Server 2008 Books Online: 

http://msdn.microsoft.com/en-us/library/ms188365.aspx   

The sample data was created using Benjamin Keen's online data generator:

http://www.generatedata.com   

Download the sample CSV file:

http://www.wardlawclaims.com/attachments/blog/jeremy/using-an-xml-format-file-with-bulk-import-in-sql-server-2008/source.csv 

Download the XML format file:

http://www.wardlawclaims.com/attachments/blog/jeremy/using-an-xml-format-file-with-bulk-import-in-sql-server-2008/contacts_format_file.xml  

Report Server Pie Chart Label Positioning

One way to avoid pie chart label overlapping in Report Server is to not label sections of the chart that are under a certain percentage rating or a particular number amount.


To start name your chart by right clicking on the chart, click properties and enter a descriptive name. My chart is named CarriersChart.



In the Drop data fields portion of the chart, I have Total Carriers.



By right Clicking Total Carriers, going to properties, clicking on the Point Labels tab, I can put the following code in the Data Label as an expression. This code will call the GetLabel function in the Report Properties.

=Code.GetLabel(Sum(Fields!TotalCarriers.Value), Sum(Fields!TotalCarriers.Value,"CarriersChart"))





Next, click on Report, Report Properties, and then the Code tab. Use the following code to only label percentages above 5 percent.


Public Function GetLabel(ByVal CarriersChart As Double, ByVal totalcarriers As Double) As String
If CarriersChart / totalcarriers <>

Return ""

Else

Return Format(CarriersChart / totalcarriers, "P1")

End If

End Function

Note that the percentage under 5 percent is not labeled.

How to Import Outlook Contacts to Micrsoft Dynamics CRM

Importing your contacts from Outlook into Dynamics CRM is a relatively painless and straight-forward process.

1. Export your contact list from Outlook to a CSV file (File->Import and Export)



2. Select Export to a File




3. Select Comma Separated Values (Windows)




4. Select the Contacts folder




5. Open up the file in Excel and look for any strange data - for example I deleted the birthday column because all of the birthdays in my contact list were 0/00/00 which gave me an error.


6. In CRM go to Workplace->Imports


7. Select Import from Files in section 1 or New from the data grid toolbar


8. Click the Browse button to select the file you just created and hit next





9. If you want to review the delimiters you can or just hit next


10. Next, you have the option to map the data automatically or use an existing map


11. Select Contacts from the drop down and hit next


12. A warning icon appears next to fields that need your attention. You may select ignore, create new field or select an existing field from the drop down




13. Before you import you get a form that will allow you to set the owner of the contacts, prompt you for an optional data map name and ask you if you want to allow duplicates





14. Hitting submit will submit your job to run


15. Refreshing the grid will enable you to see the progress of the job


16. Once the Status Reason says complete you can see the number of successes, errors and the total count


17. If you had no errors, Navigate to Contacts, you may need to select the active contacts view and there are your imported contacts

A Quick Intro to Scrum

Quick Definition of Scrum

• Scrum is a way to break down complex problems into smaller, more manageable pieces.


• This is done with a self-organizing team that works for an allotted time (a sprint), usually two weeks to deliver a potentially shippable product.


• Contrary to what some believe, the sprint does not deliver partially complete work. The completed work is fully tested and ready to show to stakeholders with any documentation or artifacts.


• At the end of the sprint, a review (sprint retrospective) is held where the process is examined and any recommendations for process improvement are discussed. As the next sprint begins, another chunk of the product is worked on and the cycle repeats itself.

Real Life Scrum Example

My Grandfather was an auto mechanic and my Dad was an auto-mechanic which pretty much made me an auto mechanic growing up. In fact, from the time I could hold a wrench I was working on cars. I didn’t realize it at the time and it didn’t have a name, but we practiced Scrum back then.

Each morning when we arrived at the garage we would get a cup of coffee and all gather together to talk about what we did yesterday, what we were going to do today and any impediments we might have.

Dad: "Need anything on the engine rebuild for Mr. Brown?"
Me: "No, everything is by the book, so far."

Dad: "Alright, I’ll start the brake job while take care of the 2 oil changes."
Me: "I have to get gas in the parts truck, so I'll do that when I drop the rotors off."

Dad: "Sounds good. We can each take a tune-up, break for lunch, then double-team the water pump on the Ford this afternoon."

This was our way of self-organizing and while it wasn't the purest form of Scrum, as a Scrum master I make an effort to relate that experience with software development.


For more information visit http://www.scrumalliance.org/.

Introduction to SQL Server 2008 Data Import with BULK IMPORT

There are several methods for importing data from a text file, such as a comma or tab separated values file, into a SQL Server database. This post will provide a brief walk though demonstrating the basics of the BULK INSERT statement. The BULK INSERT statement can be used in scenarios where you need to import the contents of a text file into a table. For example, you might receive new records in batches rather than individually. In this scenario it makes sense to import the batch at once rather than executing an insert for each record in the batch. In this post I will import a list of contacts containing names (first and last) and phone numbers into a table named "Contacts". I will make text file used in the post available for download.

Here is the T-SQL used to create the Contacts table.

CREATE TABLE Contacts
(
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
PhoneNumber VARCHAR(25) NOT NULL
);

I have a CSV (comma seperated values) text file containing two handle sample contact entries that I wish to import. The first row contains the column headings and each subsequent row contains the three column values separated by commas. Below is a sample of the file's contents.

In order to import these records into my Contacts table I will use SQL's BULK INSERT statement as it appears below:

BULK INSERT Contacts
FROM 'C:\sampledata\source.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2
);

The destination table "Contacts" is specified first after the "BULK INSERT" keywords. The path to the CSV target file is specified next after the "FROM" keyword. The "WITH" clause allows us to customize how the data will be imported. In this case I have used the "FIELDTERMINATOR" option to indicated that a comma is the column separator for our file. The default is the tab character. I have used the "FIRSTROW" option to skip the first line of the CSV file since it contains column headings which do not need to be imported.

Below is a screen shot of the contents of the "Contacts" table after the import.

This is a very basic example. In this case the Contacts table and the CSV file contain exactly the same columns in exactly the same order. If there were differences between the layout of the table or CSV file additional options would need to be specified in the "WITH" clause.

References

The BULK INSERT statement from SQL Server 2008 Books Online:

http://msdn.microsoft.com/en-us/library/ms188365.aspx

The sample data was created using Benjamin Keen's online data generator:
http://www.generatedata.com

Download the sample CSV file:
http://www.wardlawclaims.com/attachments/blog/jeremy/Introduction-to-SQL-Server-2008-Data-Import-with-BULK-IMPORT/source.csv