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

Matching the header row on your spreadsheet files

From: Monday Productivity Pointers

Video: Matching the header row on your spreadsheet files

When you sign up for a new service, you want to get started right away. In fact I'm going to open it up with Excel right now by right clicking.

Matching the header row on your spreadsheet files

When you sign up for a new service, you want to get started right away. If it's a service that involves utilizing a list you already have, why reinvent the wheel and retype all your data? Especially if you're given the opportunity to import the data. Many times the data can be imported as a CSV file, that stands for Comma Separated Values, here's what the content of a Comma Separated Value file looks like, if you've never actually seen one. This is data that's been exported from another program.

At the top I can see my header data. All my column labels like first, last, company, street address, any typical data you'd see in a contact information. Underneath that is every record on a new line. Each record has different cell data, and it's all separated by, you guessed it, a comma. For example, first name, last name, street address. There are spaces, but everything the computer needs to know is that the comma is what designates the new piece of data for that record.

At the end of the line, we move on to the next record and it starts again. So when you export the data, You're not usually given a choice as to how to format that data itself, it spits out the data, you can open it up and look at it, and there it is. So when it's time to import into the new program or service, it may take some editing of this file on your end. This is what I'm going to show you how to do today. A CSV file can also be open with a spreadsheet editor, such as Microsoft Excel.

In fact I'm going to open it up with Excel right now by right clicking. Choosing open with and then selecting Microsoft Excel. Although normally you can just double click the file and it will open up in Excel anyway. When you look at the data in a spreadsheet here is where it becomes much easier to see. In fact you can see how you could very easily change this data if you wanted to. You can also sort it and move columns around and manipulate it and do other things. So here's where we can easily change this data to format it as to how the new program needs it.

Learning how to manipulate this data is a fantastic skill to know how to do to make yourself extremely resourceful when it comes to importing data. The first foundation piece of information you need to know is that a successful import all stems from the header rows. The header rows are the labels of your columns. And again, these labels tell the program where to put the data. Here's the first name, here's the last name column, here's the company, street address, and so on.

I'm going to close out of this. If I'm going to import data into another program Like this contact list, the first thing I want to do is look for the import menu item. After that, I need to decide how I'm going to import the data. For example, I've opened up Microsoft Outlook. I can browse all these menu items until I find the import option. Now in whatever program you're importing into, I can't tell you where that import item is going to be, but that's the first things that you need to look for.

In this case, it's under the file menu. So I'm going to select import and now I need to decide how I'm going to import it. It's asking me what do I want to import. In this case, I'm going to import contacts. I'll click the right arrow, and here's where it's going to ask me how I want to import it. So you need to look for the option to import from a tab or comma-delimited text file. Sometimes you'll see the word CSV, I'll select that. And here's where I can browse to my CSV file, I'll click import and here's where I'm given the ability to map these fields.

On the left is how Outlook needs to field names in that header row labeled. On the right, here's my header rows. Now, you can see they're not the same. In this case, I see the word First, Last, Street Address, City, and on this side, the way Outlook needs them is in a slightly different format. It's looking for First Name, Last Name, Work Street Address. So because those header rows are different, Outlook can't map them without my help, so it's up to me, to take this side and drag it in, and match them up.

Now your program may work a little different. I can see in the stop right that it's telling me the instructions as to how to map those fields, it's telling me to drag the field, where I want it to go. So in this case, I can keep matching them up and now it will import successfully. But I'm going to click Cancel right now because I don't actually want to match them up. I just needed to tell you how to do it. So here's why understanding this is a good skill, because some programs don't allow you to match the fields manually like this one did. It can let you choose a button to import, it can let you choose your CSV file But it's going to assume that your header rows match what it's looking for.

If it doesn't match, your data will never line up correctly or won't import correctly. So if you keep importing data and you're coming up with blanks, that's probably because the header fields don't match and your program can't figure out where the data should actually go. Here's a great trick: First, export data from that app, into CSP format. That way, you can look at the header files that the program exported, see what it's looking for, and adjust yours accordingly.

So I'm going to show you what I mean right now. I've got a successful contact list here in outlook. And I'm going to back to the file menu, except instead of import, I'm going to choose export. Where I choose what I want to export, I want to export my contacts to a list. Unfortunately, it's already going to export it to a tab de-limited text file. I don't have to choose that I want it in a CSV format. I choose where I want to save the exported contacts. I'll save them to my desktop. That's fine. It's going to export all the contacts. And once it's done, we're going to open that up and examine it and look at those header rows.

Here's my contacts list. We're going to right click, choose Open With and choose Microsoft excel. Here's the list that it exported. So what I would advise you to do, is look at this header row. Write down all these labels like first name, last name, company, all the columns that you know you absolutely want imported into your program, work street address, for example. Now I'm going to close this out. I can choose don't save because I'm done with it. I'm going to come back up to my contacts list, the one that I just can't get to import correctly, and I'll choose excel, and now, I'm going to take these header rows and change the text to match what it wants.

I'll just change one more. When I'm all done, I can save it. When you're saving it as a common separated value. You have to save it twice, because Excel's going to yell at you, saying that it doesn't like the format. It wants you to save it in Excel format, but in this case, click continue and don't worry about the fact that you have to save it twice. It's an Excel thing. So now let's do this one more time. I'm going to choose file, import, I'll again choose contacts I'm going to import from a comma-delimited file.

I'll choose my contact list, select import, and now look what's happened. Because I changed these to be what Outlook wants, it's now a ready map these correctly. So I don't have map them manually. So that's a neat little trick if you can't get your data to import correctly. Is to first export it, see what it's looking for, change your column headers to match, and then re-import it. This is how you can learn to do a successful import, every time.

Show transcript

This video is part of

Image for Monday Productivity Pointers
Monday Productivity Pointers

122 video lessons · 51064 viewers

Jess Stratton
Author

 
Expand all | Collapse all
  1. 3m 43s
    1. Using supplemental To Do apps
      3m 43s
  2. 10h 33m
    1. Welcome to the series
      1m 13s
    2. Running a group meeting with coworkers
      7m 19s
    3. Recording and marketing chat on air
      8m 30s
    4. Creating a quick presentation
      5m 37s
    5. Presenting from an iPad or computer
      3m 36s
    6. Migrating your accounts to Mint.com
      9m 55s
    7. Setting budgets and goals
      7m 4s
    8. Collaborating on team documents
      5m 53s
    9. Creating an online photo gallery
      4m 58s
    10. Kickstarter: Setting up your project
      10m 41s
    11. Driving traffic to your project
      5m 48s
    12. Conducting a voice call with a virtual team
      6m 40s
    13. Adding video and chat notifications
      4m 7s
    14. Accepting a payment with Square
      4m 35s
    15. Using the Square Wallet
      2m 32s
    16. Setting up shop on Etsy
      6m 31s
    17. Tracking your Etsy sales with Shop Stats
      4m 9s
    18. Raising your Klout score
      7m 3s
    19. Earning Klout perks
      4m 55s
    20. Skydrive: Collaborating on team documents
      4m 56s
    21. Skydrive: Accessing files on the go
      2m 57s
    22. Setting up Google alerts to track your data
      5m 5s
    23. Removing a page from the Google search index
      4m 42s
    24. Browsing privately in public
      4m 38s
    25. Cleaning up your session before logging out
      5m 8s
    26. Troubleshooting a remote computer with TeamViewer
      3m 42s
    27. Taking screenshots from a PC
      4m 12s
    28. Taking screenshots from a Mac
      3m 36s
    29. Setting up Find My iPhone
      3m 36s
    30. Using iCloud to find an iPhone
      3m 49s
    31. Sampling color from the screen
      5m 27s
    32. Using ColourLovers.com for inspiration
      3m 22s
    33. Get an audio clip onto YouTube using iPhoto
      5m 49s
    34. Creating playlists and customizing your YouTube channel
      5m 41s
    35. Record your screen using QuickTime
      3m 14s
    36. Record your screen using CamStudio
      2m 34s
    37. Using Tempo Smart Calendar when you are going to be late
      3m 9s
    38. Using Twist to let your customers know where you are
      3m 38s
    39. Using Wunderlist to track multiple projects
      9m 0s
    40. Use the Wunderlist browser extension to create tasks on the web
      5m 46s
    41. Using Smart Mailboxes with Mac Mail
      6m 52s
    42. Customizing the Mac Mail View
      7m 13s
    43. What's a firewall?
      7m 36s
    44. What is the Cloud?
      4m 42s
    45. Creating your own recipe with IFTTT
      7m 19s
    46. Browsing existing recipes with IFTTT
      5m 7s
    47. Installing the Feedly browser extension
      6m 34s
    48. Customizing Feedly
      6m 53s
    49. Understanding the basics of Twitter
      9m 9s
    50. Using Tweetdeck to handle multiple accounts
      9m 14s
    51. Working with URL Shorteners
      5m 45s
    52. Using bit.ly
      8m 31s
    53. Creating Quick Parts to re-use text
      6m 19s
    54. Moving your Autotext to a new computer
      6m 7s
    55. Shutting off access to social networks
      6m 18s
    56. Hiding taskbars
      2m 36s
    57. Exploring the iOS 7 Update
      10m 7s
    58. Running a productive online meeting
      3m 44s
    59. Getting meeting minutes faster
      6m 47s
    60. TextExpander for Mac
      7m 6s
    61. Breevy for Windows
      3m 44s
    62. Using Smart Folders on a Mac
      5m 52s
    63. Using Windows Libraries
      4m 25s
    64. Finding large attachments in your email apps
      5m 13s
    65. Use Ninite to install all your PC apps at once
      3m 30s
    66. Use Get Mac Apps to install your Mac apps at once
      2m 56s
    67. Creating a disposable email address with Guerrilla mail
      4m 7s
    68. Creating an email address that lasts only 10 minutes
      3m 16s
    69. Finding and adding local vendors to enhance your iOS reminders
      3m 45s
    70. Adding geofencing to Find My Friends
      3m 20s
    71. Turning a Word document contract into a PDF
      4m 1s
    72. Turning a PowerPoint presentation into a PDF
      4m 10s
    73. Resetting browser site passwords
      7m 11s
    74. Disabling toolbars, resizing screens, and accidentally closed tabs
      7m 42s
    75. Identifying your wifi's weakest link
      7m 59s
    76. Setting up dual band speed on your router
      7m 36s
    77. Add your social media activity to your website
      8m 54s
    78. Using WordPress mobile to update on the go
      4m 48s
    79. Matching the header row on your spreadsheet files
      8m 20s
    80. Using a formula to merge first and last name columns
      5m 58s
    81. Using JoliDrive to browse cloud app data
      5m 11s
    82. Using JoliDrive on an iPad
      4m 31s
    83. Finding deals on eBay using misspelled listings
      4m 18s
    84. Searching for promotional and coupon codes online
      5m 52s
    85. Sending real postcards from your computer with Postagram
      4m 25s
    86. Using Postagram to send a real postcard from your smartphone
      3m 55s
    87. Getting to Inbox Zero
      11m 4s
    88. Using existing GMail labels with Mailbox
      3m 19s
    89. Adding 2-step authentication
      3m 39s
    90. Enabling in-app PIN codes
      3m 31s
    91. Accessing your digital movies
      5m 20s
    92. Copying movies onto a device
      3m 25s
    93. Using Genius Scan to scan your documents
      3m 34s
    94. Sending your scans
      2m 41s
    95. Using Acrobat to ink sign a PDF
      4m 49s
    96. Writing a letter of recommendation
      7m 49s
    97. Constructing a successful press release
      4m 48s
    98. Troubleshooting wireless security
      4m 48s
    99. Writing a claim letter
      5m 22s
    100. The best reasons to try online chat customer service
      5m 9s
    101. How to do a firmware update
      6m 34s
    102. Siri, your iPhone assistant
      4m 48s
    103. Writing an email that gets read
      4m 51s
    104. Writing an email that requires action
      2m 54s
    105. Your Blu-ray questions answered
      3m 50s
    106. Using LittleBit to photograph your goal progress
      3m 9s
    107. Exporting WordPress blog entries
      3m 28s
    108. Understanding how Office 365 works
      5m 9s
    109. Using Waze for crowdsourced GPS
      2m 58s
    110. Downloading your Facebook timeline
      3m 10s
    111. Scheduling email with Boomerang
      4m 24s
    112. Google Labs for Calendar
      2m 58s
    113. Finding missing songs in iTunes on your iPhone
      2m 10s
    114. Requesting your Twitter archive
      2m 59s
    115. Using Doodle for easy group scheduling
      4m 59s
    116. Easily remote to another computer with Join.me
      3m 47s
    117. Keyboard shortcuts for YouTube
      2m 58s
    118. Easily annotate images with Skitch
      6m 4s
    119. Blurring photos for posting on social networks
      6m 41s
    120. Migrating to Google Apps
      9m 31s
    121. Get your Google Calendar schedule by email every morning
      3m 3s

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.

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 Monday Productivity Pointers.

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 preferencesfrom 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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

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.