Jump to content

Read excel write to foldername issue

Sam1el
 Share

Recommended Posts

Sam1el Seeker

Sam1el

Posted
Posted (edited)

So basically this is what I'm doing.

My gf was asked to create a folder structure using a list she was given. The list is in excel and has 650 folders to be created and each of the 650 folders will have 6 identical sub directories. This is where I am so far.

I can parse the csv file and have it create folders with names pulls from column 1 for example the csv file reads

test,test 2,test 3
test 4,,
test 5,,

but it creates folders named test, test 4 and test 5. what I need it to do is take test test2 and test 3 and use those values to create one folder name and to the same to all the following rows. IE read row1 and create folder test_test2_test3

here's my code.

Local $sFile = "dir.csv"

Local $aREResult = StringRegExp(FileRead($sFile), ".+(?=\v+|$)", 3) ; returns array of every line
Local $iNumLines = UBound($aREResult)
ConsoleWrite("$iNumLines; " & $iNumLines & @CRLF)

;Get number of commas / columns.
Local $aREResult = StringReplace(FileRead($sFile), ",", ",") ; returns number of commas in file
Local $iNumCommas = @extended
ConsoleWrite("$iNumCommas per row; " & Int($iNumCommas / $iNumLines) + 1 & @CRLF)

Global $aMain[$iNumLines][($iNumCommas / $iNumLines) + 1], $iRow = 0 ; Array for csv file

_CSVFileToArray ($sFile) ; Fill array from file

;_ArrayDisplay($aMain, "csv file Results")


Func _CSVFileToArray($sFile)
Execute(StringTrimRight(StringRegExpReplace(StringRegExpReplace(FileRead($sFile), '"', '""'), "(\V+)(\v+|$)", 'Test1(StringRegExp("\1","([^,]+)(?:,|$)",3)) & '), 3))
EndFunc ;==>_CSVFileToArray

; Fills each row of the required 2D array
Func Test1($aArr)
For $x = 0 To UBound($aArr) - 1
$aMain[$iRow][$x] = $aArr[$x]
Next
$iRow += 1
Return
EndFunc ;==>Test1
$content=$aMain
dircreate($content)

For $i = 0 To UBound($aMain) - 1
    DirCreate ($aMain[$i][0])
Next

any help is greatly appreciated

Edited by Sam1el
Spiff59 Universalist

Spiff59

Posted
Posted (edited)

This might give you a start:

#include <Array.au3> ; for testing
Local $sFile = "dir.csv", $string

$file = FileOpen($sFile, 0)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    If $line Then $string &= $line & "," ; ignore blank lines
Wend
FileClose($file)
$string = StringTrimRight($string, 1) ; strip final comma
$aArr = StringSplit($string, ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

For $x = 1 To $aArr[0]
    ; create folders
Next

Or:

#include <Array.au3> ; for testing
Local $sFile = "dir.csv"
$aArr = StringSplit(StringRegExpReplace(StringReplace(FileRead($sFile), @CRLF, ","), "[,]+\z", ""), ",") ; split into array
_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing
Edited by Spiff59
Sam1el Seeker

Sam1el

Posted
  • Author
Posted

This might give you a start:

#include <Array.au3> ; for testing
Local $sFile = "dir.csv", $string

$file = FileOpen($sFile, 0)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    If $line Then $string &= $line & "," ; ignore blank lines
Wend
FileClose($file)
$string = StringTrimRight($string, 1) ; strip final comma
$aArr = StringSplit($string, ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

For $x = 1 To $aArr[0]
    ; create folders
Next

Or:

#include <Array.au3> ; for testing
Local $sFile = "dir.csv"
$aArr = StringSplit(StringRegExpReplace(StringReplace(FileRead($sFile), @CRLF, ","), "[,]+\z", ""), ",") ; split into array
_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

I managed to get it working somewhat with 

#include <File.au3>

Global $usrArray
_FileReadToArray("dir.csv" $usrArray)
For $i = 1 To $usrArray[0]
    DirCreate(@ScriptDir&"\"&$usrArray[$i])
Next

only issue is it's putting commas in the folder names now

Spiff59 Universalist

Spiff59

Posted
Posted (edited)

_FileReadToArray won't split the lines with multiple columns for you...

I think the one-liner above does the trick?

Edit: It:

1 - reads the entire file into a string

2 - converts CRLF's to ","

3 - strips off any trailing commas

4 - splits the string into an array

Edited by Spiff59
Sam1el Seeker

Sam1el

Posted
  • Author
Posted

_FileReadToArray won't split the lines with multiple columns for you...

I think the one-liner above does the trick?

Edit: It:

1 - reads the entire file into a string

2 - converts CRLF's to ","

3 - strips off any trailing commas

4 - splits the string into an array

Tried this seems to just ignore anything after the first comma in the row. I may have to find a way to just remove the comma form the directory names with autoit,

Spiff59 Universalist

Spiff59

Posted
Posted (edited)

Think I misunderstood the example data you put up there...

Was thinking the trailing commas were shorthand for "and so on..."

So, those are in the data and causing you the trouble?

The same code from before, copied below, now creates a test.csv using your example data.

Adjacent commas create empty array slots.

Since this is a simple script, you could just leave it like that and ignore the blank array elements in the loop of your folder creation function.

#include <Array.au3> ; for testing

Local $sFile = "test.csv"
; build a test file -----------------------------
$hFile = FileOpen($sFile, 2)
FileWriteLine($hFile, "test,test 2,test 3")
FileWriteLine($hFile, "test 4,,")
FileWriteLine($hFile, "test 5,,")
FileClose($hFile)
; -----------------------------------------------

$aArr = StringSplit(StringRegExpReplace(StringReplace(FileRead($sFile), @CRLF, ","), "[,]+\z", ""), ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

Or, instead of reading the file in all at once, go back to reading it a line-at-a-time, and strip trailing commas off of each line as you go:

#include <Array.au3> ; for testing

Local $sFile = "test.csv", $string

$file = FileOpen($sFile, 0)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $line = StringRegExpReplace($line, "[,]+\z", "") ; strip trailing commas
    If $line Then $string &= $line & "," ; ignore blank lines
Wend
FileClose($file)
$string = StringTrimRight($string, 1) ; strip final comma
$aArr = StringSplit($string, ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

For $x = 1 To $aArr[0]
;    If $aArr[$x] Then create folder...
Next
Edited by Spiff59
Sam1el Seeker

Sam1el

Posted
  • Author
Posted (edited)

Think I misunderstood the example data you put up there...

Was thinking the trailing commas were shorthand for "and so on..."

So, those are in the data and causing you the trouble?

The same code from before, copied below, now creates a test.csv using your example data.

Adjacent commas create empty array slots.

Since this is a simple script, you could just leave it like that and ignore the blank array elements in the loop of your folder creation function.

#include <Array.au3> ; for testing

Local $sFile = "test.csv"
; build a test file -----------------------------
$hFile = FileOpen($sFile, 2)
FileWriteLine($hFile, "test,test 2,test 3")
FileWriteLine($hFile, "test 4,,")
FileWriteLine($hFile, "test 5,,")
FileClose($hFile)
; -----------------------------------------------

$aArr = StringSplit(StringRegExpReplace(StringReplace(FileRead($sFile), @CRLF, ","), "[,]+\z", ""), ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

Or, instead of reading the file in all at once, go back to reading it a line-at-a-time, and strip trailing commas off of each line as you go:

#include <Array.au3> ; for testing

Local $sFile = "test.csv", $string

$file = FileOpen($sFile, 0)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    $line = StringRegExpReplace($line, "[,]+\z", "") ; strip trailing commas
    If $line Then $string &= $line & "," ; ignore blank lines
Wend
FileClose($file)
$string = StringTrimRight($string, 1) ; strip final comma
$aArr = StringSplit($string, ",") ; split into array

_ArrayDisplay($aArr, "Array elements: " & $aArr[0]) ; for testing

For $x = 1 To $aArr[0]
;    If $aArr[$x] Then create folder...
Next

I have tried every variation we've posted here. I'm throwing my hand in the air. I've only been playing with autoit for a week so I still have much to learn but if someone wants to take a crack at it

I will post both files here and. Save them to a folder and run the script you will see the output i'm currently getting which would be perfect if I could just get rid of the commas. Just change the .txt to .csv and it should work.

THis is exactly how i want the folders to be created i just need the commas gone lol or even replaced with an underscore would work.

Posted Image

foldercreatefromxl.au3

dir.txt

Edited by Sam1el
smartee Universalist

smartee

Posted
Posted

Try this :)

#include <File.au3> ;needed for _FileReadToArray function

Dim $asDirList ;declare array var for lines of file

_FileReadToArray("dir.csv", $asDirList) ;read lines of file into array with 1st element=num of lines read
If @error Then MsgBox(0, "", "Error, Could not read file.")

For $i = 1 To $asDirList[0]
    $asDirList[$i] = StringRegExpReplace($asDirList[$i], ",(\w+)", "_$1") ;replace ,test with _test
    $asDirList[$i] = StringReplace($asDirList[$i], ",", "") ;remove trailing commas
    DirCreate(@ScriptDir & "\" & $asDirList[$i]) ;create dir
Next
Sam1el Seeker

Sam1el

Posted
  • Author
Posted

Try this ;)

#include <File.au3> ;needed for _FileReadToArray function

Dim $asDirList ;declare array var for lines of file

_FileReadToArray("dir.csv", $asDirList) ;read lines of file into array with 1st element=num of lines read
If @error Then MsgBox(0, "", "Error, Could not read file.")

For $i = 1 To $asDirList[0]
    $asDirList[$i] = StringRegExpReplace($asDirList[$i], ",(\w+)", "_$1") ;replace ,test with _test
    $asDirList[$i] = StringReplace($asDirList[$i], ",", "") ;remove trailing commas
    DirCreate(@ScriptDir & "\" & $asDirList[$i]) ;create dir
Next

OMFG! you're the man. I had tried something similar when I began and thought I was overthinking it and couldn't make it work so I started over. You guys rock thanks for all the help. :)
Sam1el Seeker

Sam1el

Posted
  • Author
Posted (edited)

Any pointers on creating specifically named subdiretories in those folders? not to be a pain but i'm looking at creating roughly 6 folders for each of these 650 folders

Edited by Sam1el
Sam1el Seeker

Sam1el

Posted
  • Author
Posted

GOT IT!!!!!! :)

#include <File.au3> ;needed for _FileReadToArray function


Dim $asDirList ;declare array var for lines of file
Dim $asDirList2

_FileReadToArray("dir.csv", $asDirList);read lines of file into array with 1st element=num of lines read
_FileReadToArray("subs.csv", $asDirList2)
If @error Then MsgBox(0, "", "Error, Could not read file.")

For $i = 1 To $asDirList[0]
For $2 = 1 To $asDirList2[0]
    $asDirList[$i] = StringRegExpReplace($asDirList[$i], ",(\w+)", " $1") ;replace ,test with _test
    $asDirList[$i] = StringReplace($asDirList[$i], ",", "") ;remove trailing commas
    DirCreate(@ScriptDir & "\" & $asDirList[$i] & "\" & $asDirList2[$2]) ;create dir
Next
Next
Sam1el Seeker

Sam1el

Posted
  • Author
Posted (edited)

Now to create a gui version of it that allows me to select the csv files I want to use

Edited by Sam1el

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
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...