IronGirl Posted July 7, 2020 Share Posted July 7, 2020 Hello folks, I'm new to Autoit and I just wanna know if there is some script that helps me to populate my excel template from a text file (.txt). I have a text file with lots of columns and lines and I would like to read these data from each column and save in Excel's columns. Is there some script here that I can do this? If there is, please, indicate to me, if not, can someone help me with this script? I appreciate your help, Tks! Link to comment Share on other sites More sharing options...
jchd Posted July 7, 2020 Share Posted July 7, 2020 What is the format of your text file? If that's a .csv or .tsv then import in Excel is a standard feature. There are plenty of examples for processing .CSV data and there is a pretty good Excel UDF. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
IronGirl Posted July 7, 2020 Author Share Posted July 7, 2020 Dear jchd, My text file format is .txt... with tabular separation. Is there a script for this type of file? I have this kind of file, with tabular separation, and I have to past to an Excel Sheet. 0, 5162, -2410,9 127, 23,52 4,9431 26,455 5,1758 23,664 5,2917 5162, -2410,9 121,71 23,52 4,7372 26,321 4,7445 23,735 10,583 5162, -2410,9 116,42 23,52 4,5312 26,188 4,3131 23,812 15,875 5162, -2410,9 111,12 23,52 4,3252 26,055 3,8818 23,895 21,167 5162, -2410,9 105,83 23,52 4,1193 25,924 3,4504 23,984 26,458 5162, -2410,9 100,54 23,52 3,9133 25,793 3,0191 24,079 Tks! Link to comment Share on other sites More sharing options...
water Posted July 7, 2020 Share Posted July 7, 2020 You need something like this: #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpenText($oExcel, "C:\Local\Tab.txt", 1, Default, Default, Default, @TAB) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jchd Posted July 7, 2020 Share Posted July 7, 2020 Almost what @water said, but the example provided uses 4 whitespaces and not tabs (maybe due to some conversion during copy-paste) and more worrisomely, the formatting of numbers (a space as thousands separator, comma as decimal point, no decimal part everywhere) may require a few changes before supplying the data to Excel. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
water Posted July 7, 2020 Share Posted July 7, 2020 I have modified the provided data and changed 4 spaces to a tab character. No further modification was needed for a german system which uses comma as decimal point. Excel handles data without decimal part just fine in my environment. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jchd Posted July 7, 2020 Share Posted July 7, 2020 Wow, and it does understand 12345, w/o a decimal part and 12 3456,78 with the thousands separator? OK; Excel 15 - jchd 0 But I wonder how that would work on a system where the locale is different. Let the OP try and tell us. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
water Posted July 7, 2020 Share Posted July 7, 2020 I didn't test with 12 3456,78 as the OPs data does not include a value with space as thousands separator. Let's see if the OP is happy with the provided solution. If the system has a different locale you simply provide the decimal and thousands separator to use as parameter to _Excel_BookOpenText. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jchd Posted July 8, 2020 Share Posted July 8, 2020 Sorry I misread the data; never mind, too tired. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
water Posted July 8, 2020 Share Posted July 8, 2020 I just tested the thousands separator. Space lets Excel interpret the value as text. When I use a dot the value is correctly interpreted as number. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
IronGirl Posted July 10, 2020 Author Share Posted July 10, 2020 Dears, Thanks for helping me, it works perfectly. I didn't answer before because I had health issues in the last day. I'm good now. Let me enjoy your knowledge, is it possible to create an initial template to Excel? Let me explain in a better way, I would like to know where I can find some details about how to create a previously Excel sheet template to import these data with the Script that you give to me? Is it possible? I appreciate the help of the spend time with me, explaining these kinds of subject that for you is boring. Tks!!! Link to comment Share on other sites More sharing options...
water Posted July 10, 2020 Share Posted July 10, 2020 _Excel_BookOpenText loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data. Excel does not allow to specify a template to use. But you could open your template, save as a new workbook and then copy the data from the parsed workbook. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Zedna Posted July 10, 2020 Share Posted July 10, 2020 (edited) Here is simple convertor from TXT (with TAB) to CSV (with ; ) $text = FileRead('file.txt') $text = StringReplace($text, @TAB, ';', 0, 1) FileDelete('file.csv') FileWrite('file.csv', $text) CSV is "natural" format for Excel, so CSV can be just opened (instead of importing TXT). Edited July 10, 2020 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
IronGirl Posted July 15, 2020 Author Share Posted July 15, 2020 (edited) I appreciate the help from Water, JCHD and Zedna. Thank You! Edited July 15, 2020 by IronGirl Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now