How to strip double quotes while importing data from CSV or TSV using SSMS Import Data wizard OR SSIS?

Standard

Long Title! Let me explain. This post will help you solve following problem if you run into it:

1) You are using SSMS Import data wizard to load data from a comma (or tab) separated value (CSV/TSV) file into SQL Server Table & you find that your source data values has double quotes and so you want to strip them before loading to destination table.

2) You are using SSIS to load data from a CSV/TSV file into SQL Server Table & you want to strip the double quotes in source table fields before you load the data to destination table.

Double Quotes CSV file SSMS SSIS LoadSolution:

1. After you’ve configured the Flat File connection. you’ll reach to a point where you’ll see “Flat File Connection Manager” in SSIS. Or in the SQL Server Import & Export data wizard, you’ll see a dialog box to configure flat file connections.

2. In the Text Qualifier, enter

Strip Double Quotes SSMS SSIS Import Wizard3. Make sure to Preview the data to verify that the double quotes around data fields have been trimmed.

4. That’s it! You’ve successfully configured the flat file connection manager to strip double quotes.

Advertisements

PowerPivot Model: Why am I not seeing “Month Names” in correct logical order?

Standard

This blog post is for people who have seen the reports built on PowerPivot model where the Month Names are not in correct logical order. So instead of  “January, February, March, April …” (which is correct logical order), the order in the report would be displayed as “April, February, January, March..” (which is NOT correct).

This is what I am talking about:

powerpivot model month name not sorted correctly

Here, Month names are not sorted correctly, right? So how do we solve this? Let’s see this in this blog post!

Understanding the Sample Data-set

Optional: Download a sample data-set to practice what’s described in this blog-post: Download – Paras Doshi Blog’s sample data set

Now the data looks like this:

Date Daily New number Month Month Name
1/1/2012 0:00 0 1 January
1/2/2012 0:00 0 1 January
1/3/2012 0:00 0 1 January
1/4/2012 0:00 0 1 January
1/5/2012 0:00 0 1 January
1/6/2012 0:00 0 1 January
1/7/2012 0:00 0 1 January
….. …… …… ……

Note that “Daily new number” is used for the demo purpose. I had to anonymize the data before I could share it with you all!

Originally, the table had just two columns but since I wanted to add “time Intelligence” to the model. I added two columns Month and Month Name.

Now here’s the DAX behind these two columns:

Column DAX
Month MONTH(‘Sample Table'[Date])
Month Name RELATED(Month[MonthName])

There’s a relationship between Month Column of “Sample Table” and Month column of “Month” table

And here’s what Month Table looks like:

Month MonthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Creating a Report on Top of Sample Data-set.

Now if you create the report of Month Name vs Daily New Number, it will look like:

powerpivot model month name not sorted correctly

Oops, Problem detected: Month name are not in correct order.

So now let’s solve it.

Solution to sort month name correctly

Let’s solve this issue in our PowerPivot Model. Here’s what you do:

1. Go to PowerPivot model, Select “Month Name” column from the sample table

2 Toolbar > Home > Sort By column > click on “Sort by column”

3. And set the properties in the dialog box as:

powerpivot model sort by column dialog box

Now, go back to the report and refresh the connection to the model.

Done!

powerpivot model month name are sorted correctly

This is so because by default since the month name is “Text” – it was sorted in A to Z format. But you saw how we can use the sort by column property in PowerPivot model to fix this issue.

That’s about it for the post! your feedback is welcome!

Want to Read More? Here are few links:

Do tables in a SQL Azure Database need to have a primary key?

Standard

Answer: No.

Though SQL Azure does need that a table has a clustered index. So to this end, Let’s write some TSQL code and cement this concept in our brain. So as a part of the test, we would create a table with no primary key – However we would certainly create a clustered index on one of the column. So let’s get started:

OK, so here’s the TSQL code to create a table named ‘InternationalStudentList’:

create table InternationalStudentList (StudentName varchar(30),HomeCountry varchar(30), DegreeProgram varchar(30))
go

Now let’s insert some data into this table. Here’s the query:

insert into internationalstudentlist values('Paras Doshi','India','Masters in IT and Management')
go

If you run the above query – you would get the error:

Msg 40054, Level 16, State 1, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

So we know that a table in SQL Azure requires a clustered index, right? So, Let’s create one!
Let’s assume that the InternationalStudentList would be queried a LOT to answer the question: “List all StudentName whose HomeCountry is X” – So based on this information let’s create a clustered index on HomeCountry Column. Here’s the TSQL code:

create clustered index cix_internationalstudentlist_homecountry ON
InternationalStudentList(HomeCountry)
go

Once a clustered index is created, Try inserting data again. And you would see that a row would get successfully Inserted!
Run a Select Command to verify that:

SQL Azure Result of a SQL select command

And as you can see, we were able to insert a row in a table. Remember that this table did not have a primary key But we did create a clustered index. The Goal of the Post is achieved here, But just want to show you the Query Plan for a Query that looks like:

select StudentName,HomeCountry,DegreeProgram from InternationalStudentList
where HomeCountry = 'India'
go

sql azure clustered index seek

Note that I have run this queries on Management portal for SQL Azure, You can run it in SSMS too. But the goal is to show you that we have clustered index seek and that’s one of the way to tune queries. Not going into details in this post – And That’s about it for this post. Your feedback is welcome.

And Let’s connect! Here are few people networks that I am active on:

paras doshi blog on facebookparas doshi twitterparas doshi google plusparas doshi linkedin

I Look forward to Interacting with you!