Menu
  • HOME
  • TAGS

How to download a .xls into a Python object via FTP with Python ftplib

Tag: python,excel,python-2.7,ftp

I am new to Python, so forgive me if I am asking a wrong question. I have a .xls that I would like to ftp and save it as a Python object. Currently, I am taking an awkward approach where [Step 1] I first ftp the file and save it as .txt (I know .xls->.txt is a bad idea!), and then [Step 2] open and save that .txt file in Python shell. That is,

Step 1:

## Import the necessary module
from ftplib import FTP

ftp = FTP('webftp.vancouver.ca')
ftp.login()
ftp.cwd('OpenData/xls')
filename = 'new_food_vendor_locations.xls'
ftp.retrbinary('RETR %s' % filename, open('myLovelyNewFile.txt', 'w').write)
ftp.quit()

This allows me to create a new file 'myLovelyNewFile.txt' in my current directly. In my Python shell, I have:

Step 2:

myLovelyPython = open('myLovelyNewFile.txt','r')
for line in myLovelyPython.readlines():
     print line
myLovelyPython.close()

Although Python can run all these command lines, the printed lines do not have correct inputs due to the forceful conversion from xls to txt. Furthermore, I would like to save the ftp-ed object directly as some python object (rather than first saving it as .txt outside Python). Is there easy solution to this problem?

Best How To :

Although I was looking for a way to directly purse the ftp-ed excel file into Python object, if I allows saving the excel file in my directory and re-read it again, the following works:

ftp = FTP('webftp.vancouver.ca')
ftp.login()
ftp.cwd('OpenData/xls')
filename = 'new_food_vendor_locations.xls'
ftp.retrbinary('RETR %s' % filename, open('myLovelyNewFile.xls', 'w').write)
ftp.quit()
workbook = xlrd.open_workbook('myLovelyNewFile.xls')
worksheet = workbook.sheet_by_name('Query_vendor_food')
num_rows = worksheet.nrows - 1
curr_row = -1
while curr_row < num_rows:
    curr_row += 1
    row = worksheet.row(curr_row)
    print row

(I thank @jacoppens for his advice.)

How to remove structure with python from this case?

python,python-2.7

It's complicated to use regex, a stupid way I suggested: def remove_table(s): left_index = s.find('<table>') if -1 == left_index: return s right_index = s.find('</table>', left_index) return s[:left_index] + remove_table(s[right_index + 8:]) There may be some blank lines inside the result....

In sklearn, does a fitted pipeline reapply every transform?

python,scikit-learn,pipeline,feature-selection

The pipeline calls transform on the preprocessing and feature selection steps if you call pl.predict. That means that the features selected in training will be selected from the test data (the only thing that makes sense here). It is unclear what you mean by "apply" here. Nothing new will be...

how to enable a entry by clicking a button in Tkinter?

python,tkinter

You need to use the configure method of each widget: def rakhi(): entry1.configure(state="normal") entry2.configure(state="normal") ...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

excel

A boolean would most likely not yield better performance than integers, since the Excel formula engine is dynamically typed. To significantly improve the performance of your spreadsheet, you should probably consider other options. Excel PowerPivot comes to mind, as it can easily handle millions of records with hundreds of calculations,...

Find the tf-idf score of specific words in documents using sklearn

python,scikit-learn,tf-idf

Yes. See .vocabulary_ on your fitted/transformed TF-IDF vectorizer. In [1]: from sklearn.datasets import fetch_20newsgroups In [2]: data = fetch_20newsgroups(categories=['rec.autos']) In [3]: from sklearn.feature_extraction.text import TfidfVectorizer In [4]: cv = TfidfVectorizer() In [5]: X = cv.fit_transform(data.data) In [6]: cv.vocabulary_ It is a dictionary of the form: {word : column index in...

Python - Opening and changing large text files

python,replace,out-of-memory,large-files

You need to read one bite per iteration, analyze it and then write to another file or to sys.stdout. Try this code: mesh = open("file.mesh", "r") mesh_out = open("file-1.mesh", "w") c = mesh.read(1) if c: mesh_out.write("{") else: exit(0) while True: c = mesh.read(1) if c == "": break if c...

The event loop is already running

python,python-3.x,pyqt,pyqt4

I think the problem is with your start.py file. You have a function refreshgui which re imports start.py import will run every part of the code in the file. It is customary to wrap the main functionality in an ''if __name__ == '__main__': to prevent code from being run on...

Spring-integration scripting with Python

python,spring-integration,jython

This is a bug in Spring Integration; I have opened a JIRA Issue. if (variables != null) { result = scriptEngine.eval(script, new SimpleBindings(variables)); } else { result = scriptEngine.eval(script); } When the first branch of the if test is taken, the result variable is added to the SimpleBindings object, and...

SQLAlchemy. 2 different relationships for 1 column

python,sqlalchemy

I'm afraid you can't do it like this. I suggest you have just one relationship users and validate the insert queries.

Comparing cell contents against string in Excel

string,excel,if-statement,comparison

We need an Array formula. In G2 enter: =NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0))) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note: The curly brackets that appear in the Formula Bar should not be typed....

How do I read this list and parse it?

python,list

Your list contains one dictionary you can access the data inside like this : >>> yourlist[0]["popularity"] 2354 [0] for the first item in the list (the dictionary). ["popularity"] to get the value associated to the key 'popularity' in the dictionary....

Sort when values are None or empty strings python

python,list,sorting,null

If you want the None and '' values to appear last, you can have your key function return a tuple, so the list is sorted by the natural order of that tuple. The tuple has the form (is_none, is_empty, value); this way, the tuple for a None value will be...

Pandas - Dropping multiple empty columns

python,pandas

You can just subscript the columns: df = df[df.columns[:11]] This will return just the first 11 columns or you can do: df.drop(df.columns[11:], axis=1) To drop all the columns after the 11th one....

group indices of list in list of lists

python,list

Use collections.OrderedDict: from collections import OrderedDict od = OrderedDict() lst = [2, 0, 1, 1, 3, 2, 1, 2] for i, x in enumerate(lst): od.setdefault(x, []).append(i) ... >>> od.values() [[0, 5, 7], [1], [2, 3, 6], [4]] ...

odoo v8 - Field(s) `arch` failed against a constraint: Invalid view definition

python,xml,view,odoo,add-on

You have made silly mistake in defining _columns. _colums is not valid dictionary name for fields structure. Replace this by _columns and restart service and update module. ...

How to check for multiple attributes in a list

python,python-2.7

You can create a set holding the different IDs and then compare the size of that set to the total number of quests. The difference tells you how many IDs are duplicated. Same for names. Something like this (untested): def test_quests(quests): num_total = len(quests) different_ids = len(set((q.ID for q in...

trying to understand LSH through the sample python code

python,similarity,locality-sensitive-hash

a. It's a left shift: https://docs.python.org/2/reference/expressions.html#shifting-operations It shifts the bits one to the left. b. Note that ^ is not the "to the power of" but "bitwise XOR" in Python. c. As the comment states: it defines "number of bits per signature" as 2**10 → 1024 d. The lines calculate...

How does the class_weight parameter in scikit-learn work?

python,scikit-learn

First off, it might not be good to just go by recall alone. You can simply achieve a recall of 100% by classifying everything as the positive class. I usually suggest using AUC for selecting parameters, and then finding a threshold for the operating point (say a given precision level)...

Python Popen - wait vs communicate vs CalledProcessError

python,python-2.7,error-handling,popen

about the deadlock: It is safe to use stdout=PIPE and wait() together iff you read from the pipe. .communicate() does the reading and calls wait() for you about the memory: if the output can be unlimited then you should not use .communicate() that accumulates all output in memory. what...

Displaying a 32-bit image with NaN values (ImageJ)

python,image-processing,imagej

The display range of your image might not be set correctly. Try outputImp.resetDisplayRange() or outputImp.setDisplayRange(Stats.min, Stats.max) See the ImagePlus javadoc for more info....

Excel - select a cell based on adjacent cell value

excel

The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in col B. (2) is easy, but (1) requires a way to match the value in B on both...

Count function counting only last line of my list

python,python-2.7

I don't know what you are exactly trying to achieve but if you are trying to count R and K in the string there are more elegant ways to achieve it. But for your reference I had modified your code. N = int(raw_input()) s = [] for i in range(N):...

Pandas Dataframe Complex Calculation

python,python-2.7,pandas,dataframes

I believe the following does what you want: In [24]: df['New_Col'] = df['ActualCitations']/pd.rolling_sum(df['totalPubs'].shift(), window=2) df Out[24]: Year totalPubs ActualCitations New_Col 0 1994 71 191.002034 NaN 1 1995 77 2763.911781 NaN 2 1996 69 2022.374474 13.664692 3 1997 78 3393.094951 23.240376 So the above uses rolling_sum and shift to generate the...

Python recursive function not recursing

python,recursion

Afraid I don't know much about python, but I can probably help you with the algorithm. The encoding process repeats the following: multiply the current total by 17 add a value (a = 1, b = 2, ..., z = 26) for the next letter to the total So at...

Sum of two variables in RobotFramework

python,automated-tests,robotframework

By default variables are string in Robot. So your first two statements are assigning strings like "xx,yy" to your vars. Then "evaluate" just execute your statement as Python would do. So, adding your two strings with commas will produce a list: $ python >>> 1,2+3,4 (1, 5, 4) So you...

represent an index inside a list as x,y in python

python,list,numpy,multidimensional-array

According to documentation of numpy.reshape , it returns a new array object with the new shape specified by the parameters (given that, with the new shape, the amount of elements in the array remain unchanged) , without changing the shape of the original object, so when you are calling the...

Replace nodejs for python?

python,node.js,webserver

You might want to have a look at Tornado. It is well-documented and features built-in support for WebSockets. If you want to steer clear of the Tornado-framework, there are several Python implementations of Socket.io. Good luck!...

Django: html without CSS and the right text

python,html,css,django,url

Are you using the {% load staticfiles %} in your templates?

12 Characters Including leading and following zeros

excel

User the formula =LEFT(TEXT(A1,"+0.000000000;-0.000000000"),12) where A1 contains your number. This will be a text cell type but will format the cell exactly how you want it....

How to put an image on another image in python, using ImageTk?

python,user-interface,tkinter

Just use photoshop or G.I.M.P.. I assure you, doing it that way will be much simpler and less redundant than essentially getting Tkinter to photo edit for you (not to mention what you're talking about is just bad practice when it comes to coding) Anyways, I guess if you really...

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

Python: can't access newly defined environment variables

python,bash,environment-variables

After updating your .bashrc, perform source ~/.bashrc to apply the changes. Also, merge the two BONSAI-related calls into one: export BONSAI=/home/me/Utils/bonsai_v3.2 UPDATE: It was actually an attempt to update the environment for some Eclipse-based IDE. This is a different usecase altogether. It should be described in the Eclipse help. Also,...

How do variables inside python modules work?

python,module,python-module

The convention is to declare constants in modules as variables written in upper-case (Python style guide: https://www.python.org/dev/peps/pep-0008/#global-variable-names). But there's no way to prevent someone else to re-declare such a variable -- thus ignoring conventions -- when importing a module. There are two ways of working around this when importing modules...

sys.argv in a windows environment

python,windows,python-3.x

You are calling the script wrong Bring up a cmd (command line prompt) and type: cd C:/Users/user/PycharmProjects/helloWorld/ module_using_sys.py we are arguments And you will get the correct output....

Identify that a string could be a datetime object

python,regex,algorithm,python-2.7,datetime

What about fuzzyparsers: Sample inputs: jan 12, 2003 jan 5 2004-3-5 +34 -- 34 days in the future (relative to todays date) -4 -- 4 days in the past (relative to todays date) Example usage: >>> from fuzzyparsers import parse_date >>> parse_date('jun 17 2010') # my youngest son's birthday datetime.date(2010,...

How to use template within Django template?

python,html,django,templates,django-1.4

You can use the include tag in order to supply the included template with a consistent variable name: For example: parent.html <div class="row"> <div class="col-md-12 col-lg-12 block block-color-1"> {% include 'templates/child.html' with list_item=mylist.0 t=50 only %} </div> </div> child.html {{ list_item.text|truncatewords:t }} UPDATE: As spectras recommended, you can use the...

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...

Python: histogram/ binning data from 2 arrays.

python,histogram,large-files

if you only need to do this for a handful of points, you could do something like this. If intensites and radius are numpy arrays of your data: bin_width = 0.1 # Depending on how narrow you want your bins def get_avg(rad): average_intensity = intensities[(radius>=rad-bin_width/2.) & (radius<rad+bin_width/2.)].mean() return average_intensities #...

Strange Behavior: Floating Point Error after Appending to List

python,python-2.7,behavior

Short answer: your correct doesn't work. Long answer: The binary floating-point formats in ubiquitous use in modern computers and programming languages cannot represent most numbers like 0.1, just like no terminating decimal representation can represent 1/3. Instead, when you write 0.1 in your source code, Python automatically translates this to...

Inserting a variable in MongoDB specifying _id field

python,mongodb,pymongo

Insert only accepts a final document or an array of documents, and an optional object which contains additional options for the collection. db.collection.insert( <document or array of documents>, { // options writeConcern: <document>, ordered: <boolean> } ) You may want to add the _id to the document in advance, but...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

SyntaxError: invalid syntax?

python,syntax

Check the code before the print line for errors. This can be caused by an error in a previous line; for example: def x(): y = [ print "hello" x() This produces the following error: File "E:\Python\test.py", line 14 print "hello" ^ SyntaxError: invalid syntax When clearly the error is...

Twilio Client Python not Working in IOS Browser

javascript,python,ios,flask,twilio

Twilio developer evangelist here. Twilio Client uses WebRTC and falls back to Flash in order to make web browsers into phones. Unfortunately Safari on iOS supports neither WebRTC nor Flash so Twilio Client cannot work within any browser on iOS. It is possible to build an iOS application to use...

Create an exe with Python 3.4 using cx_Freeze

python,python-3.4,cx-freeze

Since you want to convert python script to exe have a look at py2exe

Excel VBA Loop Delete row does not start with something

excel,excel-vba

It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...

How to change the IP address of Amazon EC2 instance using boto library

python,amazon-web-services,boto

Make sure you have set properly with ~/.boto and connect to aws, have the boto module ready in python. If not, go through this first: Getting Started with Boto For example, you need assign a new EIP 54.12.23.34 to the instance i-12345678 Make sure, EIP has been allocated(existed) and you...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

Calling function and passing arguments multiple times

python,function,loops

a,b,c = 1,2,3 while i<n: a,b,c = myfunction(a,b,c) i +=1 ...

Peewee: reducing where conditionals break after a certain length

python,peewee

Try ...where(SomeTable.BIN.in_(big_list)) PeeWee has restrictions as to what can be used in their where clause in order to work with the library. http://docs.peewee-orm.com/en/latest/peewee/querying.html#query-operators...

Using counter on array for one value while keeping index of other values

python,collections

To count how often one value occurs and at the same time you want to select those values, you'd simply select those values and count how many you selected: fruits = [f for f in foods if f[0] == 'fruit'] fruit_count = len(fruits) If you need to do this for...