Jump to content
Sign in to follow this  
marko001

Help optimizing CSV conversion

Recommended Posts

marko001

Hi all,

I need to convert a .csv file that after conversion will fill an array for futher usage.

function used/not used: stringsplit(), stringregexp(),stringinstr(), stringreplace()

I know the best way is to use stringsplit given a delimiter (,). But this is not possible here since:

Here is some lines fo the .csv

Game Title,Quantity,For Sale,Platform,Rating,Number,New
After Life,5,2,PC,PG1,23332,Yes
Max Payne,4,0,Wii,PG3,109,No
Fifa 2009,11,2,DS,PG2,976,Yes
"Hellgate, London",3,2,PC,PG2,112,No
Logitech Mouse,12,4,mouse, ,No
Pinball 2010,1,1,PS3,PG1,065,No

As you can see:

"Hellgate, London",3,2,PC,PG2,112,No

will not work with stringsplit() since the "," it's inside the name of the object

Logitech Mouse,12,4,mouse, ,No

Here I have blank spaces between "," and the field "Rating" is missing. This is due to the .csv format.

When Platform <> PC,Wii,DS,PS1,PS2,PS3,XB,X3 then I need to set Platform = Hardware and Rating = the value found in place of platform (in this case Platform = N, Rating = Hardware)

Once this is finished and I have my array built, I need to add 2 more columns to it.

I get the columns _arraysearch()ing between the just created array and another file (.xlm). If i found the item I need I add the prices, if not, skip.

So now I use this code (working):

$exportfile = @ScriptDir & "\filelist.csv"
Dim $mydb
Global $file2ftp = @TempDir & "\temp2db.txt"
Dim $csv_list[1][7]
Global $rows
 
Func _exp2db_x()
    Local $iCounter = 0, $array2db[10000]
    Local $user2db
    Local $k = 0
    Dim $csv_array
    _FileReadToArray($exportfile, $csv_array)
    _FileReadToArray($price_file, $mydb)
    _ArraySort($mydb)
    $begin = TimerInit()
    For $i = 1 To UBound($csv_array) - 1
        If StringInStr($csv_array[$i], ", ,") Then ; i'm looking for a ", ," in lines
            $string = StringReplace($csv_array[$i], ", ,", ",@,", 1) ; if found I change it
        Else
            $string = $csv_array[$i]
        EndIf
        If StringInStr($string, ", ") Then $string = StringReplace($string, ", ", "^", 1); If I find "," inside the name I need to change it
        $name_s = _StringBetween($string, "", ",")
        $name = _cleanfordb($name_s[0]) ; Need to reconvert name with "," and remove " if present
        $string = StringReplace($string, ",", "*", 1) ; since I can't use stringsplit() i need different separators
        $string = StringReplace($string, ",", "#", 1) ; since I can't use stringsplit() i need different separators
        $forsale = StringRegExp($string, "#(.*?),", 1) ; for sale
        If $forsale[0] > 0 Then
            $k +=1
            $string = StringReplace($string, ",", "_", 1) ; since I can't use stringsplit() i need different separators
            $platform = StringRegExp($string, "_(.*?),", 1) ;platform
            $rating = StringRegExp($string, ",(.*?),", 1) ;rating
            If $platform[0] <> "PC" And $platform[0] <> "DS" And $platform[0] <> "Wii" And $platform[0] <> "XB" And $platform[0] <> "X3" And $platform[0] <> "Junk" Then
                $rating[0] = "Hardware" ; fixed value
                $platform[0] = "N" ; fixed value
            EndIf
            If $platform[0] = "Junk" Then
                $rating[0] = $platform[0]
                $platform[0] = "None"
            EndIf
            $new = StringRight($string, 2) ; just lasr 2 chars for last item in row
            If $new = "es" Then $new = "Yes"
            ReDim $csv_list[$k + 1][7]
            $csv_list[$k][0] = $name
            $csv_list[$k][1] = $platform[0]
            If $rating[0] <> "@" Then $csv_list[$k][2] = $rating[0]
            $csv_list[$k][3] = $new
            $csv_list[$k][4] = $forsale[0]
            ; now I build the string to check vs the main db (I need a partial search)
            $string = "<z:row c0='" & _normalize_db($csv_list[$k][0]) & "' Rating='" & $csv_list[$k][2] & "' New='" & $csv_list[$k][3] & "' Platform='" & $csv_list[$k][1] ; _normalize_db just change exotic chars into xml_readable sequence
            $check_string = _ArraySearch($mydb, $string, 39, UBound($mydb), 0, 1) ; first 38 lines is the xml header so I can skip
            If $check_string <> -1 Then $csv_list[$k][5] = _sellp($check_string) ; I can have 2 possible prices in main DB, best price and street price. If bestprice = 0 I'll use StreetPrice
        EndIf
    Next
    ConsoleWrite("Time to complete: " & TimerDiff($begin) & @CRLF)
EndFunc   ;==>_exp2db_x
 
Func _cleanfordb($k)
    If StringInStr($k, "^") Then $k = StringReplace($k, "^", ", ") ; ^
    If StringInStr($k, '"') Then $k = StringMid($k, 2, StringLen($k) - 2)
    Return $k
EndFunc   ;==>_cleanfordb
 
Func _sellp($cc)
    $p = StringRegExp($mydb[$cc], "c6='(.*?)' c7",1)
    If $p[0] = 0 Then $p = StringRegExp($mydb[$cc], "c7='(.*?)'/>",1)
    Return $p[0]
EndFunc   ;==>_sellp

Time to complete is 137,000ms, 2'17'', for 1,243 lines of csv (and 30,000 lines of main DB)

I need to work, during normal phases, with 10,000 to 30,000 lines so time to complete will be really too high.

I ask you, great programmers, if there is a way to optimize this routine (i'm not able to modify the csv since I get it this way from the Store Application

Thanks!

M.

Edited by marko001

Share this post


Link to post
Share on other sites
AdamUL

Have a look at this I have used the functions in it many time when working with CSV files.

Adam

Share this post


Link to post
Share on other sites
Melba23

marko001,

Try this and see if it is faster: ;)

#include <Array.au3> ; Just for display

; Simulate reading the file into an array
Global $aLines[7] = [ _
            'Game Title,Quantity,For Sale,Platform,Rating,Number,New', _
            'After Life,5,2,PC,PG1,23332,Yes', _
            'Max Payne,4,0,Wii,PG3,109,No', _
            'Fifa 2009,11,2,DS,PG2,976,Yes', _
            '"Hellgate, London",3,2,PC,PG2,112,No', _
            'Logitech Mouse,12,4,mouse, , ,No', _
            'Pinball 2010,1,1,PS3,PG1,065,No']

For $i = 0 To UBound($aLines) - 1

    ; Locate the first separator comma
    $iFirst_Comma = StringInStr($aLines[$i], ",", 2, -6)
    ; Create an array from the remaining sections
    $aParts = StringSplit(StringMid($aLines[$i], $iFirst_Comma + 1), ",")
    ; Repalce the count with the first section
    $aParts[0] = StringMid($aLines[$i], 1, $iFirst_Comma - 1)
    ; Display the result
    _ArrayDisplay($aParts)
    ; You can now use the array as necessary

Next

I had to add another blank field to the "Logitech Mouse line - I assume that the real file has the correct number of delimiters in each line? :graduated:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
marko001

@ Melba23: Nope, this is a problem. I can just see that once I have the file, but it comes as you see from the original program.

If the product is not a boxed game title the .csv is written that way

Game Title,Quantity,For Sale,Platform,Rating,Number,New

Logitech Mouse,12,4,mouse, ,No

Title Not Imported, 0,0,Junk, ,No

That's for I use

[font=arial,helvetica,sans-serif]          If $platform[0] <> "PC" And $platform[0] <> "DS" And $platform[0] <> "Wii" And $platform[0] <> "XB" And $platform[0] <> "X3" And $platform[0] <> "Junk" Then
                $rating[0] = "Hardware" ; fixed value
                $platform[0] = "N" ; fixed value
            EndIf
            If $platform[0] = "Junk" Then
                $rating[0] = $platform[0]
                $platform[0] = "None"
            EndIf[/font]

Share this post


Link to post
Share on other sites
marko001

@ Adam: _ParseCSV() works (see post I made to Melba) except the (damn) fact that some lines of the .csv are "irregular"

How can I manage them?

Share this post


Link to post
Share on other sites
marko001

$csv_array = _ParseCSV($exportfile)
    For $i = 1 To UBound($csv_array) - 1
        if $csv_array[$i][3] = "Junk" Then
            $csv_array[$i][3] ="None"      
            $csv_array[$i][4] = "Junk"      
            $csv_array[$i][6] = "No"    
     elseIf $csv_array[$i][3] <> "PC" And $csv_array[$i][3] <> "Wii" And $csv_array[$i][3] <> "DS" And $csv_array[$i][3] <> "XB" And $csv_array[$i][3] <> "X3" Then
            $csv_array[$i][3] ="N"          
            $csv_array[$i][4] = "Hardware"      
            $csv_array[$i][6] = "No"    
        EndIf
    Next

It's a bit tricky but solves it and allows me to use _ParseCSV()

Share this post


Link to post
Share on other sites
Melba23

marko001,

If the csv lines are "regularly irregular ( by which I mean that if there is a single blank field it should really be 2) then we could do this: ;)

#include <Array.au3> ; Just for display

; Simulate reading the file into an array
Global $aLines[7] = [ _
            'Game Title,Quantity,For Sale,Platform,Rating,Number,New', _
            'After Life,5,2,PC,PG1,23332,Yes', _
            'Max Payne,4,0,Wii,PG3,109,No', _
            'Fifa 2009,11,2,DS,PG2,976,Yes', _
            '"Hellgate, London",3,2,PC,PG2,112,No', _
            'Logitech Mouse,12,4,mouse, ,No', _
            'Pinball 2010,1,1,PS3,PG1,065,No']

For $i = 0 To UBound($aLines) - 1

    ; Add the missing comma if required <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    $sLine = StringReplace($aLines[$i], ", ,", ", , ,")
    ; Locate the first separator comma
    $iFirst_Comma = StringInStr($sLine, ",", 2, -6)
    ; Create an array from the remaining sections
    $aParts = StringSplit(StringMid($sLine, $iFirst_Comma + 1), ",")
    ; Repalce the count with the first section
    $aParts[0] = StringMid($sLine, 1, $iFirst_Comma - 1)
    ; Display the result
    _ArrayDisplay($aParts)
    ; You can now use the array as necessary

Next

Any use? :graduated:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
marko001

Melba. first of all let me say that I always liked your smileys here and there :graduated:

You always put the proper smiley in the right position ...

Then yes, this solves and dramatically reduce first part. (I'll edit later the post to report timing)

What is then terrific is this:

$string = "<z:row c0='" & _normalize_db($csv_array[$i][0]) & "' Rating='" & $csv_array[$i][4] & "' New='" & $csv_array[$i][6] & "' Platform='" & $csv_array[$i][3] ; _normalize_db just change exotic chars into xml_readable sequence
            $check_string = _ArraySearch($mydb, $string, 39, UBound($mydb), 0, 1) ; first 38 lines is the xml header so I can skip
            If $check_string <> -1 Then $csv_price[$i][0] = _sellp($check_card) ; I can have 2 possible prices in main DB, best price and street price. If bestprice = 0 I'll use StreetPrice (_sellp() is in first post)
; I also created after the _ParseCSV() a new array with dim $csv_price[ubound($csv_array)][2]

This solution gives me 2 arrays but index is the same so I don't mind merging them, I can use them anyway. Problem is that _arraysearch() is soooo slow and I can't use _arraybinarysearch() since I need a partial search (,1) and I don't know exact lenght of $check_string (game titles vary)

Share this post


Link to post
Share on other sites
BrewManNH

You could modify the _ArrayBinarySearch function to do a partial search by using StringInString, look at the way that _ArraySearch does the partial searches for hints on how to go about it.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
marko001

INCREDIBLE! Simply incredible .... from 173,000 ms to 278ms ! (1,000 records vs 40,000 lines of xml to check)

The updated _arraybinarysearch_ext() is this one. If someone feel it interesting it can be added to array.au3

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayBinarySearch_Ext
; Description ...: Uses the binary search algorithm to search through a 1-dimensional array.
; Syntax.........: _ArrayBinarySearch(Const ByRef $avArray, $vValue[, $iStart = 0[, $iEnd = 0][, $iPartial = 0]])
; Parameters ....: $avArray  - Array to search
;                 $vValue   - Value to find
;                 $iStart   - [optional] Index of array to start searching at
;                 $iEnd  - [optional] Index of array to stop searching at
;               $iPartial - [optional] If set to 1, executes a partial search
; Return values .: Success - Index that value was found at
;                 Failure - -1, sets @error to:
;                 |1 - $avArray is not an array
;                 |2 - $vValue outside of array's min/max values
;                 |3 - $vValue was not found in array
;                 |4 - $iStart is greater than $iEnd
;                 |5 - $avArray is not a 1 dimensional array
; Author ........: Jos van der Zande <jdeb at autoitscript dot com>
; Modified.......: Marko001 - Added Partial Search
; Remarks .......: When performing a binary search on an array of items, the contents MUST be sorted before the search is done.
;                 Otherwise undefined results will be returned.
; Related .......: _ArrayFindAll, _ArraySearch
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ArrayBinarySearch_Ext(Const ByRef $avArray, $vValue, $iStart = 0, $iEnd = 0, $iPartial = 0)
    If Not IsArray($avArray) Then Return SetError(1, 0, -1)
    If UBound($avArray, 0) <> 1 Then Return SetError(5, 0, -1)
 
    Local $iUBound = UBound($avArray) - 1
 
    ; Bounds checking
    If $iEnd < 1 Or $iEnd > $iUBound Then $iEnd = $iUBound
    If $iStart < 0 Then $iStart = 0
    If $iStart > $iEnd Then Return SetError(4, 0, -1)
 
    Local $iMid = Int(($iEnd + $iStart) / 2)
 
    If $avArray[$iStart] > $vValue Or $avArray[$iEnd] < $vValue Then Return SetError(2, 0, -1)
 
    ; Search
    If Not $iPartial Then
        While $iStart <= $iMid And $vValue <> $avArray[$iMid]
            If $vValue < $avArray[$iMid] Then
                $iEnd = $iMid - 1
            Else
                $iStart = $iMid + 1
            EndIf
            $iMid = Int(($iEnd + $iStart) / 2)
        WEnd
    Else
        While $iStart <= $iMid And StringInStr($avArray[$iMid], $vValue) = 0
            If $vValue < $avArray[$iMid] Then
                $iEnd = $iMid - 1
            Else
                $iStart = $iMid + 1
            EndIf
            $iMid = Int(($iEnd + $iStart) / 2)
        WEnd
    EndIf
 
    If $iStart > $iEnd Then Return SetError(3, 0, -1) ; Entry not found
 
    Return $iMid
EndFunc   ;==>_ArrayBinarySearch_Ext
Edited by marko001

Share this post


Link to post
Share on other sites
BrewManNH

I'll probably add that, or something similar to my version of _ArrayBinarySearch, just because it's missing a key piece of functionality without being able to do a partial search. The version I have is a 2D version of _ArrayBinarySearch which will search a specific subitem/"column" of a 1 or 2D array.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Melba23

marko001,

173,000 ms to 278ms

Now that it what I call a speed increase! :graduated:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
marko001

I tried now with 24,700 records (vs. 40,000 lines of .xml):

Conversion finished (it took 6.81 secs)

I slightly modified the script with redim($csv_array[ubound($csv_array)][8] so I can add prices directly in the array. Some lines have price = "" (but I expected it, no problem)

I added also (after the previous timing test)

$idx = UBound($csv_array)
    For $x = $idx - 1 to 1 Step -1 ; delete lines with empty prices
        If $csv_array[$X][7]= "" Then
            _ArrayDelete($csv_array,$x)
            $idx -=1
        EndIf
    Next

this sucks, it moves the timedifference to 127,000 secs.

since I now throw the array in an INSERT to fill a remote DB it's maybe better skip lines during the insert phase? Or can I clean the array in a faster way?

Share this post


Link to post
Share on other sites
Melba23

marko001,

ReDim is one of the slowest AutoIt functions, so avoid it if you can. I would strongly recommend ignoring any lines that do not have a price during the early parsing phase and just not entering them into the array rather than deleting them later. :graduated:

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
Spiff59

I wonder if a StringRegExp() might get rid of a lot of the gyrations you had to put in the program because StringSplit() can't skip over a delimiter within quotes. I'm sure someone could improve upon this:

#include <Array.au3>
$string = '"Hell' & Chr(39) & 's Gate, London",3,2,PC,PG2,112,No'
MsgBox(1,"",$string)
$array = StringRegExp($string, "((?:[^,\x22]|\x22[^\x22]*\x22)+)", 3)
_ArrayDisplay($array)

Share this post


Link to post
Share on other sites
marko001

@Melba

I'll take a look at the whole script to follow your hint with redim, to remove it where possible.,

Relating your proposal, I simply skipped lines when creating the query for the insert:

[b]If $csv_array[$x][7] > 0 Then[/b]
                    $query_part2 &= "('','" & shopname
                    $query_part2 &= "','" & _readyforquery($csv_array[$x][0]) ; just to return me '' if the name contains a ' (otherwise query messes up)
                    $query_part2 &= "','" & $csv_array[$x][3]
                    $query_part2 &= "','" & $csv_array[$x][4]
                    $query_part2 &= "','" & $csv_array[$x][6]
                    $query_part2 &= "','" & $csv_array[$x][2]
                    $query_part2 &= "','" & $csv_array[$x][7]
                    $query_part2 &= "','" & $csv_array[$x][8]
                 $query_part2 &= "'),"
                EndIf

Edit: 11 14 secs to execute the query (2 queries of batch_size of 1,000 each) on 1,500 records.

Query it's optimized at maximum (i think...)

Phew, this is the end (of this part...)

Edited by marko001

Share this post


Link to post
Share on other sites
czardas

Sorry for bumping an old thread. I have just made a CSV to array function, although I may be able to make some improvements. It hasn't really been tested for speed or anything, so I don't know how it compairs. It does read incomplete rows, and ignores inline commas and parses double quotes as expected. It only uses Redim when a larger number of items occur within a row, which is normallly only once, since it reads the column headings first.

Edited by czardas

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  

  • Similar Content

    • mistersquirrle
      By mistersquirrle
      Hello!
       
      I wrote myself a script to follow Google Maps Polyline encoding steps: https://developers.google.com/maps/documentation/utilities/polylinealgorithm, and that works (although I think that it's a bit janky), but now I'm having issues getting the output.
       
      When I run the script, all the points come out correctly in the console, and even when they're the only things that I log, it displays them fine. However, I'm adding each point into a variable to return all of them at once at the end, fully formatted, and it's only taking the very first point. I can't figure out what I'm doing wrong, as it seems fine.
       
      When run with the default value, it should output this at the end: Custom Polygon: _p~iF~ps|U_ulLnnqC_mqNvxq`@
      But instead I'm just getting this: Custom Polygon: _p~iF
       
      I know that it's reaching the string combination lines because it's logging the data before it (and even if a put log AFTER the $sPolygon &= $aPoints[0], it's logged fine).
       
      Here's my full code (problem is lines ~209 - 234, search "$sPolygon &= $aPoints[1]"):
      #include <Array.au3> #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> _PolyGUI() Func _PolyGUI() #Region ### START Koda GUI section ### Form= $hInputGUI = GUICreate("Lat Long encoder", 403, 301, 192, 124) GUISetFont(8, 400, 0, "Consolas") GUICtrlCreateLabel("Input polygon points here, format as:", 8, 8, 263, 19) GUICtrlSetFont(-1, 10, 800, 0, "Consolas") GUICtrlCreateLabel("Lat Long - Single point", 8, 24, 142, 17) GUICtrlCreateLabel("Lat Long, Lat Long, Lat Long - Multiple points", 8, 40, 280, 17) Local $sPoints = GUICtrlCreateEdit("", 8, 64, 385, 201, BitOR($ES_WANTRETURN, $WS_VSCROLL)) GUICtrlSetData(-1, "38.5 -120.2, 40.7 -120.95, 43.252 -126.453") GUICtrlSetFont(-1, 10, 400, 0, "Consolas") $bOK = GUICtrlCreateButton("bOK", 16, 272, 123, 25) GUICtrlSetFont(-1, 12, 800, 0, "Consolas") $bCancel = GUICtrlCreateButton("bCancel", 304, 272, 75, 25) GUICtrlSetFont(-1, 12, 800, 0, "Consolas") GUISetState(@SW_SHOW, $hInputGUI) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $bCancel Exit Case $bOK $sPoints = GUICtrlRead($sPoints) GUISetState(@SW_HIDE, $hInputGUI) _GetPoly($sPoints, True) ExitLoop EndSwitch Sleep(10) WEnd EndFunc ;==>_PolyGUI ;https://developers.google.com/maps/documentation/utilities/polylinealgorithm ;https://app.dsmobileidx.com/api/DescribeSearchForLinkId?linkId=469787 ; Note that this will only really work inside the US (this side of the World), as it's assuming any negative is the Longitude ;https://gist.github.com/ismaels/6636986 - decoder ;Using: 41.83162 -87.64696 ; Expected: sfi~F np}uO ; Actual: sfi~f np}uo ; If we remove 32 from the last ASCII code, since the last bit chunk is 0, we get the correct case/ characters ; We need to run this logic back through all the indexes though and do this to all that that <= 63 ;LinkId=469787 ; Expected: q{`aHpa_iVi[kp@}`Aa{@e[eCoqBbAyc@iRy{@g_@mz@|gA{eAh~@Vf~Etv@gB~p@gQ`^yg@~p@ekAldA{KfFxIrJ^pO~Mtl@dPrJnUz[nSpo@wf@fc@yw@n@ob@ ; Actual: s{`aHpa_iVg[kp@}`Aa{@g[gCmqBbA{c@iRy{@e_@kz@|gA{eAh~@Td~Evv@gB|p@gQb^wg@|p@ekAndA{KfFvIpJ`@rO~Mrl@dPrJnU|[lSpo@wf@dc@yw@n@mb@ ; I assume that this is because of bad data, the points have repeating 9's and 0's, which looks fishy. The polygon is (very) close, but not quite the same. Func _GetPoly($sPoints, $bLog = False) Local $timer = TimerInit(), $sConsole[11] Local $sPolygon = "" ; Step 1, take the initial signed value: Local $aCoords = StringRegExp($sPoints, "(-*?\d*\.\d*) (-*?\d*\.\d*)", 3), $aPoints[2] ;~ _ArrayDisplay($aCoords) If $bLog Then _Log(_ArrayToString($aCoords)) For $c = 0 To (UBound($aCoords) - 1) Step 2 ;~ If $bLog Then _Log($c) If $c = 0 Then $aPoints[0] = $aCoords[$c] $aPoints[1] = $aCoords[$c + 1] Else $aPoints[0] = $aCoords[$c] - $aCoords[$c - 2] $aPoints[1] = $aCoords[$c + 1] - $aCoords[$c - 1] EndIf If $bLog Then _Log("- Step 1, take the initial signed value:") _Log(" " & $aPoints[0]) _Log(" " & $aPoints[1]) EndIf ; Step 2, multiply each by 1e5, and round $aPoints[0] = Round($aPoints[0] * 1e5, 0) $aPoints[1] = Round($aPoints[1] * 1e5, 0) If $bLog Then _Log("- Step 2, multiply each by 1e5, and round") _Log(" " & $aPoints[0]) _Log(" " & $aPoints[1]) EndIf ; Step 3, convert Decimal to Binary, using two's complement for negatives. Padded to 32 bits $aPoints[0] = _NumberToBinary($aPoints[0]) $aPoints[1] = _NumberToBinary($aPoints[1]) If $bLog Then _Log("- Step 3, convert Decimal to Binary, using two's complement for negatives. Padded to 32 bits") _Log(" " & $aPoints[0]) _Log(" " & $aPoints[1]) EndIf ; Step 4, left-shifted 1 bit $aPoints[0] = StringTrimLeft($aPoints[0], 1) & "0" $aPoints[1] = StringTrimLeft($aPoints[1], 1) & "0" If $bLog Then _Log("- Step 4, left-shifted 1 bit") _Log(" " & $aPoints[0]) _Log(" " & $aPoints[1]) EndIf ; Step 5, if negative, invert binary If $c = 0 Then If $aCoords[$c] < 0 Then $aPoints[0] = _InvertBinary($aPoints[0]) If $aCoords[$c + 1] < 0 Then $aPoints[1] = _InvertBinary($aPoints[1]) Else If $aCoords[$c] - $aCoords[$c - 2] < 0 Then $aPoints[0] = _InvertBinary($aPoints[0]) If $aCoords[$c + 1] - $aCoords[$c - 1] < 0 Then $aPoints[1] = _InvertBinary($aPoints[1]) EndIf If $bLog Then _Log("- Step 5, if negative, invert binary") _Log(" " & $aPoints[0]) _Log(" " & $aPoints[1]) EndIf Local $aChunks[2][6], $0x20 For $i = 0 To 1 $0x20 = "1" ; This is out BitOR flag, 0x20 BitOR'd onto our 5-bit chunks is always 1*****, except the last chunk $sConsole[5] = "" ; Clearing console variables $sConsole[6] = "" $sConsole[7] = "" $sConsole[8] = "" $sConsole[9] = "" For $j = 0 To 5 ;There will always be 6 chunks ; Step 6 & 7, break into 5-bit chunks, and reverse order $aChunks[$i][$j] = StringTrimLeft($aPoints[$i], StringLen($aPoints[$i]) - 5) ; This splits into 5-bit chunks in reverse order, doing 6 & 7 in one operation ;~ If $bLog Then _Log(" " & $aPoints[$i]) ;~ If $bLog Then _Log(" " & StringLen($aPoints[$i])) ;~ If $bLog Then _Log(" " & StringTrimLeft($aPoints[$i], StringLen($aPoints[$i]) - 5)) ;~ If $bLog Then _Log(" " & $aChunks[$i][$j]) ; Here we consume the original binary string, so the next loop gets the correct next 5-bit chunk $aPoints[$i] = StringTrimRight($aPoints[$i], 5) $sConsole[5] &= $aChunks[$i][$j] & " " ; Once consumed, if the remaining length isn't enough for another bit chunk, switch 0x20 to 0 (no following chunks) If StringLen($aPoints[$i]) <= 5 Then $0x20 = "0" ; Step 8, BitOR 100000 (0x20) to our 5-bit chunks (effectively) $aChunks[$i][$j] = $0x20 & $aChunks[$i][$j] $sConsole[7] &= $aChunks[$i][$j] & " " ; Step 9, converting the chunk from Binary back to Decimal $aChunks[$i][$j] = _BinaryToDec($aChunks[$i][$j]) $sConsole[8] &= $aChunks[$i][$j] & " " ; Step 10, adding 63 to decimal values $aChunks[$i][$j] += 63 $sConsole[9] &= $aChunks[$i][$j] & " " If StringLen($aPoints[$i]) < 5 Then ExitLoop Next If $bLog Then _Log("- Step 6 & 7 (part " & $i & "), break into 5-bit chunks, and reverse order") _Log(" " & $sConsole[5]) _Log("- Step 8 (part " & $i & "), BitOR 100000 (0x20) to our 5-bit chunks (effectively)") _Log(" " & $sConsole[7]) _Log("- Step 9 (part " & $i & "), converting the chunk from Binary back to Decimal") _Log(" " & $sConsole[8]) _Log("- Step 10 (part " & $i & "), adding 63 to decimal values") _Log(" " & $sConsole[9]) EndIf Next Local $aASCII[0] For $i = 0 To 1 Dim $aASCII[0] ; Reset ASCII array For $j = 0 To (UBound($aChunks, 2) - 1) ; For both chunk sets ReDim $aASCII[UBound($aASCII) + 1] ; Add an index for the ASCII array If $aChunks[$i][$j] = "" Or $aChunks[$i][$j] <= 63 Then ; If the chunk is not useful $l = $j For $k = $l To 1 Step -1 If $aChunks[$i][$k] = "" Or $aChunks[$i][$k] <= 63 Or $aASCII[$k] <= 63 Then $aASCII[$k - 1] -= 32 If $aASCII[$k - 1] <= 63 Then _ArrayDelete($aASCII, $k - 1) Else ExitLoop EndIf Next ExitLoop EndIf $aASCII[$j] = Int($aChunks[$i][$j]) Next ;Step 11, convert each value to ASCII equivalent For $k = UBound($aASCII) - 1 To 0 If $aASCII[$k] <= 63 Or $aASCII[$k] = "" Then ReDim $aASCII[UBound($aASCII) - 1] Else ExitLoop EndIf Next $aPoints[$i] = StringFromASCIIArray($aASCII, 0, -1, 0) Next If $bLog Then _Log("- Step 11, convert each value to ASCII equivalent, finished") If $aCoords[$c] <= 0 Then ;@CRLF & " " & If $bLog Then _Log($aPoints[1]) _Log($aPoints[0]) _Log("Next set") EndIf $sPolygon &= $aPoints[1] $sPolygon &= $aPoints[0] Else If $bLog Then _Log($aPoints[0]) _Log($aPoints[1]) _Log("Next set") EndIf $sPolygon &= $aPoints[0] $sPolygon &= $aPoints[1] EndIf Next If $bLog Then _Log("Custom Polygon: " & $sPolygon) _Log(TimerDiff($timer) & @CRLF) EndIf Return $sPolygon EndFunc ;==>_GetPoly Func _NumberToBinary($iNumber) Local $sBinString = "" ; Maximum 32-bit # range is -2147483648 to 2147483647 If $iNumber < -2147483648 Or $iNumber > 2147483647 Then Return SetError(1, 0, "") ; Convert to a 32-bit unsigned integer. We can't work on signed #'s $iUnsignedNumber = BitAND($iNumber, 0x7FFFFFFF) ; Cycle through each bit, shifting to the right until 0 Do $sBinString = BitAND($iUnsignedNumber, 1) & $sBinString $iUnsignedNumber = BitShift($iUnsignedNumber, 1) Until Not $iUnsignedNumber ; Was it a negative #? Put the sign bit on top, and pad the bits that aren't set If $iNumber < 0 Then Return '1' & StringRight("000000000000000000000000000000" & $sBinString, 31) ; Always return 32 bit binaries If StringLen($sBinString) < 32 Then Return StringRight("0000000000000000000000000000000" & $sBinString, 32) Return $sBinString EndFunc ;==>_NumberToBinary Func _BinaryToDec($sBinary) Local Const $aPower[8] = [128, 64, 32, 16, 8, 4, 2, 1] Local $iDec If StringRegExp($sBinary, "[0-1]") Then If StringLen($sBinary) < 8 Then Do $sBinary = "0" & $sBinary Until StringLen($sBinary) = 8 EndIf $aBinary = StringSplit($sBinary, "", 2) For $i = 0 To UBound($aBinary) - 1 ;~ $aBinary[$i] = $aBinary[$i] * $aPower[$i] $iDec += $aBinary[$i] * $aPower[$i] Next Return $iDec Else Return SetError(0, 0, "Not a binary string") EndIf EndFunc ;==>_BinaryToDec Func _InvertBinary($iNumber) ;~ ConsoleWrite(@CRLF & $iNumber) Local $sNumber $aNumber = StringSplit($iNumber, "") For $i = 1 To $aNumber[0] If $aNumber[$i] = 0 Then $aNumber[$i] = 1 ElseIf $aNumber[$i] = 1 Then $aNumber[$i] = 0 Else Return SetError(0, 0, "Not a binary number") EndIf $sNumber &= String($aNumber[$i]) Next Return $sNumber EndFunc ;==>_InvertBinary Func _Log($data) ;~ Local Static $LogEnable = True ConsoleWrite(@CRLF & @HOUR & ":" & @MIN & "." & @SEC & " " & $data) LogData(@HOUR & ":" & @MIN & "." & @SEC & " " & $data, "logs/LOGFILE.txt") EndFunc ;==>_Log Func LogData($text, $File = "logs/LOGFILE.txt") Global $LogFile = "" If $LogFile = "" Then $LogFile = FileOpen($File, 9) OnAutoItExitRegister(CloseLog) EndIf FileWriteLine($LogFile, $text) EndFunc ;==>LogData Func CloseLog() If $LogFile <> "" Then _Log("Closing LoD script" & @CRLF) FileClose($LogFile) EndIf EndFunc ;==>CloseLog  
      I've tried:
      $sPolygon &= $aPoints[0] & $aPoints[1] ;---- $sPolygon = $sPolygon & $aPoints[0] & $aPoints[1] ;---- $sPolygon = $sPolygon & String($aPoints[0] & $aPoints[1]) ;---- $sPolygon = String($sPolygon) & String($aPoints[0]) & String($aPoints[1]) ;---- $sPolygon &= $aPoints[1] $sPolygon &= $aPoints[0] ;----  
      I'm sure it's something basic that I'm overlooking, but I don't understand why it's not combining the strings. 
      Also, unrelated, why doesn't $LogFile = FileOpen($File, 9) create the directory/ file if they don't exist? 9 should be $FO_CREATEPATH (8) + $FO_APPEND (1)?
      Thanks!
    • VollachR
      By VollachR
      Hi,
      I'm looking for a way to take a number value from a Row2 of a 2D array and according to this check if files that appear in rows 3-11 in the array exists.
      For example, if the number in Row2 is 5 I need to check for the files in Row 3-6 only, if it is 6 than rows 3-7 and so on.
      I thought on using a FOR loop but I have very little experience with those.
      Can you suggest the best way to do what I need?
      BTW, the files in Rows 3-11 will usually have blank value for any row above the number in Row2 (e.g. Row2 = 5 so Rows3-6 will have values but 8-11 be empty), The values I need are in Column 1 of the array, the name of the key from the INI file that the array was created from is in Column 0.
      Full Example:
      Row2 of Array:
      Col0 = Games# - Col1 = 5
      Rows3-6
      Col0 = Exe2 - Col1 = Path To File
      Col0 = Exe3 - Col1 = Path To File
      Col0 = Exe4 - Col1 = Path To File
      Col0 = Exe5 - Col1 = Path To File
      I need that if Row2 is 5 to check these above for rows if the file exists, if it was 6 then the next row as well and so on up until number 10 in Row2 as it can't go above 10.
      So basically for whatever number in Row2 from 2-10 need to check 1-9 rows from 3-11 to see if the files in Col1 exists and if any of them don't exist it should call a function that shows an error message.
      I'm pretty sure I have the first line of the for look correct:
      For $i = 1 To $aAIO[2][1] Just not sure how to continue from there, also not sure if $i should be equal 1 or 2.
      Help will be appreciated.
    • FMS
      By FMS
      Hello,
      I'm trying to get data from twitter to an array and so far I found an Twitter UDF whish lookes very intresting but couldn't get it to work.
      It lookes not supported any more(2010) and buggy when i read all te replies.
      More around this subject (autoit and twitter) i couldn't find on this forum.
      Is there sombody who know's a good way to get live data from twitter to an array inside autoit?
      (I kinda doubt that this isn't tackled before)
      In the end I was hoping to get all tweets from date to date from an specific subject inside a 2D array to work whit.
    • AndreasNWWWWW
      By AndreasNWWWWW
      I got a question:  i am trying to run different functions based upon what i select in these radio buttons.(code below)
      it needs to check server 1. then run function 1 or function 2 after what i selected in the checkbox.
      once that function is done it moves to the next one, until it has been trough all 5 
       
      iv'e tried using while loops with different while $i equals to something but then i manualy need to go in and edit the script every time.
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 615, 437, 192, 124) $Server2 = GUICtrlCreateLabel("Server2", 216, 95, 41, 17) $server1 = GUICtrlCreateLabel("Server1", 216, 72, 41, 17) $server4 = GUICtrlCreateLabel("Server4", 216, 144, 41, 17) $server3 = GUICtrlCreateLabel("Server3", 216, 119, 41, 17) $server5 = GUICtrlCreateLabel("Server5", 216, 170, 41, 17) $Start = GUICtrlCreateButton("Start", 240, 248, 147, 25) $Checkbox1 = GUICtrlCreateCheckbox("function1", 288, 72, 97, 17) $Checkbox2 = GUICtrlCreateCheckbox("function2", 392, 72, 97, 17) $Checkbox3 = GUICtrlCreateCheckbox("function1", 288, 96, 97, 17) $Checkbox4 = GUICtrlCreateCheckbox("function2", 392, 96, 97, 17) $Checkbox5 = GUICtrlCreateCheckbox("function1", 288, 120, 97, 17) $Checkbox6 = GUICtrlCreateCheckbox("function2", 392, 120, 97, 17) $Checkbox7 = GUICtrlCreateCheckbox("function1", 288, 144, 97, 17) $Checkbox8 = GUICtrlCreateCheckbox("function2", 392, 144, 97, 17) $Checkbox9 = GUICtrlCreateCheckbox("function1", 288, 170, 97, 17) $Checkbox10 = GUICtrlCreateCheckbox("function2", 392, 170, 97, 17) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit EndSwitch WEnd  
    • careca
      By careca
      This is another take on string triggers, triggers on specific strings.
      Able to simple text pasting,
      opening links (as long as there's a www. http:\\ or https:\\ at the beggining)
      and is able to open applications.
      The user selects the modifier key, and then uses a combination of that key with a couple others to perform tasks like
      screenshot the active window, (modkey + prtscr), turn off the screen (modkey + pause / break),
      open clipboard string in registry (modkey + R), change system volume (modkey + arrouw up/dn).
      The following keys pressed at the same time prompt for shutdown: S+D+T
      The following keys pressed at the same time prompt for restart: S+R+T
      Middle mouse button click on titlebar minimizes to tray, or a left mouse button click in the icon in the tray also minimizes.
      Trigger is set off by space or enter, and timeouts after 3 seconds.
      Shows your external, lan, and gateway ip's, can refresh with right mouse click, and opens the default browser if the correspondent button is pressed.
      Able to change system volume by a set percentage, reading from the inputbox the number the user sets, if 0 or empty uses system default.
      I made this because the existing string trigger applications didn't do it for me for a number of reasons.
      I did this for me, but if someone finds it useful all the better.
×