Saturday, November 24, 2012

How to Import OFX Files Into Microsoft Excel


1. Click 'Start,' type 'notepad' (without quotes) and press 'Enter.' Press 'Ctrl O.' Click the 'File type' drop-down menu and select 'All Files and Folders *.*.' Locate your OFX file, select it and click 'Open.'
2. Browse through your OFX file. You will notice that there is a pattern among all entries. Each OFX file will be different, but in general you will notice fields of data such as times, amounts or comments. There will also be a selection of letters or characters separating each line of data (e.g., hrt]). This 'separator' will be the same throughout. Determine what the separator is.
3. Click 'View,' then 'Find and Replace.' In the 'Find' field, type out the separator that currently exists in your document.
4. Enter '|' (bar, not an L) by pressing 'Shift \' in the 'Replace' field. Click 'Replace All.' This will replace all the existing separators and replace them with bar--an Excel compatible separator.
5. Click 'File,' then 'Save As.' Click the 'File type' drop-down menu and select 'All Files and Folders.' Enter 'toimport.txt' (without quotes) in the file name and click 'Save.'
6. Launch Microsoft Excel. Click the Microsoft Office logo in the ribbon. Click 'Open.'
7. Select 'Text Files' from the list, then navigate to 'toimport.txt' and double-click it to open the file. The Text Import Wizard will open.
8. Select 'Delimited' in the 'Original data type' field. Click 'Next.'
9. Set the 'Delimiters' to '|' ('Shift \') and click 'Next.' A preview of your import will appear. Click 'Finish' to import your data to Microsoft Excel.

Blogger news