How to Reformat Exception File
Description: This solution describes how to reformat exception files that populate all data in one column.
Open the data file in Excel, highlight the entire column where the data resides. Next, select "Data" from the tool bar at the top of the view. Next you will select the "Text to Columns" button in the tool bar that appears under the "Data" selection. After that the text wizard will appear. Follow the steps below from that point:
- Make sure that Delimited is selected and then click Next.
- On the next page, make sure that Comma or Tab or both Comma and Tab are selected, depending on your data file delimiters.
- Select Text in the next prompt.
- Click the Finish button to open it correctly.
To prevent this in the future, when opening the output file and making changes, you will need to select "Save As" and change the file type from "unicode text" to ".csv". Any time that the output file is opened and saved without choosing to save as and updating the file type, the file will result in the data being pushed into a single column.