Jump to content

Split excel in alphabetical ordered files?


Melfritch
 Share

Recommended Posts

Hi guys,

Ive been searching throught the help me file and havent found any info on the subject..is there a function in auto it once _ExcelBookOpen has been called to save the file in separate files named after the alphabetical order of the cells in the first collumn? ( all words starting with ''A'' saved in in 1 file named A.csv, all words with ''B'' saved in 1 file named B.csv, etc...) basicly i want to save the entire row in those files...

Any Hint on the subject would be greatly appreciated..im really stumped on the subject right now : /

Edited by Melfritch
Link to comment
Share on other sites

Read the whole worksheet into an array using _ExcelReadSheetToArray, sort the array in alphabetical order, then loop through the array and write all rows to a file until the first character changes.

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

  • Moderators

Hi, Melfritch, welcome to the forum. My best guess would be to use _ExcelReadSheetToArray first, then loop through your array and pull out any items that start with the letter(s) you'd like. Then save those off to separate files.

D'oh, too slow :)

I would envision something like this to get you started, Melfritch. You should be able to fit this to your needs.

#include <Excel.au3>

Local $oExcel = _ExcelBookOpen(@DesktopDir & "\Names.xls", 0, True)
Local $newbook1 = _ExcelBookNew()
Local $newbook2 = _ExcelBookNew()
Local $newbook3 = _ExcelBookNew()

Local $aArray

$aArray = _ExcelReadSheetToArray($oExcel, 1, 1)
_ExcelBookClose($oExcel, 0)


_ArrayDisplay($aArray)

For $i = 1 To UBound($aArray) - 1
   Select
      Case StringLeft($aArray[$i][1], 1) = "a"
         _ExcelWriteCell($newbook1, $aArray[$i][1], $i, 1)
      Case StringLeft($aArray[$i][1], 1) = "b"
         _ExcelWriteCell($newbook2, $aArray[$i][1], $i, 1)
      Case StringLeft($aArray[$i][1], 1) = "c"
         _ExcelWriteCell($newbook3, $aArray[$i][1], $i, 1)
   EndSelect
Next

_ExcelBookClose($newbook1, 1)
_ExcelBookClose($newbook2, 1)
_ExcelBookClose($newbook3, 1)
Edited by JLogan3o13

"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

You have to do it yourself.

Sort the array ascending on column 1. The process the array like this:

Local $sChar = ""
For $i = 1 to $avArray[0][0]
If $sChar <> StringLeft($avArray[$i][0], 1) And $sChar <> "" Then ; First character has changed
     ; Do whatever needs to be done when the first character changes
Endif
$sChar = StringLeft($avArray[$i][0], 1)
; Do whatever needs to be done with the current record
FileWriteLine("C:\temp\" & $sChar & ".CSV", "<here goes the data you want to write to the file>")
Next

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

  • Moderators

I guess I am unsure what you mean by saving an entire row; you take each index in the array and write it to a single cell. Are you saying you would rather write them all in one row, rather than in one column?

"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

Lets say that excel row is like this :

___________________________________

│Arkon │arkon products │2.99$│22/02/12 │

at the moment the script only saves ''Arkon'' in the excel file named a.csv, i need it to save the entire row : │Arkon │arkon products│2.99$│22/02/12│

Link to comment
Share on other sites

  • Moderators

Are you gathering all of those pieces of info from the other spreadsheet? If so, you need to understand that when you read the sheet to an array each piece of info will be a different element in the array. You would have to code the logic that puts "arkon products", "2.99$", and "22/02/12" on the same row as the "Arkon" element.

If you need to read to read your original sheet row by row so all that information stays together, we'll have to go about it a different way.

"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

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...