cristner Posted April 9, 2004 Share Posted April 9, 2004 Well, I have been trying to do this on my own for the past 3 days with no luck. There is lots of information in the help file and here in the forum but I couldn't find a sample to fit my needs and wanted to put this out to see if anyone else would have better luck. What I have is a CSV file (dbflags.txt) that has the first "column" as the name of the database, followed by a varying number of key/label pairs - AccountPSA,Account Number,2,PSA,3,Effective Start Date,4, Effective End Date,5,Account Type,6 AddressItems,Last Name,2,First Name,3,Middle Initial,4,E-mail,5,Fax#,6,Notes,7 Confirmation,Item,1,Confirmation,2,Time/Date,3 The second CSV file is the actual exported data from each database with the filename "database.log" 1,"922213480","N1T3",1/14/2004,12/31/2030,"N" 2,"","",1/14/2004,12/31/2030,"R" 1,"JENKINS","DANI","","DANI@COMPANY.COM","","" 2,"HEINLEIN","SUSAN","","SUSAN@COMPANY.COM","","" "ESD","02162004",3/15/2004 8:42:23 AM "RATE","2004011402213103",1/14/2004 7:15:54 PM Currently I have a program that creates these database exports to the "database.log" filename with data as sampled above. What I want my program to do is look at the my "dbflags.txt" file, read in the first line to know which database I want to read from and what data pairs to return. For example, from the AccountPSA database, I want to read the "Account Number" which is in position 2, the PSA from position 3, and so on and write them to a common dbreport.txt file. Once it is done reading all the lines from that database file, goto the next line in the "dbflags.txt" file and repeat the process for all the databases listed. the "dbflags.txt" file is created manually and can have the numbers changed to reflect the actual array[#] position is necessary. What I tried to do before was to put my dbflags.txt file into an array and then create another array of the associated database file based on the database listed in my first array[1] positions. I just kept getting stuck on the multi-array logic and hoped that someone here could help guide me in the right direction. Thanks in advance to any responses. Link to comment Share on other sites More sharing options...
scriptkitty Posted April 9, 2004 Share Posted April 9, 2004 this might get you started: $flagsfile="dbflags.txt" $flagtotal=FileRead($flagsfile,FileGetSize($flagsfile)) $flagtotal=StringReplace($flagtotal,@lf,""); removes @lf from @crlf $flagarray=Stringsplit($flagtotal,@cr); each line is an array now for $i = 1 to $flagarray[0] $linearray=Stringsplit($flagarray[$i],",") creates array from commas in each line ; slightly mixed up on what you want so I will just show some output atm. if ubound($linearray)>3 then; makes sure array worked msgbox(1,"Line"& $i, $linearray[$i] & @crlf & "account#"& $linearray[2] &@crlf & "PSA "&$linearray[3] ) endif next untested cause I wrote from memory atm. This help a bit? AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
cristner Posted April 9, 2004 Author Share Posted April 9, 2004 I was afraid I was going to be too wordy in my describing what I am trying to do. The dbflags.txt file is going to contain on each line the database filename and then the key/label pairs that I want returned from the database.log CSV files that I exported. Because these database.log files will have more info than I want, I created the dbflags.txt to try to tell my program which key/label pairs only that I want to write to the dbreport.txt. Because of this, I thought I would need to use an array of each "line" in the dbflags.txt and using ubound, I could determine how many pairs I had and create a for/next loop to "read" each pair and write them to my dbreport.txt. I see part of my problem as I didn't know I needed to remove the @LF out of my reading the array as my text file have both a LF/CR and the @LFCR wouldn't work as a delimiter. I'll see if this fixes part of my problem but I still need to figure out how to use the database name from my first "array" and read the key/label pairs from each database.log file. Hope this helps explain things, maybe all I need is a good night sleep and try this again tomorrow. Thanks again for you help. Link to comment Share on other sites More sharing options...
cristner Posted April 12, 2004 Author Share Posted April 12, 2004 I am still stumped here. I am attempting to read my file DBFlags.txt into an array using the below code but I still do not get any results. I want to be able to recall $DBArray[0][0] as "AccountPSA", $DBArray[0][1] as "Account Number", $DBArray[0][2] as "2" and so on. Then down so that $DBArray[1][0] is "AddressItems", $DBArray[1][1] is "Last Name", $DBArray[1][2] is "2"and so on. Could someone please look over my code and let me know what I am missing to do this. My DBFlags.txt file will have different "lengths" for each line item so do I have to set a Dim $DBArray[x][y] first to ensure that my array is large enough to hold my data? Thank you for any assistance provided. [DBFlags.txt] AccountPSA,Account Number,2,PSA,3,Effective Start Date,4, Effective End Date,5,Account Type,6 AddressItems,Last Name,2,First Name,3,Middle Initial,4,E-mail,5,Fax#,6,Notes,7 Confirmation,Item,1,Confirmation,2,Time/Date,3 DHLConfiguration,Address #1,5,Address #2,6,Address #3,7,Address #4,8,Address #5,9 expandcollapse popup . . . .FileReadToArray("c:\dbflags.txt",$DBArray) . . . Func FileReadToArray( $sFilePath, ByRef $aArray ) Local $hFile, $sReadFile, $i $hFile = FileOpen( $sFilePath, 0 ) If $hFile = -1 Then SetError( 1 ) Return 0 EndIf $aLongArray = StringSplit( FileRead( $hFile, FileGetSize( $sFilePath )), @CRLF) For $i = 1 To $aLongArray[0] $aArray = $aArray + StringSplit( $aLongArray, "," ) Next FileClose( $hFile ) Return 1 EndFunc Link to comment Share on other sites More sharing options... Developers Jos Posted April 12, 2004 Jos Developers 34.3k 154 Je maintiendrai Developers Share Posted April 12, 2004 don't think you can fill a multi dimentional array with a stringsplit. Try it this way ... (untested) Dim $AARRAY[100][50] FileReadToArray("c:\dbflags.txt", $DBARRAY) Func FileReadToArray( $SFILEPATH, ByRef $AARRAY ) Local $HFILE, $SREADFILE, $I, $IREC Local $RECCOUNT = 0 $HFILE = FileOpen( $SFILEPATH, 0 ) If $HFILE = -1 Then SetError( 1 ) Return 0 EndIf While 1 $IREC = FileReadLine( $HFILE) If @error = -1 Then ExitLoop $ALONGARRAY = StringSplit($IREC, ",") $RECCOUNT = $RECCOUNT + 1 For $I = 1 To $ALONGARRAY[0] $AARRAY[$RECCOUNT][$I] = $ALONGARRAY[$I] Next Wend FileClose( $HFILE ) Return 1 EndFunc ;==>FileReadToArray SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
cristner Posted April 13, 2004 Author Share Posted April 13, 2004 That worked great. My code is finally working the way I want it to, however I've now run into a problem when my data contains a comma that is surrounded by quotes. For Excel and most CSV programs, it is fine, but for my code it crashes - example 6,1/5/2004,11:51:24 AM,1,"CTP","Computers" - Works fine 15,1/5/2004,11:51:24 AM,1,"TSU","Technology & Software, Unrestricted" - Problem Has anyone played around with StringSplit to ignore the delimiter when it is surrounded by quotes? I am using the code provided below by JdeB if you want to expand on that sample. Link to comment Share on other sites More sharing options...
scriptkitty Posted April 14, 2004 Share Posted April 14, 2004 (edited) you could always import it into excel and export as pipe delimited, or such.6|1/5/2004|11:51:24 AM|1|"CTP"|"Computers" - Works fine15|1/5/2004|11:51:24 AM|1|"TSU"|"Technology & Software, Unrestricted"or you could use a commandline Regular Expression tool, and have it substitute for ya. Edited April 14, 2004 by scriptkitty AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
Developers Jos Posted April 14, 2004 Developers Share Posted April 14, 2004 (edited) That worked great. My code is finally working the way I want it to, however I've now run into a problem when my data contains a comma that is surrounded by quotes. For Excel and most CSV programs, it is fine, but for my code it crashes -  example   6,1/5/2004,11:51:24 AM,1,"CTP","Computers" - Works fine   15,1/5/2004,11:51:24 AM,1,"TSU","Technology & Software, Unrestricted" - Problem Has anyone played around with StringSplit to ignore the delimiter when it is surrounded by quotes? I am using the code provided below by JdeB if you want to expand on that sample.What about doing to following before the StringSplit? StringReplace($IREC,'","','@#@') StringReplace($IREC,',','|') StringReplace($IREC,'@#@','","') $ALONGARRAY = StringSplit($IREC, "|") Edited April 14, 2004 by JdeB SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
scriptkitty Posted April 14, 2004 Share Posted April 14, 2004 This didn't seem to help, but I might have done it wrong:StringReplace($IREC,'","','@#@')Â StringReplace($IREC,',','|')StringReplace($IREC,'@#@','","')$ALONGARRAY = StringSplit($IREC, ",")my other thought would be to recombine or search for the problems.ex:First string split.When you come across an element that has " as the first character, but not the last, combine it with the next element, until you find an element with " as the last character.You would want to remove those extra quotes anyway.I can post code tomorrow if you don't figure it out before then. AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
Developers Jos Posted April 14, 2004 Developers Share Posted April 14, 2004 (edited) This didn't seem to help, but I might have done it wrong:Updated my post... made a typo: StringReplace($IREC,'","','@#@') StringReplace($IREC,',','|') StringReplace($IREC,'@#@','","') $ALONGARRAY = StringSplit($IREC, "|") Edited April 14, 2004 by JdeB SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
scriptkitty Posted April 14, 2004 Share Posted April 14, 2004 (edited) JdeB, I know what you are trying to do, but there would be a ton of things to fix, besides that you are not replacing anything:StringReplace($IREC,'","','@#@')Â StringReplace($IREC,',','|')StringReplace($IREC,'@#@','","')$ALONGARRAY = StringSplit($IREC, "|")did you mean:$irec='6,1/5/2004,11:51:24 AM,1,"CTP","Computers",15,1/5/2004,11:51:24 AM,1,"TSU","Technology & Software, Unrestricted"'$IREC=StringReplace($IREC,'","','@#@')Â $IREC=StringReplace($IREC,',','|')$IREC=StringReplace($IREC,'@#@','","')$ALONGARRAY = StringSplit($IREC, "|")and you would need to replace ," and ", as well as non text fields would be in there as well. I am missing a bit of your logic in there somewhere. Edited April 14, 2004 by scriptkitty AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
Developers Jos Posted April 14, 2004 Developers Share Posted April 14, 2004 yea... see now what you mean..... Then i think to only way is to go through the record 1 character at a time and test for comma's and make sure they are not inside "".. here a an example... (this time tested it with the 2 records in the example ): $IREC = '6,1/5/2004,11:51:24 AM,1,"CTP","Computers"' $IREC = '15,1/5/2004,11:51:24 AM,1,"TSU","Technology & Software, Unrestricted"' For $X = 1 To StringLen($IREC) $TS = StringMid($IREC, $X, 1) MsgBox(0, 'debug:' & $X & "-" & $TS, $IREC) Select Case $TS = '"' $X = $X + StringInStr(StringTrimLeft($IREC, $X), '"'); set char pointer to next " Case $TS = ',' $IREC = StringLeft($IREC, $X - 1) & '|' & StringTrimLeft($IREC, $X) EndSelect Next $ALONGARRAY = StringSplit($IREC, "|") MsgBox(0, 'debug', $ALONGARRAY[0]) For $X = 1 To $ALONGARRAY[0] MsgBox(0, 'debug', $ALONGARRAY[$X]) Next SciTE4AutoIt3 Full installer Download page  - Beta files    Read before posting   How to post scriptsource   Forum etiquette Forum Rules  Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
cristner Posted April 15, 2004 Author Share Posted April 15, 2004 Looks like I got it to work. Actually I got around my problem with some data having quotes and some not but using an exteral program called CSVdb (now CSVed) from http://home.hccnet.nl/s.j.francke/software/software.htm. It's a great program for those of us who have to manipulate CSV data. I wanted to thank you both for helping me out in getting my script to work. I only hope to get my knowledge level up a few notches and be able to return the favor to a fellow newbie. With that said, now I need a little more help in testing for whether an array query is out of bounds or not. Is there an @error or something similar that I can use that will easily let me know if my array query is out of range or not. Looking for something as simple as the example below. I know this raw example isn't logical, but it is sometimes what my program sees as I get my numbers from a list that doesn't know prior what the array dimensions are so I can't assume if a certain number is greater than a set value. Thanks again.Dim $aArray[50][50]If @error $aArray[50][51] Skip somethingEndIf Link to comment Share on other sites More sharing options...
Valik Posted April 15, 2004 Share Posted April 15, 2004 See UBound in the help file. Link to comment Share on other sites More sharing options...
cristner Posted April 15, 2004 Author Share Posted April 15, 2004 D'uh - I knew that. Really :-) Thanks 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