Sign in to follow this  
Followers 0
Chimaera

Excel Import and Tabs Question

91 posts in this topic

I have a folder with a load of csv's in it, anywhere from a couple of hundred to 10,000 sometimes

I need to add the data within them into a excel sheet one csv per tab, each csv could be 1 line to several hundred.

Now the ecxel sheet has to have a header and footer with supplier details and stuff like that in it

Is it better to

Make the header at runtime with data from another excel sheet i.e. suppliers etc

Or should i try and make it using Autoit so it generates it from an ini?

Or can i use a template pre made and fill that maybe?

Obviously the data from the csv must match with the supplier

Been asked to do this so i thought id best ask before i dive in on the wrong path

Any ideas?

Share this post


Link to post
Share on other sites



You could always put your list of suppliers on a hidden tab in the workbook, I have done this before. Then, using the list of suppliers you could create the necessary tabs. Something like this:

#include <Excel.au3>

    $oExcel = _ExcelBookOpen(@DesktopDir & "\Test1.xls")
    $oExcel.ActiveWorkbook.Sheets("Suppliers").Visible = $xlSheetVisible
    $oSheet = _ExcelSheetActivate($oExcel, "Suppliers")
    $aSuppliers = _ExcelReadSheetToArray($oExcel, 2, 1)
    $oExcel.ActiveWorkbook.Sheets("Suppliers").Visible = $xlSheetHidden

    For $i = 1 To $aSuppliers[0][0]
        _ExcelSheetAddNew($oExcel, $aSuppliers[$i][1])
    Next

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

You could use the rewrite of the Excel UDF to import the CSV files.

  • Open two workbooks: One to import the CSV files (book1), another where you have one sheet for each imported CSV (book2)
  • Open a CSV file to book1 using _Excel_Open  or even _Excel_OpenText
  • Create a new worksheet in book2, write the header lines and then copy the content of  book1 to book2 using _Excel_RangeCopyPaste

Should be much faster as with the current UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

You could always put your list of suppliers on a hidden tab in the workbook, I have done this before. Then, using the list of suppliers you could create the necessary tabs. Something like this:

#include <Excel.au3>

    $oExcel = _ExcelBookOpen(@DesktopDir & "\Test1.xls")
    $oExcel.ActiveWorkbook.Sheets("Suppliers").Visible = $xlSheetVisible
    $oSheet = _ExcelSheetActivate($oExcel, "Suppliers")
    $aSuppliers = _ExcelReadSheetToArray($oExcel, 2, 1)
    $oExcel.ActiveWorkbook.Sheets("Suppliers").Visible = $xlSheetHidden

    For $i = 1 To $aSuppliers[0][0]
        _ExcelSheetAddNew($oExcel, $aSuppliers[$i][1])
    Next

Ok thx for that ill have a look

 

You could use the rewrite of the Excel UDF to import the CSV files.

  • Open two workbooks: One to import the CSV files (book1), another where you have one sheet for each imported CSV (book2)
  • Open a CSV file to book1 using _Excel_Open  or even _Excel_OpenText
  • Create a new worksheet in book2, write the header lines and then copy the content of  book1 to book2 using _Excel_RangeCopyPaste

Should be much faster as with the current UDF.

 

Can i not just open the csv's straight into an excel sheet? all in one sheet so to speak?

Something else i thought of was excel worksheet limitation is the machine itself so i cant guarentee what mach it will be run on , would i be better only opening a 1000 per book just to be safe?

Share this post


Link to post
Share on other sites

Unfortunately: No.

Excel opens each CSV file as a workbook.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Correct


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hmm not quite working how i thought

Local $sWorkbook = @ScriptDir & "\30.csv" ; is the files just using one at the moment


Example1($oAppl)

Exit


Func Example1($oAppl)

    Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 2)

    Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbookNew.ActiveSheet, False)

EndFunc   ;==>Example1

It is opening both excel sheets but not copying the data from one to the other, the data is there in the first sheet

Share this post


Link to post
Share on other sites

Should be:

Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Aah that helped thx

moved onto here now

Added the array of files and filling out the function but im still throwing an error when the array is used?

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


;~ Local $sWorkbook = @ScriptDir & "\30.csv" ; is the files just using one at the moment


Example1($oAppl)

Exit


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

    If IsArray($aCsvFilesToConvert) Then
        For $i = 0 To UBound($aCsvFilesToConvert) -1
            Local $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert, Default, Default, True)
            Local $AddSheet = _Excel_SheetAdd($oWorkbookNew, Default, False, Default, $aCsvFilesToConvert)
            Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))
        Next
    EndIf
EndFunc   ;==>Example1

Error as follows

 

"E:CodeProjects#FilesExcel TestExcel Test.au3" (37) : ==> Variable must be of type "Object".:

Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))
Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1^ ERROR

 

does it not work passed from an array or am i doing something stupid?

Hmm looks like it fails because it is not opening the active sheet of the first csv.... i think...

Edited by Chimaera

Share this post


Link to post
Share on other sites

You can't pass an array, you have to pass a single file:

Local $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$i], Default, Default, True)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Yeah i had just added that myself as i realised but it still returns a blank excell sheet with 2 sheets on it and i never see any other workbooks

and the error is the same

i copied your line in and still the same

Edited by Chimaera

Share this post


Link to post
Share on other sites

So what's the value of @error after _Excel_BookOpen and_Excel_SheetAdd?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Book open 2/0

Sheet add 0/0

Ive just been rechecking the _FileListToArrayRec and added full paths but still no luck

This is the current

 
#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()
;~ 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, Default)
    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 = 0 To UBound($aCsvFilesToConvert) -1
            Local $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$i], Default, Default, True)
            ConsoleWrite( @error & " | " & @extended & @CRLF)
            Local $AddSheet = _Excel_SheetAdd($oWorkbookNew, Default, False, Default, $aCsvFilesToConvert)
            ConsoleWrite( @error & " | " & @extended & @CRLF)
            Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))
            ConsoleWrite( @error & " | " & @extended & @CRLF)
            Local $CloseTempBook = _Excel_BookClose($oWorkbook1, False)
        Next
    EndIf
EndFunc   ;==>Example1
Edited by Chimaera

Share this post


Link to post
Share on other sites

@error = 2 stands for: Specified $sFilePatch does not exist

Can you post an example file path?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

I just tried a simpler one 

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

This is the main one

Global $aCsvFilesToConvert = _FileListToArrayRec( @ScriptDir,"*.csv", 1, Default, Default, Default)

The arraydisplay pops up fine i know it exists and has 5 files in it

Weird how its not finding the path, im only using @scriptdir after all

: ==> Variable must be of type "Object".:
Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))
Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1^ ERROR

Does .ActiveSheet not like array data?

Edited by Chimaera

Share this post


Link to post
Share on other sites

Can you please post the content of the returned array? Just copy the content of _ArrayDisplay.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

 

Row|Col 0

[0]|5
[1]|30.csv
[2]|31.csv
[3]|32.csv
[4]|33.csv
[5]|34.csv
 
And my error checking
FLTArray 0 | 0
BookOpen 2 | 0
SheetAdd 0 | 0
Edited by Chimaera

Share this post


Link to post
Share on other sites

I just noticed that the For statement is wrong. Element 0 contains the number of elements in the array. So it needs to be:

For $i = 1 To $aCsvFilesToConvert[0]

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

Well blow me down

i now get

BookOpen 3 | -2147352567
 
: ==> Variable must be of type "Object".:
Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1"))
Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1^ ERROR
 
Got it
 
Changed to this now
Global $aCsvFilesToConvert = _FileListToArray( @ScriptDir,"*.csv", 1, True)
and it works
Edited by Chimaera

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
Sign in to follow this  
Followers 0