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!')
    Exit
EndIf
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)
;_ArrayDisplay($aResult)

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

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

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

Anyone how should I do this?

Thank you!

Link to comment
Share on other sites

26 minutes ago, Kabuzi said:

expandcollapsepopup

"'" & $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)

dothing("A1")
dothing("A1:B1")
dothing("¡²³")

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
        _ArrayDisplay($aResult)
    Else
        MsgBox(0, 0, "Single value retrieved: " & $aResult)
    EndIf
EndFunc   ;==>dothing

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

BOLpGXs.png

Edited by SadBunny
Cleaner code

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

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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] & "',"
        EndIf
        $i = $i+1
    WEnd

- 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 comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...