akselo
Last Activity: Aug 10 '11, 08:30 PM
Joined: Sep 25 '07
Location: San Francisco
-
Thanks all for your help. I must say that it makes sense to just use the built-in split-to-list feature of strings and check each element. It seems pretty safe. However, it takes a long time to process a large dataset and check each value to see if it is a period, in this case the census numbers for the state of California. But efficiency doesn't matter that much in this case as releases are rare. -
To see if I understand--you are running an update query in access, or are you working in excel with linked data?
If you are working in access, you can use the IIF function in a nested way to account for each group (be sure to include the name of the field name in access and not the cell reference from excel), but I often find it is easier to insert a module and go with the select case statement , like so:
...Code:Function
Leave a comment:
-
Last column values are often 'period'. How about a pattern like
...Code:#line='ACSSF,2009e5,ca,000,0100,0013344,309,11,0,0,0,0,11,0,10,0,0,0,0,10,0,93,0,0,0,38,55,0,54,0,0,0,12,42,0,43,12,0,8,21,2,0,20,11,0,0,9,0,0,78,33,39,6,0,0,0,593,0,0,0,0,0,0,0,0,17,0,0,0,0,0,11,0,0,0,0,17,10,45,308,185,789200,909500,1000001,.,.,.,.,.,.,.,.,.,.,.,.,.,593,474,159,49,110,0,315,119,541772500,438522500,103250000,.,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,.,593,474,0,0,0,0,0,0,0,0,0,17,20,22,62,69,284,119,0,0,0,0,0,32,0,0,19,30,38,2909,4001,628,2035900,1966500,69300,593,474,49,48,91,81,51,49,8,39,58,0,119,77,42,0,0,0,0,0,0,0,0,19.4,23.0,7.7,593,0,0,0,0,0,0,0,41,0,0,0,10,31,0,358,144,81,44,31,58,0,194,163,0,7,8,16,0,593,0,0,0,32,0,19,30,21,17,0,37,22,415\n'
Leave a comment:
-
The option of searching for the literal is at one level compelling
But this doesn't work if the last item is a period and not a number as the replacement looks for a trailing comma. This could of course be handled separately, but I was hoping for a both general and clean solution...Code:# line='2.5,3,.,100,.,4.10,.,8,.,.,.,8.9,.' # line.replace('.,','0.0,') # '2.5,3,0.0,100,0.0,4.10,0.0,8,0.0,0.0,0.0,8.9,0.0'Leave a comment:
-
I like this one, but it doesn't like repeated period values, like so:
Code:patt = re.compile(r', ?. ?,') line='2.5,3,.,100,.,4.10,.,8,.,.,.,8.9,.' re.sub(patt,',0.0,',line) '2.5,0.0,.,100,0.0,4.10,0.0,8,0.0,.,0.0,8.9,.'
Leave a comment:
-
That's what I initially hoped, but it fails to replace all periods if several periods are adjacent because the last quote is seemingly used by the first match and skipped as the first quote in the next match. It accordingly skips every second period.
Code:line='2.5,3,.,100,.,4.10,.,8,.,.,.,8.9,.' line.replace(',.,',',') '2.5,3,100,4.10,8,.,8.9,.'Leave a comment:
-
Regex for replacing missing value period
I am parsing a csv data file with comma as the delimiter. The file mostly contains integers but also some decimal numbers/floats.
It has some missing values, which are marked by a period ("."). In order to insert into a database, I want to replace the missing value periods, but NOT the decimal periods.
With python's regex flavor and the re.sub construct applied to each line read as a string, can someone help what... -
Problem solved by moving strsql to after for K=1 to 176....Leave a comment:
-
I would love to just run an update query, the reason that doesn't seem to work in this case is that the number of records to update in each class depends on a value in another table determining how large the subset of each class will be updated. Now I have the problem that I wrap the recordset definition into a for k = 1 to 175...next expression where the k is used twice in the SQL string: first to filter only a given subset of records (stored in...Leave a comment:
-
Jim,
Efficiency is a concern, but I don't know any other way to select a subset using the TOP predicate besides re-creating the recordset. I don't think I can filter using TOP. Besides, if the recordset is defined from the onset, the traffic is limited to those x records, no?...Leave a comment:
-
I think I found out the problem.. I was looping the definition of a recordset, each time creating a new recordset for a new class (as based on a variable in the table) and performing calculations on that class. However, I closed and set the recordset object to nothing, so when the loop ran the next time, I got"Error No. 91: Description; Object variable or With block variable not set". Now at the end of the loop I don't set the recordset...Leave a comment:
-
I think the table you want a reference to is listed in line 16? At any rate, I eliminated the base 1 declaration and assumed 0 as the start, and the Subscript out of range Error is no more. Now I get "Error No. 91: Description; Object variable or With block variable not set". This does not come up as a runtime error, but rather as a text box without the debug option. Since I don't declare any objects, I don't see what the problem is. Any...Leave a comment:
-
Looping a recordset definition based on number from array
Hi folks,
I am working on a routine that will select a sample of parcels from a table. Each parcel belongs to a census tract, and depending on which tract, a certain calculation is applied. The trick is that the math is only to be performed on a random sample of parcels within each tract, and the size of the sample varies for each tract. Therefore, I define a recordset with a variable where the size of the sample is a value in an array... -
Well I should have seen that mistake had I paid attention. Now we know that I don't ;) With that fix, the run worked like a charm. THANKS!! For future reference, can you not pass an argument to recordset.filte r as I tried in my first effort? Is that passed strictly as sql within the quotes?...Leave a comment:
-
How can you be so fast, and then on a holiday?? At any rate, the function does populate the field, but does so with 0 for all records/values of lngTractCounter ??...Leave a comment:
-
Automatic Iterating through filtered subsets of a recordset
Hi there good folks,
I am trying to populate a column in a table with a number incrementing for each class (as set by another field; 176 different classes in table of 150,000 records representing parcels in a city), and then when a new class is present, restart the numbering until the next class and so on. The point is that I ultimately want to randomly select and apply changes to fields within each class, which is why I need the incrementing... -
This works and is awesome!! Thank you so much even if I can't see how dblTemp/varsqft(0) is assigned a value to begin with. It appears you suggest that it is better to deal with nulls in the source table rather than to make control flow workarounds in the code at runtime?...Leave a comment:
-
Yes, I have six numerical fields with square foot information for different landuse activities. I want the function to identify the largest value in any one row across the six fields and return that value. If I could perform a numerical operation on values in an array, that seems easier than 6 or so different nested if statements....Leave a comment:
-
Returning largest value from multiple columns using an array
Hello folks,
I have a function where I pass numbers from a table, and I want to compare them and return the largest horizontal value. I store them in an array, and try the DMAX function on it, but get stuck there.
...Code:Function landuse(dblmips As Double, dblcie As Double, dblmed As Double, _ dblpdr As Double, dblret As Double, dblvis As Double) Dim varsqft(5) As Double Dim varlargest As Double -
Thank you for the quick response. I have six fields with different square footage information for land use types (residential, commercial, office, etc), and hope to return the largest value for the parcel in question, and while I considered the if statement, this method seems cumbersome as I would need to test each field against all others and end up with 6 different nested if statements. Could one define an array of six columns and return the l...Leave a comment:
No activity results to display
Leave a comment: