Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Exporting data in tabular form

From: Drupal 6: Online Presentation of Data

Video: Exporting data in tabular form

As you have seen from other videos in this series you can create some remarkably sophisticated displays of information and in importing and manipulating data in Drupal, we saw how to get data into Drupal. In this video, we're going to go in the other direction, to show you how you can use views to get information out of Drupal in a simple and highly portable format. That is, as a comma- or a tab-delimited text file. If you have been watching this series from the beginning you actually have all the skills you need to figure out how to do this. We're just going to set up a view with the information we want to export. But in the process of getting there, we'll come across a couple of difficult points that are typical of the kind that you discover when you take Drupal into new and unexpected areas.

Exporting data in tabular form

As you have seen from other videos in this series you can create some remarkably sophisticated displays of information and in importing and manipulating data in Drupal, we saw how to get data into Drupal. In this video, we're going to go in the other direction, to show you how you can use views to get information out of Drupal in a simple and highly portable format. That is, as a comma- or a tab-delimited text file. If you have been watching this series from the beginning you actually have all the skills you need to figure out how to do this. We're just going to set up a view with the information we want to export. But in the process of getting there, we'll come across a couple of difficult points that are typical of the kind that you discover when you take Drupal into new and unexpected areas.

The good news is that Drupal is flexible and powerful enough to overcome these problems. The first thing we'll do as usual is to setup a view. To do so go to Administer, Site Building and Views click on Add. It will be a Node-based view and the name we'll give it tabular_ export, Tabular export and scroll to the bottom. Once there click Next. As usual we're going to add some filters. I want to get an exportable file of all of the people in this site, so I'll make it Published = Yes and Type = Person. We sort by Node Group, scroll down, Published and Type, Add. Yes, Published, Type is Person, and Update.

In this case, I want an export of both the personal name and the family name. You might remember that we can agglomerate them in the title but we do have them as two separate fields. I'll go back up here and add just those two fields, by clicking on Add next to Fields, scroll down, scroll down to the Content group and a Family name and Personal name, and Add. Since all we want is the information, we don't want any labels attached and we're not going to mess around with any of this Link the field to its node, what we're trying for is plain text export. Scroll down a little further and we actually have a choice under this Format pop-up for Plain text, and Update. We do the same here for the Personal name, No label and Plain text and Update.

So far so good. As you know we can put these inline by going up to our field Row Style and clicking on the Options. Then we just click in these boxes to put them inline and click Update. Now let's take another look. Aha! We have this in the wrong order, simple enough to rearrange. We know how to do all of these steps, click Update. We're doing pretty well here now. The only problem is we don't actually have them delimited in anyway. You might have noticed in the Row Style Options we can add a delimiter. So let's do that, add a comma and Update.

From here we can go in two directions to create industry standard text exports that programs like Microsoft Excel will understand. We could make a comma-delimited file as I've done here. Normally comma delimited files have quote marks around them. That's known as a CSV file or Comma Separated Values. We could also separate the fields with tabs. Let's start by making this comma-delimited file a little more standard by putting quotes around it. To do so, we go back up to the Fields themselves, we'll start with the Personal name. Let's think about what we actually want to do, we want to put quotes around the Tom and then have that comma and then quotes around the de Nada.

We do have a space before the comma but that actually doesn't matter in a comma separated values file, because all that matters is what's in those quotes. We can do that by using these Rewrite the output of this field, so we click it and then scroll down a little. As you've seen in other places in Drupal, this Field Rewrite allows you to specify replacement patterns, and it works sort of strangely. This is the first field that we have in the list. If we scroll down and take another look, we see we can only replace it with the value from that first field, the Personal name value. Let's do that. We can copy it. I did that by pressing Command+C on the Mac on the PC you would use Ctrl+C. Now that I've copied it, I press Command+V or Ctrl+V on the PC to put it in my Rewrite the output of this field area, and then scroll down and click Update.

Now you will see that it actually doesn't look any different from before, we have just explicitly replaced this. Now we can have some fun by making that Rewrite a little more fancy. Go back up, click on the Personal field again, and put in quotes which remember is what we wanted to do in the first place. Scroll down again and click Update. And indeed it works. We now see that the first name is in quotes. Now if we want to put the second name in quotes, we could go back up and do the same exact thing. We would click on the Family field. Rewrite the output of this field and paste in the replacement pattern here. One interesting thing you will notice though as I scroll down is this second field includes replacement patterns both for the first one and the second one.

This is something a little unusual in Views. The further down that the field is, that is as you go down in this Fields area up here, the more replacement patterns it contains. It actually contains replacement patterns for all of the previous fields. That's quite convenient for us because then we can put everything into the last field and we only have to mess with it once, and in fact, that's what we're going to do. I am going to go back to this Personal field here and just get rid of this Rewrite the output of this field, scroll down and click Update, and we see we are back the way that we were with that comma. Go up a little bit and then we are only going to change again that second field. Rewrite the output of that field and in Text I'm going to again paste the personal name value, scroll down a little bit and see okay, it's field_ family_name_value, copy and paste that just as we did before.

Then I can play with this as much as I want. I'll put quotes around each field, a comma between them and then scroll down and click Update as usual. As we scrolled up, we see it's giving us a little bit more than we really want. This section here is exactly what we want. It has the quote, first name, end quote comma and then family name, but we also have this first name showing and the comma. That's because, remember, we didn't hide any previously displayed information, we just added to it.

We can now go back and hide that information. To do so scroll up and go to our Personal field and there is actually an option in Views Exclude from display. Scroll down again and Update. There, now it actually looks the way that we want. To be completely clean about this we should go back up to our Field option widget here and get rid of this separator. I know it's not showing but if we started to add fields later, we would start seeing that separator again and it might be hard for us to remember exactly what we've done. I'll also uncheck these Inline fields. Again, in case we start adding and taking out fields, click Update. And there it is. We have our Fields now in comma separated value format which any database program or Excel or any other spreadsheet program can understand just fine.

Now let's try to format the output as tab separated values. As it turns out that's a lot harder and this time it's not Drupal's fault. It's actually within the HTML standard. You see in HTML you can indicate any ASCII character that's visible with a little bit of code. But a tab character isn't actually visible and you can't indicate it with ASCII. If you search for answers to this problem online, they all say the same thing. They say you shouldn't be using a tab in HTML. You should be using tables instead. But in our case, we really do want to use a tab character because we're outputting through HTML in order to get our separated values the way that we want. So what exactly do we do? As it happens, there is two ways to solve this problem. They are both a little hackish but they both work. The simpler one and it's a little less elegant is to go back up to our Rewrite the output of this field, and instead of having quotes and so forth which aren't needed for a tab separated format, remove the quotes and then instead of a comma there, I'm just going to put a marker, anything that wouldn't normally show up in the fields themselves.

Let's say three commas and then scroll down, and click Update. Now we have it separated not by tabs, but by these three commas, we could then select all of them. Copy it. Go to a Text Editor. I happen to have one open right now called TextWrangler. Go to it by hitting Command+Tab on the Mac and paste it and then using the Find & Replace feature in your Text Editor, you could go back and say find triple comma or replace it with a tab and replace all. And there we are. We now have tab characters in the middle here. In Text Editor we can do that by Showing Invisibles. And we see that little triangle means that it is actually a tab character.

The second way is sort of to go backwards. We'll start with a tab character here in our Text Editor, we select it and Copy it and then go back to Drupal and rewrite the output of that field, and there we're going to paste our tab character in. Again, it's a real hack and anybody who knows HTML and loves the standard is just tearing their hair right now with what a hack it is. But it does work. Click on our Field there, go back to Rewrite the output of this field and then paste our Tab character, there is one other thing that we've do however. We have to make sure that HTML outputs this exactly as it's showing, because otherwise the way that HTML works, it will turn that tab just into a plain space.

To do that I'm going to add a little HTML code here at the beginning

 and
						
							then at the end 
. Now we scroll down, change the Format to Default and Update it. Now we have our tab there, and we can prove that by copying it and pasting it into our Text Editor, and you see that little triangle which means that it is actually a Tab character. There are one or two more things I want to point out just very quickly before we go on. The first is that when you do a text output of this sort, almost always you want to have the entire file in one go, you don't want to show just ten items at a time. So when you do something like this remember to go up and change the Items to display so that it shows all of them. This is a common mistake, people then will go through all of this trouble, and say why don't I see my entire file? It's because you have to change that and click Update.

Now of course, we see all of the items that are in our entire database. Next thing I wanted to mention is what if you wanted to include not only the person's name but also their address, and remember we did that through households, well then you have to work in relationships, we are not going to go through that process now, so you may just want to review the relationship video. If you got frustrated with that, the trick is to make sure you are going at it from the right side. In this case, we are doing something with the Node Type = Person and then we would draw a relationship to households, as it happens that won't work because the way that we setup households we would have to start with the household and then make a relationship to person.

I know it's an advanced topic and we're not going to go into it now. But I did want to warn you about that again, because it's one of the big points of frustrations as figuring out which way relationships go, and that comes into play a lot when people are trying to just output all of the data from their database. The last thing that we're going to do is we are not going to be using this text output throughout the rest of the course, so I'm just going to delete it. It's easy enough to set up again if you want to do so. I hope this video showed you some of the many data output options you have in views itself and you don't have to add any other modules or write custom code to make it work.

Once you do start adding other modules though possibilities for data presentation just multiply, but none of those modules will let you actually reach in and change the display fields themselves quite as well as Views does. It really is the foundation for all data presentation solutions.

Show transcript

This video is part of

Image for Drupal 6: Online Presentation of Data
Drupal 6: Online Presentation of Data

50 video lessons · 11400 viewers

Tom Geller
Author

 
Expand all | Collapse all
  1. 12m 56s
    1. Welcome
      1m 12s
    2. Reviewing requirements
      3m 35s
    3. Using the exercise files
      3m 11s
    4. Touring examples of data visualization
      4m 58s
  2. 27m 56s
    1. Planning data structure
      8m 26s
    2. Importing and manipulating data
      6m 40s
    3. Looking at Drupal's database
      6m 13s
    4. Deciding whether to store personal data as nodes or users
      6m 37s
  3. 1h 13m
    1. Understanding the Content Construction Kit (CCK)
      4m 57s
    2. Creating new content types with CCK
      7m 26s
    3. Hiding the Body field
      2m 46s
    4. Reflecting CCK field data in the Title field
      7m 28s
    5. Managing CCK field placement
      7m 34s
    6. Exploring CCK's other features
      8m 22s
    7. Using other CCK field types
      3m 25s
    8. Adding date information as a CCK field
      8m 43s
    9. Including images as CCK fields
      10m 23s
    10. Connecting content to existing nodes
      5m 58s
    11. Using taxonomies to categorize and group data
      5m 59s
  4. 53m 54s
    1. Understanding why views are useful
      6m 12s
    2. Using SimpleViews to create basic content views
      5m 49s
    3. Diving into the Views interface
      11m 16s
    4. Adding fields to a view
      7m 12s
    5. Understanding iconic controls in the Views interface
      7m 15s
    6. Surveying the Sort, Filter, and Field options in Views
      5m 40s
    7. Adding view displays as pages, blocks, and RSS feeds
      10m 30s
  5. 43m 34s
    1. Overriding default settings on view displays
      8m 56s
    2. Attaching more information to views
      10m 57s
    3. Improving view appearances with grid, list, and table formatting
      9m 20s
    4. Surveying other basic display settings in Views
      11m 3s
    5. Altering a view's appearance through CSS
      3m 18s
  6. 55m 8s
    1. Importing, exporting, and cloning views
      6m 9s
    2. Controlling access to views
      7m 19s
    3. Learning from built-in views
      5m 52s
    4. Creating views that aren't based on nodes
      10m 6s
    5. Extending views with arguments
      10m 17s
    6. Extending views with relationships
      7m 2s
    7. Going further with relationships
      8m 23s
  7. 46m 40s
    1. Understanding geographic data
      4m 26s
    2. Setting up the Location module
      16m 20s
    3. Entering geographic data with the Location module
      10m 10s
    4. Displaying basic maps with the GMap module
      6m 43s
    5. Integrating the GMap module with Views
      9m 1s
  8. 54m 21s
    1. Exporting data in tabular form
      11m 25s
    2. Planning with the calendar modules
      11m 31s
    3. Using the Charts module and Google Charts
      7m 11s
    4. Graphing data with Open Flash Chart
      4m 50s
    5. Making important data pop out with tag clouds
      7m 46s
    6. Putting it all together in an attractive package
      11m 38s
  9. 36s
    1. Conclusion
      36s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Drupal 6: Online Presentation of Data.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.