Convert to Excel

[JohnRichard]

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.

[Aceguy]

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

_Excelbooknew()

_excelrowinster()

ect.

[JohnRichard]

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.

[Aceguy]

#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

[JohnRichard]

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

[Aceguy]

#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 May 16, 2009 by Aceguy

[picaxe]

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 May 16, 2009 by picaxe

[big_daddy]

Just an enhanced version of what picaxe posted.

expandcollapse popup

#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

[SmOke_N]

Just an enhanced version of what picaxe posted.

expandcollapse popup

#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!

[JohnRichard]

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

[JohnRichard]

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.

[big_daddy]

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.

[JohnRichard]

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

[big_daddy]

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.

[Aceguy]

just add a +1 into the row like....

_ExcelWriteCell($oExcel,$stringsplit[$ct2],$ct+1,$ct2)

[Aceguy]

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