You can import a pipe delimited file into Microsoft Excel to transfer easily the data from another system, such as a database, web application or software program. The pipe character separates each field of text. Most delimited files are separated by either a comma or a tab; however, the pipe can also be used. Delimited files can be read and shared across many different platforms, making it an ideal data sharing format. You can import up to 1,048,576 rows and 16,384 columns.
Import Text File
Video of the Day
Step 1
Open Microsoft Excel.
Video of the Day
Step 2
Click "From Text" in the "Get External Data" section on the "Data" tab. Navigate to the location of the saved data file, and click "Open." The data file needs to be saved as a TXT file for this process to work. This opens the "Text Import Wizard." You can also choose to connect to get external data "From Access," "From Web" or "From Other Sources."
Step 3
Select "Delimited:" from the "Original Data Type" section. Click "Next."
Step 4
Select "Other" and enter a "|" in the "Delimiters" section. Verify that the data looks corrects in the "Data Preview" section and click "Next."
Step 5
Choose a column format for the first column. The options are "General, "Text" and "Date." Choose "General" to convert numeric values to numbers, "Date" to convert values to dates, and the remaining values to "Text." You can also choose "Do Not Import Column (skip)" to not import the column.
Step 6
Click the next column in the "Data Preview" section and repeat Step 5 until all the columns have been formatted or skipped. Click "Finish" to open the text file with pipe-delimited text separated into columns.
Split Pipe Delimitated Columns
Step 1
Select the data that you want to split.
Step 2
Click "Text to Columns" in the "Data Tools" group on the "Data" tab. This opens the "Convert Text to Columns Wizard" dialog box.
Step 3
Select "Other" and enter a "|" in the "Delimiters" section. Verify that the data looks corrects in the "Data Preview" section and click "Next."
Step 4
Choose a column format for the first column. The options are "General, "Text" and "Date." Choose "General" to convert numeric values to numbers, "Date" to convert values to dates, and the remaining values to "Text." You can also choose "Do Not Import Column (skip)" to not import the column.
Step 5
Click the next column in the "Data Preview" section and repeat Step 4 until all the columns have been formatted or skipped. Enter a cell value in the "Destination" column to place the split data or leave "Destination" blank to overwrite the existing data. Click "Finish."