Converting a text file to an Excel workbook?

Question

I have a huge text file, with almost 10,000 rows in it. The information is arranged like a table, just not in table format. That is, on each row, there is information separated by colons (about 15 different "cells" on each line). Is there any easy way to convert this data into an Excel table?

Here is a few lines of what the data in the text file looks like:

ZLWZ:N/A:WUZHONG:CHINA:00:00:00:U:00:00:00:U:0000
ZLXG:SIA:XI'AN:CHINA:00:00:00:U:00:00:00:U:0000
ZLXN:N/A:XINING:CHINA:00:00:00:U:00:00:00:U:0000

Answer 1

Yep...copy the data & paste into excel. This will put it all into the first column of the spreadsheet.



Now select all of the text and in the Data menu select 'text to columns'



Select "delimited" and choose next



The next screen will ask you what the delimiters are...in your case this is the colons...so check the box that says 'other' and in the box next to this option type in a colon



This will automatically put spaces where the colons are



Go straight to Finish and voila..all your text sorted into the appropriate columns.

Answer 2

If ":" is your cell delimiter then follow these instructions



1) Open Excel

2) File -> Open -> browse to your text file



An import wizard will open up



3) Make sure "Delimiter" is selected -> Next

4) Uncheck "Tab" and check "Other" and put ":" in the text box

(you can either leave the "Text qualifier" as quotes or {none})

5) This last section is not required, but you can set the data types of each column if you want... otherwise click finisted.



I hope this helps.

Answer 3

Actually, Excel makes this relatively easy...



Open Excel

Click on 'File' then 'Open'

At the bottom of the Dialog box change 'Files of Type' to "Text Files"

Find your text file, click on it to select it, and then click 'Open'



Excel will open a Text Import Wizard

Select 'Delimited' then click 'Next'

Select 'Other' and type a colon in the box and click 'Next'



This portion of the wizard allows you to select the basic format of each column or 'Skip' the column altogether. (not import it)



When you're done, click 'Finish' and you'll see your text data as individual cells in your Excel spreadsheet.



I hope that helps.

Answer 4

I know Excel has an option to import TAB (ASCII character code 009) separated .txt files, but not colon.



Maybe try to use find and replace in Word to make it TAB.



(Sorry NOT comma, thats .csv)