Jump to content
Santhosh_Thiyagarajan

Open fixed width text file in excel

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


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

×
×
  • Create New...