Jump to content

_Excel_BookOpenText Leading 0's


Go to solution Solved by water,

Recommended Posts

Is there a way to stop _Excel_BookOpenText () from removing leading 0's? 

This is what I have been using. 

$oWorkbook = _Excel_BookOpenText($oExcel, $sExcelFile, 1,$xlDelimited,Default,Default,"|")

I have codes in my columns that have 08888809, but when I put them through my code it turns it into 8888809. 

I am opening some fairly large files when doing this. I am not sure if that maters or not. 

You can also see this happening in the example

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpenText Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open a text file as delimited, separator = |, pass fieldinfo and set
; DecimalSeparator and ThousandsSeparator.
Local $sTextFile = @ScriptDir & "\Extras\_Excel1.txt"
Local $aField1[2] = [1, $xlTextFormat]
Local $aField2[2] = [2, $xlTextFormat]
Local $aField3[2] = [3, $xlGeneralFormat]
Local $aField4[2] = [4, $xlDMYFormat]
Local $aField5[2] = [5, $xlTextFormat]
Local $aFieldInfo[5] = [$aField1, $aField2, $aField3, $aField4, $aField5]
_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlDelimited, Default, True, "|", $aFieldInfo, ",", ".")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpenText Example 1", "Error opening '" & $sTextFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpenText Example 1", "Workbook '" & $sTextFile & "' has been opened successfully.")

I added another delimited field to my extra text file, but you can see it is doing it with the dates too. 

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites

Can you post an example of the text file you import?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

No problem. 

JDOE|Doe,John||Earth|Alaska|Test|TEST|Test|08000000|Test|10/01/21|AA1234567891|L120000|AVE|AVE|10/12/21|1|40.00|0|0

The 8000000 Column "I", once in excel is my trouble child. 

This number can be 10 digits to 7 digits with a leading 0 sometimes 

I have been looking at this Thread.

Problem is my code can change in number of digits. Unlike OP of this. 

 

Changing the format after _Excel_BookOpenText does not seem to be working for me. 

 

Like this.. 

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $sTextFile = @ScriptDir & "\test.txt"
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sExcelFile, 1,$xlDelimited,Default,Default,"|")
$oWorkbook.ActiveSheet.Columns("I:I").NumberFormat = "@"

I am guessing I need to format before I import the data. I am not sure how to do this though. 

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites

Why do you need the leading number?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

I might be misunderstanding the question. 

But, When we search for a code in our system. 

If we search for 800000 and the code is really 0800000 it won't show up or could be a completely different code. I never know which codes should have the 0 and which do not. 

It can also mess with are V-lookups 

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites

If it is a number with a leading 0 and can be 7 to 10 digits long then - in my opinion - this is a text.
So you need to specify the correct format for the column when importing. See the example in your post #1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Solution

This works for me:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()

; Open a text file as delimited, separator = |, pass fieldinfo and set
; DecimalSeparator and ThousandsSeparator.
Local $sTextFile = @ScriptDir & "\Import.txt"
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[] = [$aField1, $aField2, $aField3, $aField4, $aField5, $aField6, $aField7, $aField8]
_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlDelimited, Default, True, "|", $aFieldInfo, ",", ".")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening '" & $sTextFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpenText Example 1", "Workbook '" & $sTextFile & "' has been opened successfully.")

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...