{"id":4099,"date":"2021-11-08T12:47:43","date_gmt":"2021-11-08T12:47:43","guid":{"rendered":"https:\/\/lgildv5i97.onrocket.site\/answers\/?post_type=question&p=4099"},"modified":"2021-11-08T12:47:52","modified_gmt":"2021-11-08T12:47:52","slug":"dos-batch-to-convert-text-file-with-rows-columns-to-excel-worksheet","status":"publish","type":"question","link":"https:\/\/computing.net\/answers\/show\/dos-batch-to-convert-text-file-with-rows-columns-to-excel-worksheet\/759.html","title":{"rendered":"DOS Batch to Convert Text File with Rows & Columns to Excel Worksheet"},"content":{"rendered":"

Delineate your column information with tabs in your text file (eg. “MyDoc.txt”).<\/h3>\n

Step 1. Open your text file in\u00a0Notepad\u00a0(double-click the text file in\u00a0File Manager).<\/p>\n

Step 2. Select\u00a0Edit\u00a0->\u00a0Replace… from the menu.<\/p>\n

Step 3.\u00a0Type two spaces in the “Find what:” field and one space in the “Replace with:” field.<\/p>\n

Step 4.\u00a0Then click on “Replace All” repeatedly until no more replacements are made.<\/p>\n

Step 5.\u00a0Now type only one space in the “Find what:” field and only a tab in the “Replace with:” field.<\/p>\n

Step 6. Click on the “Find Next” button.<\/p>\n

Step 7. Then click on “Replace” or “Replace All” as appropriate until tabs have been inserted everywhere you want to begin a new column (in each row).<\/p>\n

Step 8. Then select\u00a0File\u00a0->\u00a0Save, then\u00a0File\u00a0->\u00a0Exit\u00a0(to exit\u00a0Notepad).<\/p>\n

Create Excel worksheet using a DOS Batch File.<\/h3>\n

Step 1. Create the batch file (eg. “TextToExcel.bat”).<\/p>\n

Step 1a. Create a document in\u00a0Notepad\u00a0with the following contents:<\/p>\n

@echo off<\/p>\n

copy %1 %1.xls > nul
\nexcel %1.xls<\/p>\n

Step 1b. Then select\u00a0File\u00a0->\u00a0Save As…, change the filename extension from “.txt” to “.bat”, then click on the\u00a0Save\u00a0button.<\/p>\n

Step 1c. Then\u00a0File\u00a0->\u00a0Exit\u00a0(to exit\u00a0Notepad).<\/p>\n

Step 2. Run your batch file to create the\u00a0Excel worksheet.<\/p>\n

Step 2a. Click on\u00a0Start\u00a0->\u00a0Run…, or alternatively\u00a0Start\u00a0->\u00a0Programs\u00a0->\u00a0Accessories\u00a0->\u00a0Command Prompt.<\/p>\n

Step 2b. Type “[d1:][path1]TextToExcel [d2:][path2]MyDoc.txt” and hit\u00a0Enter.<\/p>\n

NOTE: Substitute the appropriate (optional) drives and paths as applicable.<\/p>\n

NOTE: If you copy your batch file to a directory that is in the path statement, “d1:” and “path1” will not be needed.<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":13},"question-category":[55],"question_tags":[],"class_list":["post-4099","question","type-question","status-publish","hentry","question-category-programming"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/4099","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=4099"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=4099"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=4099"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=4099"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}