_Excel_BookOpenText Leading 0's

[SkysLastChance]

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. 

[water]

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

[Danyfirex]

It would be great if you share a demo .txt file.

 

Saludos

[SkysLastChance]

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 November 16, 2021 by SkysLastChance

[water]

Why do you need the leading number?

[SkysLastChance]

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 November 16, 2021 by SkysLastChance

[water]

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

[water]

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.")

 

[SkysLastChance]

You beat me too it.... Haha

 

Thank you very much!

Edited November 16, 2021 by SkysLastChance

[water]