Jump to content

Excel Array to text file with separated data

Recommended Posts

Hello experts,


I am new to Autoit and having a bad time trying to read an excel column and getting each cell value separated by , and '

Ex: 'value1','value2','value3'

This is what i am doing:

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


Local $sFilePath = @ScriptDir & "\bloqueios_excluidos.xlsx"
Local $oExcel = _Excel_Open()
Local $sColuna
Local $aResult


; Script Start

If Not FileExists($sFilePath) Then
    MsgBox(16, '', 'Excel não encontrado!')
Local $oWorkbook = _Excel_BookOpen($oExcel,$sFilePath)

Local $sColuna = InputBox("Gerador de scripts", "Insira o primeiro e o ultimo campo separados por dois ponto EX: F2:F8 ",""," m")

"'" & $aResult & "'," = _Excel_RangeRead($oWorkbook, 1, $sColuna , 1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$TextFile = "TextFile.txt"
FileOpen($TextFile, 2)

    _FileWriteFromArray($TextFile, $aResult)
$oExcel.activeworkbook.saved = 1
$oExcel = 0

Run("NotePad.exe " & $TextFile,"", @SW_MAXIMIZE)

Anyone how should I do this?

Thank you!

Link to post
Share on other sites
26 minutes ago, Kabuzi said:


"'" & $aResult & "'," = _Excel_RangeRead($oWorkbook, 1, $sColuna , 1)

That contains multiple problems. First, you cannot assign something to a concatenated string. Second: as described in the helpfile, the _Excel_Rangeread returns an array for multiple cells and a string for just one cell.

Consider this example:

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

Local $sFilePath = "c:\tmp\Book1.xlsx"
Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath)


Func dothing($query)
    $aResult = _Excel_RangeRead($oWorkbook, Default, $query, 1)
    ; Note: $aResult could be 0 (indicating an error), or a string, or an array.
    if $aResult = 0 then ; or alternatively:    if @error then
        MsgBox(0, 0, "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ElseIf IsArray($aResult) Then
        MsgBox(0, 0, "Single value retrieved: " & $aResult)
EndFunc   ;==>dothing

I populated my c:\tmp\Book1.xlsx as follows:


Edited by SadBunny
Cleaner code

Roses are FF0000, violets are 0000FF... All my base are belong to you.

Link to post
Share on other sites

Read the whole column into an array, process the array and create the CSV string.

My UDFs and Tutorials:


Active Directory (NEW 2021-06-05 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version - Download
Outlook Tools (2019-07-22 - Version - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version - Download - General Help & Support - Example Scripts
PowerPoint (2021-07-20 - Version - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version - Download - General Help & Support - Wiki

ADO - Wiki, WebDriver - Wiki


Link to post
Share on other sites

Ty guys, I finally finished my code. I will leave it here in case someone need something like this in the future...

What I was trying to do is read a column from a .xlsx and then turn into SQL queries.

I followed what Water said and got this:


$i = 0
    While $i < $Linhas
        If IsNumber($aExcluir[$i]) Then
            $sScript = $sScript&"'" & $aExcluir[$i] & "',"
        $i = $i+1

- All the the information I got with excel _Excel_RangeRead is in $aExcluir.

- $Linhas is the number of positions in $aExcluir.

- This code is concatenating each value in the string $sScript .

At the end I put everything in a .sql file using FileWrite("Script.sql",$sScript)


Edited by Kabuzi
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...