Open fixed width text file in excel

[Santhosh_Thiyagarajan 0]

Hi Team, I'm trying to use the power of AutoIT to simplify one of my daily tasks.

My question here is; I have a text file that I need to open in excel & there are no delimiter symbols in the file. I usually open excel and data import & select delimiter as 'Fixed Width' and provide the length of each column in the wizard. 

I'm trying to use the _Excel_BookOpenText function to perform this operation by passing an array on attribute 7 ($sDelimiter), but the script doesn't recognize the delimiter fixed width values

Here is the file that I'm trying to open and the script that I wrote. can someone help me with this, please?

 

------------------

#include <excel.au3>
#include <Array.au3>

Local $oExcel = _Excel_Open()
Global $sTextFile = @ScriptDir & "\file.txt"
Global $MyDelimiter [43] = ["0","1","3","5","13","17","25","35","45","47","54","59","64","71","77","82","88","93","99","104","110","115","122","129","133","143","145","146","152","158","164","170","172","185","193","198","210","216","226","228","229","232","235"]
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, Default, $xlDelimited, $xlTextQualifierDoubleQuote, True,$MyDelimiter, Default, ",", ".")

------------------

file.txt

[Subz 524]

Try:

Local $sTextFile = @ScriptDir & "\File.txt"
_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth)

 

[Santhosh_Thiyagarajan 0]

Thanks for your suggestions Subz. I tried the same, but it just opened the file in a spreadsheet with its default delimiter.

 

[Subz 524]

You still require fieldinfo array, although you would need to use 2 element array for example:

expandcollapse popup

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $sTextFile = @ScriptDir & "\File.txt"
Local $aMyDelimiter1 = [0, $xlTextFormat]
Local $aMyDelimiter2 = [1, $xlTextFormat]
Local $aMyDelimiter3 = [3, $xlTextFormat]
Local $aMyDelimiter4 = [5, $xlTextFormat]
Local $aMyDelimiter5 = [13, $xlTextFormat]
Local $aMyDelimiter6 = [17, $xlTextFormat]
Local $aMyDelimiter7 = [25, $xlTextFormat]
Local $aMyDelimiter8 = [35, $xlTextFormat]
Local $aMyDelimiter9 = [45, $xlTextFormat]
Local $aMyDelimiter10 = [47, $xlTextFormat]
Local $aMyDelimiter11 = [54, $xlTextFormat]
Local $aMyDelimiter12 = [59, $xlTextFormat]
Local $aMyDelimiter13 = [64, $xlTextFormat]
Local $aMyDelimiter14 = [71, $xlTextFormat]
Local $aMyDelimiter15 = [77, $xlTextFormat]
Local $aMyDelimiter16 = [82, $xlTextFormat]
Local $aMyDelimiter17 = [88, $xlTextFormat]
Local $aMyDelimiter18 = [93, $xlTextFormat]
Local $aMyDelimiter19 = [99, $xlTextFormat]
Local $aMyDelimiter20 = [104, $xlTextFormat]
Local $aMyDelimiter21 = [110, $xlTextFormat]
Local $aMyDelimiter22 = [115, $xlTextFormat]
Local $aMyDelimiter23 = [122, $xlTextFormat]
Local $aMyDelimiter24 = [129, $xlTextFormat]
Local $aMyDelimiter25 = [133, $xlTextFormat]
Local $aMyDelimiter26 = [143, $xlTextFormat]
Local $aMyDelimiter27 = [145, $xlTextFormat]
Local $aMyDelimiter28 = [146, $xlTextFormat]
Local $aMyDelimiter29 = [152, $xlTextFormat]
Local $aMyDelimiter30 = [158, $xlTextFormat]
Local $aMyDelimiter31 = [164, $xlTextFormat]
Local $aMyDelimiter32 = [170, $xlTextFormat]
Local $aMyDelimiter33 = [172, $xlTextFormat]
Local $aMyDelimiter34 = [185, $xlTextFormat]
Local $aMyDelimiter35 = [193, $xlTextFormat]
Local $aMyDelimiter36 = [198, $xlTextFormat]
Local $aMyDelimiter37 = [210, $xlTextFormat]
Local $aMyDelimiter38 = [216, $xlTextFormat]
Local $aMyDelimiter39 = [226, $xlTextFormat]
Local $aMyDelimiter40 = [228, $xlTextFormat]
Local $aMyDelimiter41 = [229, $xlTextFormat]
Local $aMyDelimiter42 = [232, $xlTextFormat]
Local $aMyDelimiter43 = [235, $xlTextFormat]
Local $aMyDelimiters = [$aMyDelimiter1, $aMyDelimiter2, $aMyDelimiter3, $aMyDelimiter4, $aMyDelimiter5, $aMyDelimiter6, $aMyDelimiter7, $aMyDelimiter8, $aMyDelimiter9, $aMyDelimiter10, $aMyDelimiter11, $aMyDelimiter12, $aMyDelimiter13, $aMyDelimiter14, $aMyDelimiter15, $aMyDelimiter16, $aMyDelimiter17, $aMyDelimiter18, $aMyDelimiter19, $aMyDelimiter20, $aMyDelimiter21, $aMyDelimiter22, $aMyDelimiter23, $aMyDelimiter24, $aMyDelimiter25, $aMyDelimiter26, $aMyDelimiter27, $aMyDelimiter28, $aMyDelimiter29, $aMyDelimiter30, $aMyDelimiter31, $aMyDelimiter32, $aMyDelimiter33, $aMyDelimiter34, $aMyDelimiter35, $aMyDelimiter36, $aMyDelimiter37, $aMyDelimiter38, $aMyDelimiter39, $aMyDelimiter40, $aMyDelimiter41, $aMyDelimiter42, $aMyDelimiter43]
_Excel_BookOpenText($oExcel, $sTextFile, Default, $xlFixedWidth, Default, True, ",", $aMyDelimiters, ",", ".")

 

[Nine 167]

Here a simplified way to construct the $aFieldInfo array :

Global $MyDelimiter [43] = ["0","1","3","5","13","17","25","35","45","47","54","59","64","71","77","82","88","93","99","104","110","115","122","129","133","143","145","146","152","158","164","170","172","185","193","198","210","216","226","228","229","232","235"]
Global $aFieldInfo [UBound($MyDelimiter)]
For $i = 0 to UBound($MyDelimiter)-1
  Local $aTmp[2] = [Number($MyDelimiter[$i]),$xlTextFormat]
  $aFieldInfo[$i] = $aTmp
Next

 

[Santhosh_Thiyagarajan 0]

Thanks, Subz. Your suggestion really helped me. 

Hey Nine, thanks for your simplified code  & I will give it a try.