// Google Tag Mamager // Google Tag Mamager Skip to main content

Having trouble importing data into Excel? Ever have a list that you want to convert into a spreadsheet but don’t want to go through the effort of manually retyping the data? Don’t worry, ChatGPT has you covered. If your data is poorly formatted and won’t import into Excel, ChatGPT can help you quickly and easily convert it into a structured table.

ChatGPT will then generate a well-organized table that you can copy and paste into Excel. In no time, you’ll have the data you need in the format you need it. So don’t let formatting issues keep you from getting the data you need – ChatGPT has got you covered!

If you’re looking to import data into excel without using Excel’s Get Data from File, keep on reading.

What is “Get Data From File”

Microsoft Excel has an amazing feature that will let you import data from many different file types like Text files, PDFs, Websites (HTML), XML, and JSON among many others. We won’t go into depth about this feature in this article. But in many cases you can use Excel’s “Get Data” features to easily convert many types of data into structured data or spreadsheet. You can read more about how to do that here.

Importing Messy Lists and Unstructured Data into Excel with ChatGPT

Sometimes data files are so messy that the Get Data From File option doesn’t work. In these cases, you’ll need to import data into without Excel import. Thankfully there is in option.

If you’ve tried to import the data into excel and its not working, its probably because the data is so poorly structured that Excel can’t easily convert it into a usable format on its own. The following PDF, taken from Grambling State University, represents a poorly formatted list that makes manipulating the data difficult.

A screenshot of a faculty directory in pdf form

This document is okay if you want to just look up faculty contact information. But lets say you want to organize this list by department or building. Well this list wouldn’t be very helpful. If you try and copy and paste the data into Excel, you might get an output like this:

A screenshot of an Excel document with data pasted in a long string

Importing this data into Excel also doesn’t work:

A screenshot of microsoft excel's get data from file feature

Thankfully, ChatGPT can step in where Microsoft Excel falls short. To take advantage of this, you’ll first need to convert the pdf into an excel format. I recommend Adobe PDF to Excel. Based on that, you’ll get something that looks like this:

A screenshot of a document formatted with adobe's pdf-to-excel service

This is a bit better but still not exactly what we are looking for. Thankfully, CHATGPT can take things from here. The way to do it is to highlight the data you want to convert from the Excel, copy it, and ask ChatGPT

“Convert the following Data into a table of structured data:”
And paste the data from the Excel into the chat

If you want to get more specific, you can ask ChatGPT

“Convert the following data into a table of structured data including name, email, phone, fax, gsu box, building, room, title, department:”
And paste the data from the Excel into the chat

ChatGPT will then produce a beautifully organized table that should look like this:

a screenshot of a table formatted using ChatGPT

You can then highlight this table, select copy, and paste into Excel.

Voila, you’re done.

I hope you found this article helpful, if so, please feel free to subscribe to our newsletter at the bottom of the page with your email address. If you have any ChatGPT Tricks you love and want to share, you can add them in the comments below.

Leave a Reply