Populate an excel table from a txt file

[ohgod]

There is a file like this:

"Date";"Indoor Temperature";"Indoor Humidity";"Outdoor Temperature 1";"Outdoor Humidity 1";"Outdoor Temperature 2";"Outdoor Humidity 2";"Outdoor Temperature 3";"Outdoor Humidity 3";"Outdoor Temperature 4";"Outdoor Humidity 4";"Outdoor Temperature 5";"Outdoor Humidity 5"
"16.03.2008 18:05";"24.8";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:10";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:15";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:20";"24.9";"47";"24.9";"45";"---";"---";"---";"---";"---";"---";"---";"---"

...

Will anybody be so kind and paste a simple script that can populate an excel file with a file containing data like this?

Or/And point out some resources for snippets/functions/examples regarding excel handling (I'm searching for an adaptable for 3 hours now )

Thank you very much

[weaponx]

I'm not sure what you need.

Option 1:

Save the file as *.csv and then I open it Excel using File > Open > *.csv > Delimited > Semi-colon

Option 2:

Save the file as *.csv and then do:

#include <File.au3>

_ReplaceStringInFile ("test.csv", ";", ",")

[PsaltyDS]

There is a file like this:

"Date";"Indoor Temperature";"Indoor Humidity";"Outdoor Temperature 1";"Outdoor Humidity 1";"Outdoor Temperature 2";"Outdoor Humidity 2";"Outdoor Temperature 3";"Outdoor Humidity 3";"Outdoor Temperature 4";"Outdoor Humidity 4";"Outdoor Temperature 5";"Outdoor Humidity 5"
"16.03.2008 18:05";"24.8";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:10";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:15";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---"
"16.03.2008 18:20";"24.9";"47";"24.9";"45";"---";"---";"---";"---";"---";"---";"---";"---"

...

Will anybody be so kind and paste a simple script that can populate an excel file with a file containing data like this?

Or/And point out some resources for snippets/functions/examples regarding excel handling (I'm searching for an adaptable for 3 hours now )

Thank you very much

The idea from weaponx is very usable. Convert it to a format that Excel can import directly (.csv).

If you want to code something that works directly with the file and spreadsheet:

1. Read to an array with FileReadToArray().

2. Break each line into another array of row data with StringSplit()

3. Write the row's data to the Excel spreadsheet with _ExcelWriteArray(). This last function is part of the _ExcelCOM_UDF.au3 UDF by Locodarwin.

Writing code for you is not what happens here (mostly), but you'll find lots of help learning to do it yourself!

[ohgod]

stringsplit works but I cannot make _ExcelWriteArray work.

it doesnt do a thing

#include<ExcelCOM_UDF.au3>
#include <Array.au3>
#include <file.au3>
global $aRecords
global $istartrow
global $istartcolumn
global $sordarab
global $oExcel


$_oExcel =  _ExcelBookNew(1)
If Not _FileReadToArray("trimmed.csv",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf
For $x = 1 to 60
 ConsoleWrite("Record:" & $x & " " & $aRecords[$x] & @CRLF)
 ; MsgBox(0,"Xesfor","Record:" & $x & " " & $aRecords[$x])
 For $y = 1 to 13
    $sordarab= StringSplit($aRecords[$x], ",")
    ConsoleWrite("SORDARAB:" & $y & " " & $sordarab[$y] & @CRLF)
;MsgBox(0,"ypsilon","Record:" & $y & " " & $sordarab[$y])
    Next
_ExcelWriteArray($oExcel, $y, $x, $sordarab,0,0)
Next

[PsaltyDS]

stringsplit works but I cannot make _ExcelWriteArray work.

it doesnt do a thing

#include<ExcelCOM_UDF.au3>
#include <Array.au3>
#include <file.au3>
global $aRecords
global $istartrow
global $istartcolumn
global $sordarab
global $oExcel


$_oExcel =  _ExcelBookNew(1)
If Not _FileReadToArray("trimmed.csv",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf
For $x = 1 to 60
 ConsoleWrite("Record:" & $x & " " & $aRecords[$x] & @CRLF)
; MsgBox(0,"Xesfor","Record:" & $x & " " & $aRecords[$x])
 For $y = 1 to 13
    $sordarab= StringSplit($aRecords[$x], ",")
    ConsoleWrite("SORDARAB:" & $y & " " & $sordarab[$y] & @CRLF)
;MsgBox(0,"ypsilon","Record:" & $y & " " & $sordarab[$y])
    Next
_ExcelWriteArray($oExcel, $y, $x, $sordarab,0,0)
Next

Your excel object has a stray "_" in it. Try it like this:

#include<ExcelCOM_UDF.au3>
#include <file.au3>

Global $aRecords, $istartrow, $istartcolumn, $sordarab, $oExcel

; Read file to array
If Not _FileReadToArray("trimmed.csv", $aRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf

; Create instance of Excel 
$oExcel = _ExcelBookNew()

; Add each line of the file to a row of the spreadsheet
For $x = 1 To 60
    ; Split the line at the commas
    $sordarab = StringSplit($aRecords[$x], ",")
    ; Write the resulting array to the spreadsheet
    _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1)
Next

[junkew]

I do not understand why you would write this in AutoIt as its just a oneliner in an excel macro

See below for AutoIt code (data.txt should be in %temp% folder, tested with excel 2003)

$oExcel = ObjCreate("Excel.Application")                  ; Create an Excel Object
$oExcel.Visible = 1    ; Let Excel show itself

;~VBA code 
;~Workbooks.OpenText Filename:= _
;~       "%temp%\data.txt", Origin:= _
;~       xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
;~       , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:= _
;~       False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
;~       , Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
;~       Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True

$oExcel.Workbooks.OpenText(@tempdir & "\data.txt", 1, 1, 1, 2, False, False, True, False, False, False)

sleep(4000)                                            ;See the results for 4 seconds
$oExcel.ActiveWorkBook.Saved = 1                          ; Simulate a save of the Workbook
$oExcel.Quit                                              ; Quit Excel

[ohgod]

Your excel object has a stray "_" in it. Try it like this:

#include<ExcelCOM_UDF.au3>
#include <file.au3>

Global $aRecords, $istartrow, $istartcolumn, $sordarab, $oExcel

; Read file to array
If Not _FileReadToArray("trimmed.csv", $aRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf

; Create instance of Excel 
$oExcel = _ExcelBookNew()

; Add each line of the file to a row of the spreadsheet
For $x = 1 To 60
    ; Split the line at the commas
    $sordarab = StringSplit($aRecords[$x], ",")
    ; Write the resulting array to the spreadsheet
    _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1)
Next

A HUGE thank you for this and all the help!

altought it's really slow

[PsaltyDS]

A HUGE thank you for this and all the help!

altought it's really slow

Slow...?

It only processes 60 lines (your number). How long does it take when you run it?

Update: I created a txt file from you original post with about 100 lines in it and every fifth line blank. Then I ran this against it:

expandcollapse popup

#include<ExcelCOM_UDF.au3>
#include <file.au3>
#include <array.au3>

Global $aRecords, $sordarab, $oExcel, $iReadTime, $iDeleteTime, $iCreateTime
Global $iWriteTime, $iTotalTime, $sFile = @ScriptDir & "\test.txt"

; Read file to array
$iReadTime = TimerInit()
If Not _FileReadToArray($sFile, $aRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf
$iReadTime = TimerDiff($iReadTime)

; Remove Blank lines
$iDeleteTime = TimerInit()
For $x = $aRecords[0] To 1 Step -1
    If StringStripWS($aRecords[$x], 8) = "" Then _ArrayDelete($aRecords, $x)
Next
$iDeleteTime = TimerDiff($iDeleteTime)

; Create instance of Excel
$iCreateTime = TimerInit()
$oExcel = _ExcelBookNew()
$iCreateTime = TimerDiff($iCreateTime)

; Add each line of the file to a row of the spreadsheet
$iWriteTime = TimerInit()
For $x = 1 To 60
    ; Split the line at the commas
    $sordarab = StringSplit($aRecords[$x], ";")
    ; Write the resulting array to the spreadsheet
    _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1)
Next
$iWriteTime = TimerDiff($iWriteTime)

; Show times
Global $sMsg = "All Times in ms:" & @CRLF & _
        "Read to array: " & Round($iReadTime, 3) & @CRLF & _
        "Delete blanks: " & Round($iDeleteTime, 3) & @CRLF & _
        "Create Excel: " & Round($iCreateTime, 3) & @CRLF & _
        "Write rows to excel: " & Round($iWriteTime, 3) & @CRLF & _
        "Total time: " & Round($iReadTime + $iDeleteTime + $iCreateTime + $iWriteTime, 3)
ConsoleWrite($sMsg & @LF)

Results:

>Running:(3.2.10.0):C:\Program Files\AutoIt3\autoit3.exe "C:\temp\Test\Test1.au3"   
All Times in ms:
Read to array: 2.39
Delete blanks: 44.349
Create Excel: 181.923
Write rows to excel: 1762.052
Total time: 1990.714
+>11:59:29 AutoIT3.exe ended.rc:0

Now, "slow" is a relative term, but approx. 1.9sec to do the whole function doesn't seem out of line.

Edited March 27, 2008 by PsaltyDS

[junkew]

See my previous answer. If 1-2 seconds is not fast I would like to know what your definition of slow is.

$oExcel.Workbooks.OpenText(@tempdir & "\data.txt", 1, 1, 1, 2, False, False, True, False, False, False)

It takes just a second to read and convert it on my 1.4 GHZ machine.

[ohgod]

Yesterday I run into the mentioned $oExcel.Workbooks.OpenText so hopefully it can be tried today.

the file to be imported into excel contains data from at least 5 colums, and a new row for every five minutes. (it's about weather monitoring) so that is gonna be a lot of records I'm sorry if I didn't make that clear.

Excel is only needed because of it's graphing capability, I want to have the datas on a sheet and some pre-made data dependant diagrams/ on a different one, then email the one i need.

maybe i have too big dreams

hopefully, the excel COM udf, a mail sender that can deal with multiply attachments, and you guys will help me to achive it