Chimaera Posted January 16, 2014 Share Posted January 16, 2014 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? If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted January 16, 2014 Moderators Share Posted January 16, 2014 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 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted January 16, 2014 Share Posted January 16, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 16, 2014 Author Share Posted January 16, 2014 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? If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 16, 2014 Share Posted January 16, 2014 Unfortunately: No. Excel opens each CSV file as a workbook. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 16, 2014 Author Share Posted January 16, 2014 Aah so i open each one then copy contents then close and repeat If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 16, 2014 Share Posted January 16, 2014 Correct My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 17, 2014 Author Share Posted January 17, 2014 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 If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 17, 2014 Share Posted January 17, 2014 Should be: Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1")) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 18, 2014 Share Posted January 18, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 18, 2014 Share Posted January 18, 2014 So what's the value of @error after _Excel_BookOpen and_Excel_SheetAdd? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 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() ;~ 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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 18, 2014 Share Posted January 18, 2014 @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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 18, 2014 Share Posted January 18, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted January 18, 2014 Share Posted January 18, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted January 18, 2014 Author Share Posted January 18, 2014 (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 January 18, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now