How can I import my .csv and still keep my Excel Formatting?
February 13, 2018 5:40 AM   Subscribe

I have a formatted Excel Workbook (column widths, headers, colors, etc) that I need to populate with data from a .csv. If I open the workbook and csv, copy and paste, all is good...however I want to automate this a bit. Doing an import brings in the data, but all the formatting disappears! I appreciate any thoughts! KIT
posted by keep it tight to Technology (4 answers total) 1 user marked this as a favorite
I mean what I'd do is lazy and probably not best practices:

I'd make the carefully laid out workbook my columns just so etc, then make all the sort-able data vlookups or index to another sheet were I would dump that .csv every time I need to update it and bingo presentable front page with ugly .csv hidden on sheet 2
posted by French Fry at 5:55 AM on February 13, 2018 [9 favorites]

CSV by nature does not store formatting, so I think that something like French Fry's solution is the only way, and it can be "best practices". Separate data from presentation.

Either that, or you could add some extra rows to your CSV with encoded formatting information, and have some VBA import script that applies the formatting to your data, but that's even less clean.
posted by jozxyqk at 6:31 AM on February 13, 2018

I just ran a couple of tests. If I have an Excel workbook, format a few columns (bold / underline / colors / etc), and Import External Data from Text (selecting a CSV file), my formatting is retained (except column widths, which autofit)... This is with Excel 2010.
posted by Jacob G at 6:49 AM on February 13, 2018 [1 favorite]

You can also cut and paste your csv data into your formatted Excel document, copy a row or column that has the formatting you would like to apply, then use Paste Special... and select Formats. That will paste the desired formatting on the selected cells while leaving the original data undisturbed.
posted by slogger at 10:32 AM on February 13, 2018 [1 favorite]

« Older Beam Me Up   |   i'm not going to take it any more! Alternatives? Newer »
This thread is closed to new comments.