Welcome, guest | Sign In | My Account | Store | Cart
# queryCSV.py
# FB - 201010111
# Query a CSV file.

# example CSV file: myData.csv
# id,code name,value
# 36,abc,7.6
# 40,def,3.6
# 9,ghi,6.3
# 76,def,99

# example query expression
query
= 'id > 36 and code_name == "def" and value <= 100'

print 'Query:'
print query
print

import csv
csvData
= csv.reader(open('myData.csv'))
csvTable
= []
isHeader
= True
for row in csvData:
   
if isHeader:
        isHeader
= False
        headerRow
= row
       
for i in range(len(headerRow)):
           
# replace spaces w/ underscores in column headers
            headerRow
[i] = headerRow[i].replace(' ', '_')
   
else:
        csvTable
.append(row)

# determine column types: string/int/float
colType
= []
for i in range(len(headerRow)):
    isFloat
= True
    isInt
= True
   
for j in range(len(csvTable)):
       
try:
            v
= float(csvTable[j][i])
           
if not v == int(v):
                isInt
= False
       
except ValueError:
            isFloat
= False
            isInt
= False

    colT
= ''
   
if isInt:
        colT
= 'int'
   
elif isFloat:
        colT
= 'float'
   
else:
        colT
= 'string'
    colType
.append(colT)

   
# print headerRow[i], colT
   
# run the query
for j in range(len(csvTable)):
   
# assign the column variables
   
for i in range(len(headerRow)):
       
if colType[i] == 'string':
           
exec(headerRow[i] + '=' + '"' + csvTable[j][i] + '"')
       
elif colType[i] == 'float':
           
exec(headerRow[i] + '=' + 'float("' + csvTable[j][i] + '")')
       
elif colType[i] == 'int':
           
exec(headerRow[i] + '=' + 'int("' + csvTable[j][i] + '")')

   
# output the rows matching the query
   
if eval(query):
       
print headerRow
       
print csvTable[j]
       
print

Diff to Previous Revision

--- revision 3 2010-10-08 19:34:33
+++ revision 4 2010-10-12 00:08:22
@@ -1,5 +1,5 @@
 
# queryCSV.py
-# FB - 201010085
+# FB - 201010111
 
# Query a CSV file.
 
 
# example CSV file: myData.csv
@@ -10,7 +10,7 @@
 
# 76,def,99
 
 
# example query expression
-query = 'int(id) > 36 and code_name == "def" and float(value) <= 100'
+query = 'id > 36 and code_name == "def" and value <= 100'
 
 
print 'Query:'
 
print query
@@ -18,20 +18,56 @@
 
 
import csv
 csvData
= csv.reader(open('myData.csv'))
-rowNum = 0
+csvTable = []
+isHeader = True
 
for row in csvData:
-    if rowNum == 0:
+    if isHeader:
+        isHeader = False
         headerRow
= row
-    else:
-        # assign the column variables
         
for i in range(len(headerRow)):
-            # replace spaces w/ underscores
+            # replace spaces w/ underscores in column headers
             headerRow
[i] = headerRow[i].replace(' ', '_')
-            exec(headerRow[i] + '=' + '"' +row[i] + '"')
-        # output the rows matching the query
-        if eval(query):
-            print headerRow
-            print row
-            print
+    else:
+        csvTable.append(row)
 
-    rowNum +=1
+# determine column types: string/int/float
+colType = []
+for i in range(len(headerRow)):
+    isFloat = True
+    isInt = True
+    for j in range(len(csvTable)):
+        try:
+            v = float(csvTable[j][i])
+            if not v == int(v):
+                isInt = False
+        except ValueError:
+            isFloat = False
+            isInt = False
+
+    colT = ''
+    if isInt:
+        colT = 'int'
+    elif isFloat:
+        colT = 'float'
+    else:
+        colT = 'string'
+    colType.append(colT)
+
+    # print headerRow[i], colT
+    
+# run the query
+for j in range(len(csvTable)):
+    # assign the column variables
+    for i in range(len(headerRow)):
+        if colType[i] == 'string':
+            exec(headerRow[i] + '=' + '"' + csvTable[j][i] + '"')
+        elif colType[i] == 'float':
+            exec(headerRow[i] + '=' + 'float("' + csvTable[j][i] + '")')
+        elif colType[i] == 'int':
+            exec(headerRow[i] + '=' + 'int("' + csvTable[j][i] + '")')
+
+    # output the rows matching the query
+    if eval(query):
+        print headerRow
+        print csvTable[j]
+        print

History