Jump to content
Skeletor

FileReadLine to Array to Excel

Recommended Posts

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 by Skeletor

Share this post


Link to post
Share on other sites

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

#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 by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 :)
 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@Skeletor
This

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

Should be

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

 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Teacher @FrancescoDiMuro deserve the mvp statut xD


My video tutorials : HERE ( In construction )  || My Discord : https://discord.gg/S9AnwHw

How to Ask Help ||  UIAutomation From Junkew || WebDriver From Danp2 || And Water's UDFs in the Quote

Spoiler

 Water's UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 

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

  • Similar Content

    • By Blitzkid
      Hello, i want to search several directories for files with the largest numbers behind them (Like "video123") . They dont have a datatype. But there are also files with longer names and datatypes in these folders (Like "video778.mp4"). Is it possible to filter the _FileListToArray Syntax from
      to smth. like
       
      Here is my Code
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <array.au3> #include <File.au3> $filedir = @ScriptDir & "\" _checkfile() Func _checkfile() ConsoleWrite("______________________" & @CRLF) Local $arr[3] = ["music", "picture", "video"] For $i = 0 To UBound($arr) - 1 Local $arrayfiles = _FileListToArray($filedir & $arr[$i], $arr[$i] & "*", 1) If @error = 1 Then ConsoleWrite($arr[$i] & "Error 1") EndIf If @error = 4 Then ConsoleWrite($arr[$i] & "Error 2") ;Exit EndIf $arrayfilter = _ArrayMax($arrayfiles, 0, 1) Global $stringfiles = StringReplace($arrayfilter, $arr[$i], "") ConsoleWrite($arrayfilter & @CRLF) Next EndFunc ;==>_checkfile  
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
    • By jmp
      Script running good but error in line 7.
      When i run this script :
      #include <IE.au3> #include <Array.au3> $oIE = _IEAttach ("Shop") $oTable = _IETableGetCollection ($oIE, 1) $aTableData = _IETableWriteToArray ($oTable) For $inumber = 1 To UBound($aTableData) -1 $table = $aTableData[4][$inumber] MsgBox(0, "", $table) Next I got Error: array variable has incorrect number of subscripts or subscript dimension range exceeded
×
×
  • Create New...