What Happens When Excel Decides to Prettify Your Data For You When You Import it to Ruby on Rails (and More Reasons to Use .CSV Files and Not .XLSX Files)

You've fetched your data from an Excel sheet (.xlsx) using a rake task. You've added models, views, and controllers in Ruby on Rails to ensure that you can get and show your data in your React frontend. 

But uh-oh.... your image links aren't working!

Awkward Schitts Creek GIF By CBC


You first take a look at your excel file and the links look just like they should. 


Let's inspect the imported data through rails console:

    
    1 rails c

    2.7.4 :001 > Product.first.image1         Product Load (2.6ms) SELECT "products".* FROM "products" ORDER BY "products".
            "id" ASC LIMIT $1 [["LIMIT", 1]]          => "<html><u>https://i.etsystatic.com/23287327/r/il/2bde7a/3700353637/il_full
                xfull.3700353637_msew.jpg</u></html>"


Taking a closer look at your image links you discover something odd.

What happened here? Why are your links showing up with these strange <html><u> and </u></html> tags appended at the beginning and end? 

Unfortunately you are in a hurry to show your boss some deliverables today, they need your code so they can continue to move this project forward. You're definitely going to have to drop this messy table and re-seed your data. But that can wait until later. In the meantime you decide to make a quick fix to get the images to render so your team can keep working. Then when you have a working product to deliver (today is the due date after all!) you can swim back upstream and fix the issue at the source. 

One quick fix would be to call a JavaScript string method on this image link. 

<img src={image1.slice(9,-11)} alt="mala bead necklace"/>


Now back to the heart of the issue. Why did excel screw us over by adding those <html><u> tags? What is a <u> tag anyway? We refresh our memory with a quick google search (as all good developers do). 

Google It Jimmy Fallon GIF by The Tonight Show Starring Jimmy Fallon

Google reminds us that <u></u> tags are used to underline in HTML. 

What are we peasants using old school HTML now? Well, we're not! But Microsoft Excel is! 

Yes that's right. Under the hood, Excel is adding some <html><u> formatting for us to "pretty print" our links and make them display with a lovely underline for us. 

Excel:   "Oh, I see this cell contains a url. I will make it          look nice for you!" ðŸ¤“

 Excel then proceeds to add oldschool html markup... 

Us:      "My those underlines look distinguished and so                 presentable in this document, thanks Excel!" ðŸ˜ƒ 

Later on...  

Us:      "I need to export this data to use in my SQL                 database. I'm going to use the Roo gem to help me             with that."

Roo gem: "I see you have information in this cell, I'm not             touching that and will import it into SQL exactly             as you have formatted it." 

Us:      "Why is there excess markup baggage on my data?!" ðŸ˜–


The morale of the story... 

Excel is NOT a data medium. It is an analyst tool. Always import a .csv file rather than .xls and you will make your life as a backend developer much easier. 

 

        


    


Unfortunately that inline styling really mangled the string we imported. And Roo, 



Comments

Post a Comment

Popular posts from this blog

Using GitHub on a Pair Programming Project

Image
When my journey to learn software development began I envisioned sitting alone in my mom's basement in the dark. The truth is, I had no idea how much teamwork and pair programming would be involved in my new career. As someone who loves collaborating I love working with teams. I am a  firm believer that two heads are  always  better than one.  Now here comes the "but"....  Work Hug GIF By Kochstrasse™ I knew very little about collaborating on Github as I embarked on my first pair programming project. Here's what I wish I knew: Getting Started  Create your repo a.k.a. repository in Github       1 > Settings      2 > Collaborators      3 > Add People           Get the Github desktop app     You might be thinking "oh jeez, I don't want to install yet another app" but I promise it will make your life easier.  Add your repository to Github desktop Open yo...
Read more

Importing Excel Data into a Ruby on Rails Application

Image
So you've got some Excel data you would like to import into your Ruby on Rails project?  As a software engineer you may not be responsible for data integrity and quality control. You're given a spreadsheet and your job is to import it and render the information into a web application. But what if you are given bad data? A little bit of basic quality control can go a long way in saving you from future bugs in your application. You can save yourself some time and trouble just by taking a quick peak at your data before importing it.  Paw Patrol Puppy GIF By WesternCPE Ok, now that you have ensured your data is valid and well-structured let's get it into our database! First, set up your application and your MVC (models, views, controllers) in your CLI (command line interface).     1 rails new my_app     2 rails generate resource Product product_title description price:integer (insert          the...
Read more

The Journey Begins: Navigating a 15-week Software Engineering Bootcamp

Image
Welcome! I'm glad you are here. I'm Morgan Hobbs and I am a Fullstack Software Engineering student at Flatiron School . My journey as a student in a 15-week full-time bootcamp has begun in the middle of reading Andy Weir's  first novel   The Martian . As such, it seems the only appropriate and logical way for me to share my journey with you would be to use space travel analogies.   I will be your intergalactic guide as we journey to Mars (and learn some coding along the way).   You might think the biggest challenge to overcome when learning to code is learning how to problem solve. You may be right.  “At some point, everything's gonna go south on you... everything's going to go south and you're going to say, this is it. This is how I end. Now you can either accept that, or you can get to work. That's all it is. You just begin. You do the math. You solve one problem... and you solve the next one... and then the next. And If you solve enou...
Read more