Import CSV into Excel Spreadsheet via OLEDB
The following is a quick how-to explaining the process of importing an external CSV file into an existing Excel spreadsheet. This approach can be very handy if your CSV data is updated regularly by some external application. Keeping the formatting in the spreadsheet and pulling fresh data from a separate CSV is the way to go. The instructions below are for Excel 2010 running on Windows 7 64-bit. Command syntax for other versions of Excel and other platforms may differ.
- Open an existing spreadsheet or create a new one by hitting CTRL-N.
- Click on the “Data” tab –> From Other Sources
- Click on “From Data Connection Wizard”
- Click “Other/Advanced” –> Next
- Select “Microsoft Jet 4.0 OLE DB Provider” –> Next
- Enter the directory path to the CSV file and click on the “All” tab.
Note: do not enter the name of the CSV file, just the directory where it is located. Do not click “Test Connection” just yet.
- Under the “All” tab select “Extended Properties” –> Edit Value
- In the “Property Value” field enter Text;HDR=Yes –> OK
- Click “Test Connection” –> OK
- At this point you should see the CSV files in the directory you specified. Select the one you want –> Next
- Create a name and description for your connection and click Finish.
- Choose the destination for your imported data –> OK
I hope this has been useful and have a blast multiplying your useless spreadsheets!