Yahoo Answers is shutting down on 4 May 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Converting text file to data table?
I have a text file in the format:
[Field name 1]: [data]
[Field name 2]: [data]
...
[Field name 5]: [data]
[Field name 1]: [data]
...
&c
I would like to convert this to a Word Table or an Excel Spreadsheet (or an Access Table) ... or, indeed, the equivalent in Open Office.
I could write a VB script but it would take me about the same time to convert the data I have manually!
Is there already a small piece of freeware available which will do the job? (or can anyone suggest a quick way of doing it in Excel?)
CSV (or any other separator) is easy, of course, if your first line contains the field names and the following lines contain data. I can see no easy way of converting, though, when every line consists of [Field Name]: [data]. All that 'Convert Text' will do is give me a column of repeated Field Names and a column of Data.
Stegs and GavL: You haven't read the question properly!
Sulanu M has been reported for spamming (verified by looking at other responses).
4 Answers
- YahgoogleLv 41 decade agoFavourite answer
'Replace Pioneer' is highly recommended for resolving your issue.
1. launch 'Replace Pioneer' and press ctrl-o to open your source file(plain text).
2. ctrl-h open 'Replace' dialog
in 'Replace' page:
* set 'Replace Scope' to 'Line'
* set 'Search for pattern' to (no space at end)
^.*?:
* click 'Replace', all field names will be stripped, only data left
[data]
[data]
[data]
[data]
....
3. ctrl-h open 'Replace' dialog again.
in 'Replace' page:
* set 'unit number filter' to !5<5>
* set 'search for pattern' to \n
* set 'replace with pattern' to \t
* click 'Replace', all data will be re-arranged, every 5 [data] will be delimitered by <table> key, and arranged in same line
4. copy above data and paste to excel table.
Example:
[data] [data] [data] [data] [data]
[data] [data] [data] [data] [data]
.....
Note: 5<5> means 5,10,15,20,...(start from 5, increased by 5)
!5<5> means 1,2,3,4, 6,7,8,9, 11,12,13,14, ...(numbers other than 5,10,15,...)
Above operation means replace 1,2,3,4, 6,7,8,9, ... \n(line break) with \t(table key).
** Automation support:
You can also save above two steps in a script file, and launch them by select 'Replace->Replace by script' menu in one time in the future.
** Batch support:
If you click 'Batch...' instead of 'Replace', you can be brought to 'Batch Runner' window, which enable you to process many files in one time.
Replace Pioneer free trial download available at http://www.mind-pioneer.com/
Another approach for a similar example is available at referred address:
- 1 decade ago
Comma separated Values
If you replace all the ":" with "," you should be able to import the information as comma separated values (CSV) and Excel will split the information into columns for you. One column for Field Name, One for Data.
Alternatively if the field names are the same length, you can simply import from a Text file and Excel will ask you where you want to split the rows of information. So if the ":" is at the same point on each row you can tell Excel to the put the column split there. I used to do this regularly and never had any problems.
Good Luck
- 1 decade ago
You can import text files to Excel and specify the delimiter to separate data into cells using a wizard. Just specify the colon and it should work. You can find more details by searching your help file for something like "import text file".
- 5 years ago
Hey 'just JR' of web2coders, you piece of garbage. Stop giving advice and pay back the people who you stole money from, you thief!!
