FileReadLine to Array to Excel

[Skeletor 60]

Hi All,

I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?

One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.

So its imperative that the $value1, $value2, etc variables be used. 

Code below:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)


For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1")
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1")

Next

 

Edited October 3, 2018 by Skeletor

[FrancescoDiMuro 181]

@Skeletor
What about _FileReadToArray() and _Excel_RangeWrite()?

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <ExcelConstants.au3>
#include <File.au3>

Global $strFilePath = @ScriptDir & "\SampleFile.txt", _
       $arrFileContent, _
       $objExcel, _
       $objWorkbook, _
       $strExcelFile = @ScriptDir & "\SampleWorkbook.xls"


_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
If @error Then
    ConsoleWrite("Error with _FileReadToArray(). Error: " & @error & @CRLF)
Else
    ; _ArrayDisplay($arrFileContent)
    $objExcel = _Excel_Open(False)
    If @error Then
        ConsoleWrite("Error while creating Excel object. Error: " & @error & @CRLF)
    Else
        $objWorkbook = _Excel_BookNew($objExcel)
        If @error Then
            ConsoleWrite("Error while creating the Workbook. Error: " & @error & @CRLF)
        Else
            _Excel_RangeWrite($objWorkbook, $objWorkbook.ActiveSheet, $arrFileContent)
            If @error Then
                ConsoleWrite("Error while writing the content of $arrFileContent in the Workbook. Error: " & @error & @CRLF)
            Else
                If FileExists($strExcelFile) Then FileDelete($strExcelFile)
                _Excel_BookSaveAs($objWorkbook, $strExcelFile, $xlExcel8)
                If @error Then
                    ConsoleWrite("Error while saving the Workbook. Error: " & @error & @CRLF)
                Else
                    ConsoleWrite("The content of the $arrFileContent array has been written in the Excel file." & @CRLF)
                    _Excel_BookClose($objWorkbook)
                    _Excel_Close($objExcel)
                EndIf
            EndIf
        EndIf
    EndIf
EndIf

Cheers

SampleFile.txt

Edited October 3, 2018 by FrancescoDiMuro

[Skeletor 60]

@FrancescoDiMuro 

Cool, but that's a straight dump from text file to Excel. 
I wanted to pick out only a few values from the text file and insert them into the Excel sheet... 

So, FileReadLine does this prefectly, reads one line at a time from the text file and inserts it into the Excel SpreadSheet.. but, it takes a long time. 
 

[FrancescoDiMuro 181]

@Skeletor
You can always modify your array as much as you want.
Keep in mind that with _FileReadToArray() you fill your array with the content of the file; and then, you can do whatever you want, since you are working with the array...
You can create another array which contains only what you want ( or use directly the rows/columns you want to store in the second file ).
So, it's your choice
 

[Skeletor 60]

This is where I am confused.. I'm trying to figure out this whole array saga... 

I'm not much of a fan about these.. that's why I use FileReadLines and stuff like that... 
so if I use:
 

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Will that work?
Especially if it can trickle down the rows.. like A1, A2, A3, etc....?

[FrancescoDiMuro 181]

@Skeletor
It is what _Excel_RangeWrite() does...
But all depends on how your array is composed...
If you have a 1-Dimensional array, using _Excel_RangeWrite() you are going to set all the values in the Workbook starting from "A1" cell ( leaving the Range parameter default ); so, in this case, if you have a 1-Dimensional array and you would like to set values in B, C, D... columns, you have to format you array in order to have a 2-Dimensional array ( rows and columns ).
So, the line 

5 minutes ago, Skeletor said:

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

it's correct, but you need to know how your $Array is formatted
 

[Skeletor 60]

Thanks FrancescoDiMuro, so I need to just work on my array... so do I get rid of the For..Loop then? 
Or will this loop through the file, read each line, then the StringSplit break it up like how I want and then spit it out like so....?

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

$Array = ($value1, $value2, $value3, $value4)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

Is this correct? Or how would I format my Array... ?

[FrancescoDiMuro 181]

24 minutes ago, Skeletor said:

$Array = ($value1, $value2, $value3, $value4)

This is not correct.
To set correctly your values in your array, you should do something like this:

#include <Array.au3>

Global $Array[1][4]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    
    _ArrayAdd($Array, $value1 & "|" & $value2 & "|" & $value3 & "|" & $value4 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

But I am going to tell you again that you are doing unecessary steps, which will take additional time to do what they do

Edited October 3, 2018 by FrancescoDiMuro

[Subz 468]

As per FrancescoDiMuro _FileReadToArray example, just add the following afterwards:

This just deletes all the columns except for Column 0 to 3, then just use _Excel_RangeWrite to write it directly into Excel.

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

For $i = UBound($arrFileContent) - 1 To 4 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next

 

[Skeletor 60]

Thanks Subz, however I'm selecting only a few values form the files.. 

Example: 
 

aaa,sss,ddd,fff,ggg,hhh

When I stringSplit I take get this:
 

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
Next

I then want to take $value1 and $value3 out from the file and insert it into Excel... 
 

[Skeletor 60]

Using the FileArray will only dump the entire file as is into Excel... Yes splits it into columns, but thats not what I want.. i want to use select my data...

[FrancescoDiMuro 181]

@Skeletor

6 minutes ago, Skeletor said:

I then want to take $value1 and $value3 out from the file and insert it into Excel... 

#include <Array.au3>

Global $Array[1][2]

$FileRead1 = FileReadLine("C:\temp\sample.txt",1) ;File has a url in

For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)    
    $value1 = $input[1]
    $value3 = $input[3]
    
    _ArrayAdd($Array, $value1 & "|" & $value3 & @CRLF)
Next

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A1")

If you don't want to use all the samples we already gave to you.

Edited October 3, 2018 by FrancescoDiMuro

[Subz 468]

If you only wanted Input1 and Input3 then just delete the second column as well, or maybe I'm missing something?

_FileReadToArray($strFilePath, $arrFileContent, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
For $i = UBound($arrFileContent) - 1 To 3 Step - 1
    _ArrayColDelete($arrFileContent, $i)
Next
_ArrayColDelete($arrFileContent, 1)
_ArrayDisplay($arrFileContent)

 

[Skeletor 60]

Thanks Subz, but my examples are only a tiny bit of the large code I have.. 

So, basically, the $value1 and $value3 will not go in order... 

Let's say I have this:

$FileRead1 = FileReadLine("C:\temp\sample.txt",1)
Local $countlines = _FileCountLines($FileRead1)
Global $Array[$countlines]
For $count = 1 To _FileCountLines($FileRead1) Step 1
    $string = FileReadLine($FileRead1, $count)
    $input = StringSplit($string, ",", 1)
    $value1 = $input[1]
    $value2 = $input[2]
    $value3 = $input[3]
    $value4 = $input[4]
    $value5 = $input[5]
    $value6 = $input[6]
    $value7 = $input[7]
    $value8 = $input[8]
    $value9 = $input[9]
    _ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")
Next

_ArrayDisplay($Array)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Array, "A2")

Now, I turned the Array into a 1D.. I get results but each one of the $values are in rows, and not like how I specified them... 
If in 2D I get no results.

[FrancescoDiMuro 181]

@Skeletor
This

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3, 1, "|")

Should be

_ArrayAdd($Array, $value9 & "|" & $value2 & "|" & " " & "|" & $value5 & "|" & $value7 & "|" & $value5 & "|" & $value3 & @CRLF, 1, "|")

 

[caramen 54]

Teacher @FrancescoDiMuro deserve the mvp statut xD

[Subz 468]

Two other methods which would be much faster than FileReadLine

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")
    If @error Then Exit

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 8, 8), "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 1, 1), "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 6, 6), "E1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 4, 4), "F1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, _ArrayExtract($g_aFileData, -1, -1, 2, 2), "G1")

Second example:

#include <Array.au3>
#include <ArrayWorkshop.au3> ;~ https://www.autoitscript.com/forum/topic/180467-arrayworkshop/
#include <Excel.au3>
#include <File.au3>

Global $g_sFilePath = @ScriptDir & "\SampleFile.txt"
Global $g_aFileData
_FileReadToArray($g_sFilePath, $g_aFileData, $FRTA_NOCOUNT + $FRTA_ENTIRESPLIT, ",")

;~ Create the Order of Columns to write into Excel
;~ Note Columns are 0 index based.
Global $g_aColumnOrder[] = [8, 1, "", 4, 6, 4, 3, ""]
Global $g_aColumnData = _FileExtract($g_aFileData, $g_aColumnOrder)
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $g_aColumnData)

Func _FileExtract($_aFileData, $_aColumnOrder)
    If $_aColumnOrder[0] = "" Then
        Local $aColumnData[UBound($_aFileData)][1]
    Else
        Local $aColumnData = _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[0], $_aColumnOrder[0])
    EndIf
    For $i = 1 To UBound($_aColumnOrder) - 1
        If $_aColumnOrder[$i] = "" Then
            _ArrayColInsert($aColumnData, $i)
        Else
            _ArrayAttach($aColumnData, _ArrayExtract($_aFileData, -1, -1, $_aColumnOrder[$i], $_aColumnOrder[$i]), 2)
        EndIf
    Next
    Return $aColumnData
EndFunc

 

[Skeletor 60]

@Subz

Thanks, I'll look into those two examples.