Excel Import and Tabs Question

[water]

_Excel_BookOpen needs the full path. So please use:

Local $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\" & $aCsvFilesToConvert[$i], Default, Default, True)

[Chimaera]

Which is similar to what i did with file list to array on my edit

Is it better to do yours

Local $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\" & $aCsvFilesToConvert[$i], Default, Default, True)

or this?

Global $aCsvFilesToConvert = _FileListToArray( @ScriptDir,"*.csv", 1, True)

[water]

Doesn't matter how you provide the full path.

[Chimaera]

Ok thx for the help and time you have given

And here is the code for those that want it

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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


; Create application object
Global $oAppl = _Excel_Open()

;~ Global $aCsvFilesToConvert = _FileListToArrayRec( @ScriptDir,"*.csv", 1, Default, Default, 0)
Global $aCsvFilesToConvert = _FileListToArray( @ScriptDir,"*.csv", 1)
        If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "FLTArray " & @error & " | " & @extended & @CRLF)
    If Not IsArray($aCsvFilesToConvert) Then
        MsgBox($MB_ICONINFORMATION, "Array Error", "      No Files Available To Process", 2)
        Exit
    EndIf

Example1($oAppl)

Exit

Func Example1($oAppl)
    _ArrayDisplay($aCsvFilesToConvert, "Csv Files To Convert")
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 1)

    If IsArray($aCsvFilesToConvert) Then
        For $i = 1 To $aCsvFilesToConvert[0]
            Local $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\" & $aCsvFilesToConvert[$i], Default, Default, True) ; Opens the book csv file ready to copy
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "BookOpen " & @error & " | " & @extended & @CRLF)
            Local $AddSheet = _Excel_SheetAdd($oWorkbookNew, Default, False, Default, $aCsvFilesToConvert[$i]) ; Adds a blank sheet to the second workbook ready for the copy
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "SheetAdd " & @error & " | " & @extended & @CRLF)
            Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1")) ; Copy data from Book1 to BookNew
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "RangeCopyPaste " & @error & " | " & @extended & @CRLF)
            Local $CloseTempBook = _Excel_BookClose($oWorkbook1, False) ; Close the csv book ready for the next cycle
        Next
    EndIf
    _Excel_SheetDelete($oWorkbookNew, 1) ; Removes empty 1st worksheet
EndFunc   ;==>Example1

A test run of 600 pages ish in about 3.5 mins

Edited January 18, 2014 by Chimaera

[water]

Maybe even faster:

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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

; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2)
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONINFORMATION, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf

Example1($oAppl)

Exit


Func Example1($oAppl)
    Local $oWorkbook1, $i
    _ArrayDisplay($aCsvFilesToConvert, "Csv Files To Convert")
    Local $oWorkbookNew = _Excel_BookNew($oAppl, $aCsvFilesToConvert[0])
    For $i = 1 To $aCsvFilesToConvert[0]
        $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$i], True)
        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($i).Range("A1"))
        _Excel_BookClose($oWorkbook1, False)
    Next
EndFunc   ;==>Example1

[Chimaera]

Im curious is there a way to add in missing blank pages after its finished as my files have name gaps as certain pages dont contain the data im working with.

But it would be nice to fill in the missing ones with a blank page

[water]

You mean something like this:

Files:

30.csv

33.csv

Sheet1 = 30.csv, Sheet2 = empty, Sheet3 = empty, Sheet4=33.csv?

[Chimaera]

yep

exactly that except the blank sheet will be called Page 31 and Page 32

I cant seem to get your example to add the sheet names i altered mine like this

Local $AddSheet = _Excel_SheetAdd($oWorkbookNew, Default, False, Default, "Page " & StringTrimRight($aCsvFilesToConvert[$i],4))

Yours is about 2.5 seconds quicker on a 20 file test though

Edited January 18, 2014 by Chimaera

[water]

Is the number of the first file always 1 or can it be - as in your example - another number?

[Chimaera]

From the test files i have to work from they start like this

4,5,6,7,9,10,12,13,14,16,17,20, etc

 and im trying to get it to

Page 1,Page 2,Page 3,Page 4,Page 5,Page 6,Page 7,Page 8,Page 9,Page 10,Page 11,Page 12, etc

Red ones are the blank fillin pages

Would it be easier to read the folder for the missing ones

Obviously im looking to add "Page " and remove ". csv" from each file to just give me the page number which are the file numbers

Ive edited your example to this so it gives me the file numbers at the array which i can use later but the Book Open uses the path

the only bit i cant work out is how to add the Page in front of the filenames

Ive tried

 

        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, "Page " & StringTrimRight($oWorkbookNew.Sheets($i),4).Range("A1"))

        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets("Page " & StringTrimRight($aCsvFilesToConvert[$i],4)).Range("A1"))

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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

; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 0)
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONINFORMATION, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf

Example1($oAppl)

Exit


Func Example1($oAppl)
    Local $oWorkbook1, $i
    _ArrayDisplay($aCsvFilesToConvert, "Csv Files To Convert")
    Local $oWorkbookNew = _Excel_BookNew($oAppl, $aCsvFilesToConvert[0])
    For $i = 1 To $aCsvFilesToConvert[0]
        $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\" & $aCsvFilesToConvert[$i], True)
        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($i).Range("A1"))
        _Excel_BookClose($oWorkbook1, False)
    Next
EndFunc   ;==>Example1

[water]

This should do what you want:

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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

; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2)
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONINFORMATION, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf

Example1($oAppl)

Exit

Func Example1($oAppl)
    Local $oWorkbook1, $iArray, $iSheet, $iSheets
    Local $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
    _PathSplit( $aCsvFilesToConvert[$aCsvFilesToConvert[0]], $sDrive, $sDir, $sFilename, $sExtension)
    $iSheets = Int($sFilename)
    Local $oWorkbookNew = _Excel_BookNew($oAppl, $iSheets)
    For $iArray = 1 To $aCsvFilesToConvert[0]
        $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$iArray], True)
        _PathSplit($aCsvFilesToConvert[$iArray], $sDrive, $sDir, $sFilename, $sExtension)
        $iSheet = Int($sFilename)
        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($iSheet).Range("A1"))
        _Excel_BookClose($oWorkbook1, False)
    Next
EndFunc   ;==>Example1

[Chimaera]

Yep thx for that , that adds all missing numbers that are missing

[water]

[water]

I've added comments and some code to number the sheets "Page n".

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

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

; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2)
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONINFORMATION, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf

Example1($oAppl)

Exit

Func Example1($oAppl)
    Local $oWorkbook1, $iArray, $iSheet, $iSheets
    Local $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
    ; Get the filename of the last file = highest file number = number of sheets in the target workbook
    _PathSplit( $aCsvFilesToConvert[$aCsvFilesToConvert[0]], $sDrive, $sDir, $sFilename, $sExtension)
    $iSheets = Int($sFilename)
    ; Create a new workbook with the number of sheets calculated above
    Local $oWorkbookNew = _Excel_BookNew($oAppl, $iSheets)
    For $iArray = 1 To $aCsvFilesToConvert[0]
        ; Open the CSV file in a new workbook
        $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$iArray], True)
        ; Get the filename of the CSV file = number of the worksheet to write the data
        _PathSplit($aCsvFilesToConvert[$iArray], $sDrive, $sDir, $sFilename, $sExtension)
        $iSheet = Int($sFilename)
        ; Copy the sheet from the source workbook to the target workbook
        _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($iSheet).Range("A1"))
        ; Close the source workbook (CSV file)
        _Excel_BookClose($oWorkbook1, False)
    Next
    ; Set the name of all sheets
    For $i = 1 To $iSheets
        $oWorkbookNew.Sheets($i).Name = "Page " & $i
    Next
EndFunc   ;==>Example1

[Chimaera]

Thx for the comments i will go away and digest

Many thanks again

Its some fine work you have done on the rewrite, i never realised what was possible with AutoIt and excel

[water]

I'm glad that some people solve real problems with the new UDF. It looks like it will replace the current Excel UDF in the next beta.

[Chimaera]

Just been doing some testing and with 587 csv's it loads them all into the array and i checked the array to be sure

And starts running but..

It stops at csv number 99 in the excel sheet and the script finishes.. any ideas? and both 99 and 100 contain data

It will be good if its included

Ill be happy when we can save as xlsx for sure

Edited January 18, 2014 by Chimaera

[water]

Which Excel version do you run?

[Chimaera]

2013

[water]

There seems to be a limitation of 255 worksheets you can create with the method _Excel_BookNew uses.

Try this:

Local $oWorkbookNew = _Excel_BookNew($oAppl, 1) ; <== Replace this line
If $iSheets > 1 Then _Excel_SheetAdd($oWorkbookNew, 1, False, $iSheets - 1) ; <== Add this line

Another issue to consider:

The numbers of your files need to padded with 0.

If you have file files "4.csv" and "100.csv" the script will crash. The files need to be named "004.csv" and "100.csv".