Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate

Manipulating Data in a read variable

  • Please log in to reply
2 replies to this topic
  • Members
  • 3 posts
  • Last active: Oct 31 2015 07:25 PM
  • Joined: 25 Oct 2015

I have a series of 8 csv files (around 40k lines total) that I am working on integrating into one file and then updating the information daily. I have the code working decently (with only minor annoyances) but I am wondering if there are better ways to handle two of the processes.

ToolTip Reading All Files                                 ;Read all files into Variables
FileRead, Total, Level.txt                                ;Read Original File
Loop, 8                                                   ;Loop through Levels
Level :=A_Index - 1                                       ;Level numbers range 0-7
ToolTip Reading Level %Level%
FileRead, Level%Level%, Level%Level%.csv                  ;Read CSV File into Level Variable
StringReplace,OutputVar,Level%Level%,`n,`n,useerrorlevel  ;LineCount from forums
Lines%Level% :=ErrorLevel + 1                             ;LineCount from forums
LinesTotal :=LinesTotal + Lines%Level%
Loop, 8
Level :=A_Index - 1
Loop, Read, Level%Level%.csv, Level.txt               ;Read through Each Level file one line at a time. Append changes to Level.txt
     CurrentLine ++
     Lines%Level% := % Lines%Level%
     ToolTip Reading line %A_Index% of %LinesLevel% from Level %Level%`nLine %CurrentLine% of %LinesTotal%`n%A_LoopReadLine%`nNew Items:`n%NewItems%
     StringSplit, Array, A_LoopReadLine, `,                  ;Split Comma Separated File into individual variables
     If Array1 is not integer                                ;First Variable should be Item Number
          Continue                                           ;If not then read next line
     IfInString, Total, %Array1%                             ;Item Number should appear within the Complete File
          Continue                                           ;If not then
     StringReplace NewLine, A_LoopReadLine, `,, %A_Tab%, A   ;Change to Tab Deliminated
     FileAppend %NewLine%`n                                  ;Add new item to Complete File
     ;NewItems = %NewItems%`n%A_LoopReadLine%
Total =                                          ;Clear Total Variable
FileRead, Total, Level.txt                       ;Replace with updated list
StringReplace,OutputVar,Total,`n,`n,useerrorlevel;LineCount from forums
Lines:=ErrorLevel + 1                            ;LineCount from forums
Loop, Read, Level.txt, LevelUpdated.txt          ;Read file line-by-line and create updated file (Add Date column with current Level)
     ToolTip Analyzing Item %A_Index% of %Lines%`n%A_LoopReadLine%
     StringSplit, Array, A_LoopReadLine, %A_Tab%
     If A_Index = 1
          FileAppend %A_LoopReadLine%%A_Tab%%A_YYYY%/%A_MM%/%A_DD%`n      ;On the first line add a date field
     If Array1 is not integer                                             ;If not an item number skip line
     Loop, 8                                                              ;Loop through Levels
          Level :=A_Index - 1                                             ;Level numbers range 0-7
          IfInString, Level%Level%, %Array1%                              ;If Item Number found within Level
               FileAppend %A_LoopReadLine%%A_Tab%%Level%`n                ;Add Current Level to today's column

The process works fine and I've gotten better (somewhat) at dynamic changes to some of the variables so I don't have to type and edit 8 different versions of each loop. However I want to try to fix these two issues:


1- I have read each csv into a variable which makes it easy to locate item numbers across files and get line numbers, but I still use a reading loop to process each line. Is there a better/faster way I should be looking at doing this? Should I try to StringSplit the variable on line breaks and then process it that way?


2- I am updating each line with the current "Level" that the item is at every day. This is just appending onto the end of each line so that I end up with:

Item ID	Call Number	Title	Author	Library	Year Published	Total Checkouts	Date Copy Created	Date Last Checked Out	Copy Price	10/25/2015
0000516459062	004.165 POG	IPHONE THE MISSING MANUAL	"Pogue	 David"	WE-WH	2008	26	9/17/2008	9/23/2015	25	0
0000518571237	004.167 GOO 2012	ANDROID PHONES FOR DUMMIES	"Gookin	 Dan	 author"	WE-WH	2012	28	5/11/2012	9/16/2015	25	0

This works fine and every day I can add a new column and then eventually graph the daily level changes. It would be useful to be able to update two of the columns as I am doing this (Total Checkouts and Date Last Checked Out specifically) but the only way I can think to do this is to Loop Read to the correct line and then split that line and update the values which seems like a very slow process to add.


Any suggestions are appreciated!

  • Members
  • 520 posts
  • Last active:
  • Joined: 12 Aug 2014

Some general comments:


1) You say you read each CSV into a variable but as far as I can see you use a file parsing loop to read the files, you could replate that with a string parsing loop


FileRead, data, file.txt
Loop, parse, data, `n, `r ; don't forget the ignore `r
; ...

And keep all the data in memory and only write the files when ready.



- The tooltip is handy but also slows the script down (my personal experience)

- Add SetBatchLines, -1 at the top of you script, that should also help



2) You are working with tab delimted files (CSV) - there are two libraries that can help you reading and updating rows, columns and cells.

 There is the Table lib by VxE https://github.com/J...E/AHK-Lib-Tableand the CSV lib by kdoske (works with TAB too if you define TAB as a delimeter) http://autohotkey.co...sv-library-lib/ ( actually there is third one, but I haven't worked with it but perhaps worth looking into https://github.com/JnLlnd/ObjCSV ) Note that if you try the kdoske csv lib you may need to fix the code posted on the forum - the code at the top is messed up a bit (easy to spot)


You'll have try and see which library works for you but once you have one that does, you can read and modify cells which might not be faster as such but may proof easier to maintain and modify in the long run.

  • Members
  • 3 posts
  • Last active: Oct 31 2015 07:25 PM
  • Joined: 25 Oct 2015

I read the csv files into a variable at the beginning. Line 8. But the only thing I use it for is for finding matching items in the lists. I'll have to play around with the string parsing loop some.


I could get rid of the tooltip and I've thought about doing so once I'm able to automate the rest of the process (getting the files and setting them up) but at least while I'm running it manually I like knowing the progress. It's good to know that it will speed things up though when I remove it.


I'll look through the libraries you linked. I mostly switch to Tab deliminated because when I move back and forth from Excel tab seems to be handled better than commas even though I open it as a comma separated file. But at first glance the libraries look like they may help with properly manipulating data in the table.


Thanks again for the assistance!