importing a list of numbers from excel

mott3510

I am new with python and programming in general so I am sorry if what I am about to say doesn't make any sense. I have several different lists of numbers in excel and I would like to save each individual list as something, eg. MyHighs. As of right now I have just put in the numbers my self as shown below...

My_High = (95, 96, 97, 95, 91, 93, 98, 97, 99, 101, 100, 100)
Actual_High = (100, 99, 95, 97, 91, 91, 102, 99, 100, 99, 99, 99)

I have those numbers listed in excel and I would like to save that list of numbers as My_High and Actual_High. However, I am not sure how you would import the excel file and then save the list of numbers of something. I appreciate any helpful hints!!

bvdet

Let's say you have two columns in your worksheet. The first column is labeled My_High (cell A-1) and the second Actual_High (cell B-1). The numbers follow in the rows below. The easiest thing to do is save the worksheet as a csv (comma separated value) file and read the file with a python script. Save the data as a dictionary. Example code:

Expand|Select|Wrap|Line Numbers

 f = open('high.csv')

dd = {}

keys = f.readline().strip().split(',')

for key in keys:

    dd.setdefault(key, [])
 
for line in f:

    for i, item in enumerate(line.strip().split(',')):

        dd[keys[i]].append(int(item))
 
f.close()
 
for key in keys:

    print "%s: %s" % (key, dd[key])

Output:

Expand|Select|Wrap|Line Numbers

 >>> My_High: [95, 96, 97, 95, 91, 93, 98, 97, 99, 101, 100, 100]

Actual_High: [100, 99, 95, 97, 91, 91, 102, 99, 100, 99, 99, 99]

>>>

mott3510

I put in the code you gave me and as an output I got this...

99: [ ]
98: [ ]
755555543cleaclear: [ ]

Not sure what any of that means or why I wasn't getting my list of numbers to show up. Thanks for all your help, I really appreciate it.

bvdet

You have other data in your worksheet and it is not formatted the way I described. The CSV file should look like this for my code to work:

My_High,Actual_High
95,100
96,99
97,95
95,97
91,91
93,91
98,102
97,99
99,100
101,99
100,99
100,99

If you could show me how your data looks, I might be able to suggest something.

mott3510

This is how my excel sheet looks. My Highs is in column A and Actual Highs is in column B. Hope this makes sense. Thanks.

My Highs Actual Highs
98 99
99 99
100 99
100 100
99 99
101 102
91 91
92 91
97 97
96 95
98 99
100 100
97 95
86

bvdet

Your CSV file should look like this:

My Highs,Actual Highs
98,99
99,99
100,99
100,100
99,99
101,102
91,91
92,91
97,97
96,95
98,99
100,100
97,95
86

My code produces the following output:

Expand|Select|Wrap|Line Numbers

 
>>> My Highs: [98, 99, 100, 100, 99, 101, 91, 92, 97, 96, 98, 100, 97, 86]

Actual Highs: [99, 99, 99, 100, 99, 102, 91, 91, 97, 95, 99, 100, 95]

>>>

Open your CSV file and look at the contents.

mott3510

When I open my file, verification.csv in excel it looks like what i posted above. When I open that same file in notepad it looks exactly like yours. In my code I have...f = open("verification.csv")...I then tried saving it as a .txt file in notepad but I get the same output.

bvdet

Are you saving the excel file (xls extension) from the File menu with Save As and selecting CSV as the "Save as type"?

mott3510

I am saving it as CSV (Comma delimited). Other CSV choices are...CSV (MS-DOS) and CSV (Macintosh). However, I do not have a mac and I also tried saving it as CSV (MS-DOS) but that did not work either.

bvdet

I cannot explain the problem you are having. I have an application that uses a similar method of reading spreadsheet data to input structural steel building columns into steel detailing software. The CSV file looks like this:

Grid,ColSize,Grade,BottElev,TopElev,Rotation,BaseP late
E-2,W10x33,A992,-3'-4,26'-2 1/2,0,BP6
E-3,W10x33,A992,-3'-4,26'-7 7/8,90,BP6
E-3.8,W10x33,A992,-3'-4,33'-3 3/4,90,BP6
E-4.6,W10x33,A992,-3'-4,33'-8 1/8,90,BP6
E-5.7,W10x33,A992,-3'-4,34'-2 1/16,90,BP6
.....................

I can assure you, it works. Try entering the complete path:

f = open("C:\\dir1\\dir2\\verification.csv")