#1
|
|||
|
|||
convert text to tables
Not sure if Word is the right forum. I use Office 2013. I have a delimited file. four consecutive commas is the column delimiter.
The first 7 columns are only 30 characters in length, the other two columns contains many lines, they also contain carriage return and line feed. I was not able to import it to Excel,Libre Calc,Rons Editor,csved(let me know if I can add to this list). By that I mean that I wanted the cells in the last two columns, the eighth and the ninth column to wrap lines as and when there is a CRLF. So this did not work because CRLFs are treated as next row in spreadsheets. Now I want to convert text pasted in word to table. I am using Landscape mode since the eight and ninth column are like(almost) an A4 page themselves. This option-convert text to table is greyed out. Help me out please. THanks in advance |
#2
|
||||
|
||||
We need to see the input text file to suggest a way of solving this.
You need to explain how the process can decide when a new row needs to be created. If any cells can contain crlf then there needs to be a macro or some other automated process to transfer this information up to the preceding row. It may be possible to do without a macro but without seeing a significant portion of the text file, we have no way of making a meaningful suggestion.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Please use this attachment. THere are multiple such rows. THis is just one such row from the large text file I have.
|
#4
|
|||
|
|||
I looked at the attachment, although on my screen it still indicates 0 views.
My general approach would be to replace crlf with the pipe character (|) before the import (it rarely appears in text, so it is my go-to delimiter, but another character could be used). After the import that character could be replaced with crlf. The thing I find myself wondering is how you would distinguish between crlf in the text you supplied, and crlf that would start the next row. BTW, a trick for getting a quick block of text if Word: in any Word document type =rand() and press Enter. Put a number in the parentheses for a longer chunk of text. |
#5
|
|||
|
|||
Yes the issue you say you are wondering about is the main issue.
Another issue that I faced is Excel spills data to the next column automatically. So, if my eighth and ninth row exceeds certain length next column is used. And I cannot use pipe as the delimiter since this data is from scripts that are being searched. Shell scripts which contain many such characters like dolar, hash etc. |
#6
|
||||
|
||||
I would do this in Word to avoid the issue of Excel spilling over.
You didn't give us much of a view of your dataset but assuming each record actually starts with "/" then it would be simple to do a search and replace series and then to convert it all to a table. Find "^p" and replace with "zxzx" Find "zxzx/" and replace with "^p/" Find ",,,," and replace with "^t" Now convert to table with tab delimiters.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia Last edited by Guessed; 03-17-2015 at 05:13 PM. Reason: Forgot to include the / in the second replace (as spotted by BruceM) |
#7
|
|||
|
|||
I suggested the pipe character because it is rarely used, in my experience, but as I mentioned it could be any character, or a string of characters as Andrew showed. There must be some character of combination of characters that will not be found in the source text.
Like Andrew, I would use Word to manipulate the text. After that it can be converted to a table as suggested. The table can then be copied to Excel. Or you could convert to a csv file, which can be opened directly with Access. Let me build a little on Andrew's approach. You could replace paragraph marks (^p) with "zxzx". Then find "zxzx/" and convert it to a paragraph and a slash (^p/). Convert "zxzx" to a space, and convert four consecutive commas to a tab (^t). Now you can convert to the text to a table, using tab delimiters. Instead of a tab you could do the above, except replace four commas with one comma. Save the result as a csv file, and open it in Excel. All of this assumes the rest of the rows are in similar format, with a slash at the beginning of each new row. If not, the question remains how you know when you are at a new row. In either a table cell or an Excel cell, word wrapping can take care of long lines without the need for line breaks. |
#8
|
|||
|
|||
Its okay if the lines do no get wrapped within the cell. If they use the next row of the same column that serves my purpose. I dont want the wrapped lines of seventh and eighth column to use the first column. I can also provide an end of line text delimiter like "end_of_line" and start of line delimiter like "start_of_line"
Can you please show this on the attachment I pasted below. I've added an extra column just to ensure there is no spill from seventh column to eighth column. |
#9
|
|||
|
|||
Using Andrew's suggestion for find and replace, with the amendments I described, I pasted the content twice into a Word document (twice because you provided only one row), then performed the find and replace steps as described. I used the option to replace four commas with one comma.
I then tried two options, both of which worked: Save the file as a text file, rename it as a csv file, and open with Excel; or create a table from text with commas as the separator, copy the table, and paste it into Excel. |
Tags |
convert text to table |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Convert Text To Table: Separate text at (Other)? | tinfanide | Word VBA | 2 | 01-12-2015 05:26 AM |
Convert image-text hybrids into plain text | morlack | Excel | 4 | 12-03-2014 05:29 PM |
convert tables to fillable forms in word | expert4knowledge | Word | 1 | 02-13-2014 03:06 AM |
My plain text post got converted to rich text in a reply, how to convert it back? | david.karr | Outlook | 0 | 01-05-2012 09:46 AM |
Convert Number to Text | devcon | Word | 0 | 07-10-2010 01:16 AM |