Formatting imported .CSV file quickly

Question

As part of my job, every day I import data from an online database into an Excel spreadsheet. The data is downloaded as a .CSV file then opens in Excel. The data is always sloppy. The font is wrong and too small, the column widths are too narrow, the dollar values are just numbers with no formatting, and more. I literally spend a good 20 minutes or more just to reformat the information so that it is legible and presentable. Is there an easier way to do this?

Answer 1

The best way to handle this dilemma is to use a Macro to record how you want the spreadsheet to ultimately look. You can then execute the Macro every time you import the data and it will make all the adjustments for you instantly.

Creating the Macro:

·        Import the data as a .CSV file normally.

·        Make a copy of the sheet so you have two identical sheets in the same workbook.

·        Save the workbook as a .XLSX file

·        Turn on your Macro recorder by selecting ‘Developer’ on the ribbon

·        On the left click the command called ‘Record Macro’

·        In the dialog box, name your Macro and give it a shortcut key (I suggest using Ctrl+m)

·        Keep the Macro stored in the workbook and type an optional description

·        When you click OK the recording will begin which means the Macro will record everything you do from that point forward

·        Click OK and start formatting the spreadsheet the way you want

o  Take your time, there’s no rush

o  Stay on the spreadsheet the whole time, do not do anything else on the computer

o  Reformat everything the way you want to see it in final form

o  You can even format print parameters

·        When finished formatting, on the ‘Developer’ tab the ‘Record Macro’ command is now ‘Stop Recording’, click it to stop recording

·        Your Macro is set.

To test your Macro:

Go to the sheet that you copied. 

On the keyboard press Ctrl+m and your Macro will format the sheet instantly.

Now save this file in a designated folder and be sure to include a date in the file name so you know when it was created. (Note, this will now be a Macro-enabled file with an extension of .XLSM)

·        Tomorrow, before you download the next .CSV file, make a copy of this file and change the date (in the name) to the current date.

·        Open the file and create a blank sheet.

·        Copy the .CSV file data to the blank sheet and execute the Macro (Ctrl+m).

·        The sheet is instantly formatted.

·        You can delete the other sheets from the previous day, if you want.

Repeat daily.