Tuesday, August 7, 2012

Importing and exporting Fixed Width data in Excel-2010

How to import “Fixed Width” data from a text file to Excel

·         Select all the contents in .txt file and copy them.
·         Paste them in Excel Sheet. This will be pasted in one column (say Column A)
·         Select the Column A and go to “Data” tab and click “Text To Column” option.
·         Select the Fixed Width Format option and click “Next”.
·         Here you would see in grid that data is separated by some columns.  These columns are identified by the MS-Excel by default. In case, if you see column distribution is not proper, you may add/move/delete the column as per columns width mentioned in your data dictionary.
·         Click finish, it will convert one column contents into desired columns.

How to export “Fixed Width” data from Excel to text file

·         Go to File-> Save AS
·         Select option Formatted Text (Space Delimited) option
·         Set file extension “.prn”
·         Click “SAVE AS” button. A file (i.e. MyExportedData.prn) will be saved with .prn extension .
·         Now go to the property of the MyExportedData.prn file by right clicking the file.
·         Remove the current file extension (i.e. .prn) and put the file extension “.txt” extension in front of your file name. The changing file extension is done in “General” page of the property window.

No comments:

Post a Comment