Share this content
0
12136

Importing CSV Raw Data into an Existing Template

How Do I Import CSV File into Existing Template?

Hi Everyone,

I deal with this frustration all the time at work with downloading data from a website, which creates CSV raw data file that has to be saved as an excel file.  When you save it in excel the data is a mess.    It takes hours to reformat the data so it can be imported into the Department Reconciliation Worksheet to perform the reconciliation.  

My question:  Is there an efficient way to download and manipulate the raw data from the website into the format required by Department Reconciliation Spreadsheet?

Any help or ideas would be greatly appreciated.

Kind Regards,

Stacey

 

 

 

 

Replies

Please login or register to join the discussion.

Dec 28th 2015 15:28

Stacey,

It sounds like you're using the File, Open command in Excel to open your CSV file. Doing so can indeed lead to tons of repetitive work.

The alternative is to go to a blank worksheet and in cell A1 choose Data, and then From Text. This will launch an Open dialog box from which you'll choose your CSV file, and then you'll be walked through the Text Import Wizard. Choose Delimited on the first screen, and then Comma on the second screen. On the third screen you can mark any columns that have leading zeros as text, and you can mark any columns don't need as Skip. Once you've made your selections, click Finish. Going forward, when you open your spreadsheet you'll be prompted to choose a CSV file. You won't have to go through the wizard, the data will simply be replaced. Click Cancel on the prompt if you want to keep the current data.

If part of your manipulation involves removing rows, this won't help you, as you can only show or hide columns. Microsoft Query is the more sophisticated approach that can give you total control over your CSV file, but the From Text command may do what you need.

Let me know if this gets you closer to your end goal.

David

Thanks (1)
avatar
Mar 28th 2016 20:12

Excel is not well suited to handling CSVs. In fact, it's dismal. Access is a much better tool.

That said, how are you importing the data into Excel? Instead of opening the CSV in Excel, you could try opening a blank spreadsheet in Excel first and using the data import feature to better control the formatting.

Thanks (0)
Share this content