Jump to content

Reading, Writing, Stringsplit, Oh My!


Recommended Posts

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

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

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

:D

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

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


Jos
            
        
        
            
                
                    


    
        
    

                    
                        
                    
                    
                        

                    
                
            
            
                Developers
                
                    
                
            
            
                
                    
                        
                            
                                
                            
                                 34.3k
                            
                                
                            
                        
                        
                            
                                
                                    
                                        
                                        154
                                
                                    
                                
                            
                        
                    
                
            
            
                

    
    
        
Je maintiendrai
    
    

            
        
    
    
        



    
        
            
                
                    
                    
                        Developers
                    
                    
                    
                    
                
            
            
                
                    
                    
                        
                        
                            Share
                        
                        
                        
                        
                        
                            
                                
                            
                            
                            
                            
                            
                            
                        
                    
                
                
            
        

        
            Posted 
            
            
                
                
            
        
    

    

    

    
        
        
            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

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

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 fine

15|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 by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Link to comment
Share on other sites

  • Developers

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

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

  • Developers

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

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 by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Link to comment
Share on other sites

  • Developers

yea... see now what you mean..... :huh2:

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 :D ):

$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

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 something

EndIf

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