Sign in to follow this  
Followers 0
JohnRichard

Convert to Excel

16 posts in this topic

hi all. i have this txt file which i load into listview. i wanted to export this txt file into excel. is this possible in autoIt? here is what i have right now to generate excel file out of that text file. anyone can help me?

TXT file is like this...

192.168.1.25 | ACER1 | user1|Denver McCain|12:24:51 PM|10/05/2009
192.168.1.26 | ACER2 | user1|Denver McCain|12:25:19 PM|10/05/2009
192.168.1.35 | ACER3 | user1|Denver McCain|2:14:33 PM|10/05/2009
192.168.1.37 | ACER4 | user1|Denver McCain|2:16:32 PM|10/05/2009
192.168.1.38 | ACER5 | user1|Denver McCain|2:17:38 PM|10/05/2009
192.168.1.40 | ACER6 | user1|Denver McCain|2:19:25 PM|10/05/2009

Func Excel()

    $length = _FileCountLines(@DesktopDir & "log.txt")
    $Rfile = FileOpen(@DesktopDir & "log.xls", 0)
    For $x = 1 To $length; - 1
        $line = FileReadLine($Rfile, $x)
        If @error = -1 Then ExitLoop
        $line_pos = StringSplit($line, @CRLF, 1)        
        FileWriteLine($Rfile, $line_pos)
    Next
    FileClose($Rfile)   
    $Off_loc = FileGetShortName(@DesktopDir & "log.xls")
    Run(@ComSpec & " /c Start " & $Off_loc, "", @SW_SHOW)

EndFunc

appreciate your expert ideas or suggestions. thank you.

Share this post


Link to post
Share on other sites



There are a host of excel functions... look in the helpfile

_Excelbooknew()

_excelrowinster()

ect.

but this will just open an existing excel file. in my case, i am generating a txt file and i want the data in the txt file to be generated in excel file. in the function i posted, i wanted to have that txt file to create a excel file then opens it.

code in helpfile

#include <Excel.au3>
$oExcel = _ExcelBookNew()

forgive. i'm newbie in auto it.

Share this post


Link to post
Share on other sites

#include <Excel.au3>
#include<array.au3>
$title=WinGetTitle("Microsoft Excel","")

if not $title=0 then
$oExcel = _ExcelBookAttach($title, "Title")
WinActivate($title)

Else
    $oExcel=_excelbooknew()
EndIf
for $j=1 to 10
For $i = 1 To 5 ;Loop
    _ExcelWriteCell($oExcel, "Data "&$i, $j, $i) ;Write to the Cell
Next
next

Share this post


Link to post
Share on other sites

hi ace. i'm really got confused. i'm not good as you. how am i load the data in the txt file into excel.

only 1 data is inserted into the first column of the excel. i mean all of the first line of data from txt file is inserted into first column of the excel...

forgive my ignorance...

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

#include <Excel.au3>
#include <file.au3>

$title=WinGetTitle("Microsoft Excel","")

if not $title=0 then
$oExcel = _ExcelBookAttach($title, "Title")
WinActivate($title)

Else
    $oExcel=_excelbooknew()
EndIf

dim $var
_FileReadToArray(@DesktopDir&"\log.txt",$var)


for $ct=1 to ubound($var)-1
 $stringsplit=StringSplit($var[$ct],"|")
 for $ct2= 1 to $stringsplit[0]
 _ExcelWriteCell($oExcel,$stringsplit[$ct2],$ct,$ct2)
Next
next

the above program works, but has no error checking from the file or the stringsplit... hope this helps

Edited by Aceguy

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Another way is for Excel to directly read your text file

$sPathFile = @ScriptDir & "\excel_test.$$$"
$sTxt = "192.168.1.25 | ACER1 | user1|Denver McCain|12:24:51 PM|10/05/2009" & @CRLF & _
        "192.168.1.26 | ACER2 | user1|Denver McCain|12:25:19 PM|10/05/2009" & @CRLF & _
        "192.168.1.35 | ACER3 | user1|Denver McCain|2:14:33 PM|10/05/2009" & @CRLF & _
        "192.168.1.37 | ACER4 | user1|Denver McCain|2:16:32 PM|10/05/2009" & @CRLF & _
        "192.168.1.38 | ACER5 | user1|Denver McCain|2:17:38 PM|10/05/2009" & @CRLF & _
        "192.168.1.40 | ACER6 | user1|Denver McCain|2:19:25 PM|10/05/2009"
If FileExists($sPathFile) Then FileDelete($sPathFile)
FileWrite($sPathFile, $sTxt)

$oExcel = ObjCreate("Excel.Application")
If IsObj($oExcel) Then
    $oExcel.Visible = 1
    ;$oExcel.Workbooks.OpenText($sFile, 2, 1, 1, -4142, False, False, False, False, False, True, "|")
    $oExcel.Workbooks.OpenText($sPathFile, 2, 1, 1, 1, False, False, False, False, False, True, "|")
    $oExcel.Worksheets(StringRegExpReplace(StringRegExpReplace($sPathFile, '([\\\w:]*)\\', ''), '\..*', '') ).Columns("A:Z" ).AutoFit
EndIf
Edit: better regexp

Edited by picaxe

Share this post


Link to post
Share on other sites

Just an enhanced version of what picaxe posted.

#include <IE.au3>
#include <Excel.au3>

#AutoIt3Wrapper_Au3Check_Parameters = -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

; XlTextQualifier Enumeration
Const $xlTextQualifierDoubleQuote = 1
Const $xlTextQualifierNone = -4142
Const $xlTextQualifierSingleQuote = 2

; XlTextParsingType Enumeration
Const $xlDelimited = 1
Const $xlFixedWidth = 2

_IEErrorHandlerRegister()
Global $sFilePath = "C:\Temp\Test.txt"
Global $oExcel = _ExcelBookOpenText($sFilePath, True, 1, $xlDelimited, $xlTextQualifierDoubleQuote, False, False, False, False, False, True, "|")
$oExcel.ActiveWorkbook.Sheets(1).Cells.EntireColumn.AutoFit


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookOpenText
; Description ...: Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data
; Syntax.........: _ExcelBookOpenText($sFilePath, $fVisible = 1, $iStartRow = 1, $vDataType = Default, $vTextQualifier = $xlTextQualifierNone, $fConsecutiveDelimiter = False, _
;                  $fTab = False, $fSemicolon = False, $fComma = False, $fSpace = False, $fOther = False, $sOtherChar = Default)
; Parameters ....: $sFilePath - Path and filename of the file to be opened
;                  $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                  $iStartRow - The row number at which to start parsing text. The default value is 1
;                  $vDataType - Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth.
;                               If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.
;                  $vTextQualifier - Specifies the text qualifier. Can be one of the following  XlTextQualifier constants: xlTextQualifierDoubleQuote, xlTextQualifierNone, or xlTextQualifierSingleQuote
;                  $fConsecutiveDelimiter - True to have consecutive delimiters considered one delimiter. The default is False.
;                  $fTab - True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fSemicolon - True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fComma - True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fSpace - True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fOther - True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $sOtherChar - (required if Other is True). Specifies the delimiter character when Other is True.
;                                If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
; Return values .: Success      - Returns new object identifier
;                  Failure      - Returns 0 and sets @error on errors:
;                  @error=1     - Unable to create the object
;                  @error=2     - File does not exist
; Author ........: Bob Anthony <big_daddy>
; Modified.......:
; Remarks .......: None
; Related .......:
; Link ..........;
; Example .......;
; ===============================================================================================================================
Func _ExcelBookOpenText($sFilePath, $fVisible = 1, $iStartRow = 1, $vDataType = Default, $vTextQualifier = $xlTextQualifierDoubleQuote, $fConsecutiveDelimiter = False, _
        $fTab = False, $fSemicolon = False, $fComma = False, $fSpace = False, $fOther = False, $sOtherChar = Default)
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0

    With $oExcel
        .Visible = $fVisible
        .Workbooks.OpenText($sFilePath, Default, $iStartRow, $vDataType, $vTextQualifier, $fConsecutiveDelimiter, $fTab, $fSemicolon, $fComma, $fSpace, $fOther, $sOtherChar)
        .ActiveWorkbook.Sheets(1).Select ()
    EndWith
    Return $oExcel
EndFunc   ;==>_ExcelBookOpenText

Share this post


Link to post
Share on other sites

Just an enhanced version of what picaxe posted.

#include <IE.au3>
#include <Excel.au3>

#AutoIt3Wrapper_Au3Check_Parameters = -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

; XlTextQualifier Enumeration
Const $xlTextQualifierDoubleQuote = 1
Const $xlTextQualifierNone = -4142
Const $xlTextQualifierSingleQuote = 2

; XlTextParsingType Enumeration
Const $xlDelimited = 1
Const $xlFixedWidth = 2

_IEErrorHandlerRegister()
Global $sFilePath = "C:\Temp\Test.txt"
Global $oExcel = _ExcelBookOpenText($sFilePath, True, 1, $xlDelimited, $xlTextQualifierDoubleQuote, False, False, False, False, False, True, "|")
$oExcel.ActiveWorkbook.Sheets(1).Cells.EntireColumn.AutoFit


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookOpenText
; Description ...: Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data
; Syntax.........: _ExcelBookOpenText($sFilePath, $fVisible = 1, $iStartRow = 1, $vDataType = Default, $vTextQualifier = $xlTextQualifierNone, $fConsecutiveDelimiter = False, _
;                  $fTab = False, $fSemicolon = False, $fComma = False, $fSpace = False, $fOther = False, $sOtherChar = Default)
; Parameters ....: $sFilePath - Path and filename of the file to be opened
;                  $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                  $iStartRow - The row number at which to start parsing text. The default value is 1
;                  $vDataType - Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth.
;                               If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.
;                  $vTextQualifier - Specifies the text qualifier. Can be one of the following  XlTextQualifier constants: xlTextQualifierDoubleQuote, xlTextQualifierNone, or xlTextQualifierSingleQuote
;                  $fConsecutiveDelimiter - True to have consecutive delimiters considered one delimiter. The default is False.
;                  $fTab - True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fSemicolon - True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fComma - True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fSpace - True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $fOther - True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.
;                  $sOtherChar - (required if Other is True). Specifies the delimiter character when Other is True.
;                                If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
; Return values .: Success      - Returns new object identifier
;                  Failure      - Returns 0 and sets @error on errors:
;                  @error=1     - Unable to create the object
;                  @error=2     - File does not exist
; Author ........: Bob Anthony <big_daddy>
; Modified.......:
; Remarks .......: None
; Related .......:
; Link ..........;
; Example .......;
; ===============================================================================================================================
Func _ExcelBookOpenText($sFilePath, $fVisible = 1, $iStartRow = 1, $vDataType = Default, $vTextQualifier = $xlTextQualifierDoubleQuote, $fConsecutiveDelimiter = False, _
        $fTab = False, $fSemicolon = False, $fComma = False, $fSpace = False, $fOther = False, $sOtherChar = Default)
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0

    With $oExcel
        .Visible = $fVisible
        .Workbooks.OpenText($sFilePath, Default, $iStartRow, $vDataType, $vTextQualifier, $fConsecutiveDelimiter, $fTab, $fSemicolon, $fComma, $fSpace, $fOther, $sOtherChar)
        .ActiveWorkbook.Sheets(1).Select ()
    EndWith
    Return $oExcel
EndFunc   ;==>_ExcelBookOpenText
4000+ lines of code for object event handling (IE.au3)? Kiss up! :)

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

hi guys. i'm back online. i was able to check and learn from the script. big thanks to all of you. the script works great. i have question on writing the data to excel. i want to write the data on the second column of the excel. i wanted to leave the first column because i will be doing a column header on the excel.

how will i do that.

for $ct=2 to ubound($var)-1

i modified the $ct = 2. yes it did write starting on the 2nd column but it starts also to parse on the 2nd line of the text file.

any idea how to do that? i greatly appreciate your help. thanks...

Share this post


Link to post
Share on other sites

thank you big daddy for this excellent txt to excel UDF. works great. but i have same question with aceguy.

i wanted to write the data into excel beginning into the 2nd column of the excel. i will be creating a column header on the first column of the excel.

i have tried to modify this,,,

Global $oExcel = _ExcelBookOpenText($sFilePath, True, 2, $xlDelimited, $xlTextQualifierDoubleQuote, False, False, False, False, False, True, "|")

but this will start parsing from the 2nd line of the text file then pass write into excel.

any idea howto do that?

greatly appreciate your great ideas...thank you.

Share this post


Link to post
Share on other sites

but this will start parsing from the 2nd line of the text file then pass write into excel.

any idea howto do that?

greatly appreciate your great ideas...thank you.

Just insert a column after the txt file is imported.

Share this post


Link to post
Share on other sites

Just insert a column after the txt file is imported.

great idea big daddy. :) but inserting a column will affect my exel format. i mean i have this predefined excel with column headers each for the data to be inserted like Column Header1, Column Header2 and so on. When i make _ExcelRowInsert($oExcel, 1, 1), my header will be adjusted.

i just wanted how will i write the data to excel starting on the 2nd row of the excel so that i will fit exactly on the column heading i defined in the excel...

will that be possible? many thanks to you...

Share this post


Link to post
Share on other sites

great idea big daddy. :) but inserting a column will affect my exel format. i mean i have this predefined excel with column headers each for the data to be inserted like Column Header1, Column Header2 and so on. When i make _ExcelRowInsert($oExcel, 1, 1), my header will be adjusted.

i just wanted how will i write the data to excel starting on the 2nd row of the excel so that i will fit exactly on the column heading i defined in the excel...

will that be possible? many thanks to you...

That's not how the import works. You can't import to an existing document, it creates a new document to import to. However you could easily format the document to your liking after the import. If you post an example document of how it should be formatted, I'll try to help with the code to accomplish it.

Share this post


Link to post
Share on other sites

this is how to open an exisiting excel doc and import to that, TAKE CARE it will overwrite you pre exisitng data.

just change the loc and filename.

#include <Excel.au3>
#include <file.au3>

$file=@MyDocumentsDir&"\Book1.xlsx"
$ss=StringSplit($file,"\")
$last=$ss[0]
ConsoleWrite($ss[$last]&@Lf)
$ss2=StringSplit($ss[$last],".")
ConsoleWrite($ss2[1]&@lf)

if WinExists("Microsoft Excel - "&$ss2[1])=1 then Exit

_ExcelBookOpen($file)
ConsoleWrite("winwait start"&@lf)
WinWaitActive("Microsoft Excel - "&$ss2[1])
consolewrite("winwait end"&@Lf)
$oExcel=_ExcelBookAttach($file) 

dim $var
_FileReadToArray(@DesktopDir&"\log.txt",$var)
for $ct=1 to ubound($var)-1
 $stringsplit=StringSplit($var[$ct],"|")
 for $ct2= 1 to $stringsplit[0]
 _ExcelWriteCell($oExcel,$stringsplit[$ct2],$ct+1,$ct2)
Next
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
Sign in to follow this  
Followers 0