Sign in to follow this  
Followers 0
Melfritch

Split excel in alphabetical ordered files?

9 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
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 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

#3 ·  Posted (edited)

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

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

Share this post


Link to post
Share on other sites

Thx guys really appreciated, k one last question then..how do i set _ArraySearch to search all the words starting with ''A'' and stop before ''B'' ?

_ArraySearch($avArray, $sSearch, 0, 0, 0, 1)
$iCase?

Share this post


Link to post
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 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

Thank you so much JLogan ..whats the argument to save the entire row and not just the first cell at :

ExcelWriteCell($newbook1, $aArray[$i][1], $i, 1) ?

Share this post


Link to post
Share on other sites

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?


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

Share this post


Link to post
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│

Share this post


Link to post
Share on other sites

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.


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

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