xcaliber13 Posted February 26, 2015 Share Posted February 26, 2015 New to the forums and have been using AutoIt for a couple of months. Here is my problem, I have a text file that imports into excel with the correct columns if I use: _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth) I get the correct information into the correct number of columns that I expect. (8 columns) The only issue that no matter what I try to do to correct is that in column 1 (first column) it is dropping the leading zero. If I try to import any other way than FixedWidth my data does not get into the correct columns. The text file is not delimited other than spaces. Any suggestions as to get it from dropping the leading zero when imported using fixedwidth? Thank you Link to comment Share on other sites More sharing options...
water Posted February 26, 2015 Share Posted February 26, 2015 Welcome to AutoIt and the forum! The function only imports data, no formatting. The data in column 1 is imported and then formatted by Excel using the default format. This default format does not show leading zeros. So if you need the leading zeroa you need to change the formatting of the cells in column 1. 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...
xcaliber13 Posted February 27, 2015 Author Share Posted February 27, 2015 Water Thank you for the quick reply. Yes I do understand that excel is formatting the column. I am guessing that I did not make myself clear enough on what is my problem. I have been researching this issue for 2 weeks. I have tried all the different suggestions that have been in this forum. I have tried different variations of the follow code: Local $aField1[2] = [1, $xlTextFormat] Local $aField2[2] = [2, $xlTextFormat] Local $aField3[2] = [3, $xlTextFormat] Local $aField4[2] = [4, $xlTextFormat] Local $aField5[2] = [5, $xlTextFormat] Local $aField6[2] = [6, $xlTextFormat] Local $aField7[2] = [7, $xlTextFormat] Local $aField8[2] = [8, $xlTextFormat] Local $aFieldInfo[8] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8] _Excel_BookOpenText($oAppl, $sTextFile, Default, $xlDelimited, Default, True, "", $aFieldInfo, Default, Default) Now this code will indeed format the columns correctly (No leading zeros dropped) but it does not import into correct columns. With the different variations I either get less columns or more columns of data going into the excel spreadsheet. So using $xlFixedWidth I get the correct number of columns with the correct data in each column but leading zeros dropped Using the other variations that I have tried I get more or less than the expected columns with the incorrect data in each column but no leading zeros dropped. What the heck am I doing wrong? Link to comment Share on other sites More sharing options...
water Posted February 27, 2015 Share Posted February 27, 2015 This MSDN article describes how to format numeric data with leading zeroes. Means you could import data as you did before and then format (even by using AutoIt) column A as needed. 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...
water Posted February 27, 2015 Share Posted February 27, 2015 Another approach: Could you please post a signle line of your input file so we can play with it? 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...
xcaliber13 Posted February 27, 2015 Author Share Posted February 27, 2015 Here are a few lines of the text file I am using. Text modified so as NOT to be actual live data. But still in the same format as original file. 047182 12/01/2014 SMITH,JOE JOHN SPINE CERVICAL, 2 OR 3 VWS 72040 JOHN JOE MD SIGNED 65019325 047182 12/01/2014 SMITH,JOE JOHN SPINE CERVICAL, 2 OR 3 VWS 72040 JOHN JOE MD SIGNED 65022204 202038 12/01/2014 FELTLINK, MARIE BETH DIGITAL MAM BILAT;W/CAD 77052 FRIDAY D. JOE,MD SIGNED 61668406 077371 12/01/2014 JONES,ROBERT BOB JR WRIST LEFT MIN 3 VIEWS 73110 MIKE S. SMITH, SIGNED 65030280 077371 12/01/2014 JONES,ROBERT BOB JR SHOULDER RIGHT COMPLETE MIN 2V 73030 MIKE S. SMITH, SIGNED 65030280 077371 12/01/2014 JONES,ROBERT BOB JR CT ABDOMEN W/WO CONTRAST 74170 MIKE S. SMITH, SIGNED 65030280 Not showing here but all data is single line being with the numbers. It is these first numbers that I am having a hard time to save the leading zero upon importing. Link to comment Share on other sites More sharing options...
water Posted February 27, 2015 Share Posted February 27, 2015 (edited) First try.#include <Excel.au3> Global $oExcel = _Excel_Open() Global $sTextFile = @ScriptDir & "\test.txt" Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth) $oWorkbook.ActiveSheet.Range("A:A").NumberFormat = "0000000"Formats colum A with 7 digits, adds 0s on the left where needed. Edited February 27, 2015 by water 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...
Solution xcaliber13 Posted February 27, 2015 Author Solution Share Posted February 27, 2015 Water YOU ARE THE BEST!!!! That did exactly what I need. Many, Many, and a thousand more THANK YOU's!!!!!!!!!!!!!!!!! Link to comment Share on other sites More sharing options...
water Posted February 27, 2015 Share Posted February 27, 2015 Glad to be of service 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...
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