Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

How to get Output from this Program into Excel?

  1. Jun 28, 2017 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi All,
    I have been trying to teach myself programming recently (Python, mostly). I obviously still have a good way to go. User MarneMath was nice-enough to help me in this post a while back:
    :
    https://www.physicsforums.com/threads/classification-valuation-problem-baseball.893418/#post-5630986

    on scraping data using Python. Now, I ran it in Anaconda and it did compile. I made a small change to it , changing "somefilename" to "somefilename.csv" . Now, this may be very simple, but, what do I need to do in order to get an Excel file with the .csv format? I know the program created a file, but I am not sure how to export it into an Excel file. Any ideas?
    Thanks.
     
  2. jcsd
  3. Jun 28, 2017 #2
    It depends on which version of Excel.
    Open a blank worksheet. Select 'Data' (from the ribbon), 'From Text', then select the desired CSV file.

    There was something about Excel's CSV import in later versions that infuriated me, can't recall what, and in my opinion OpenOffice and LibreOffice do a better job of it.
     
  4. Jun 28, 2017 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Sorry, if this is dumb but how is Excel aware of the existence of the file, if the program , run in Anaconda, makes no mention of Excel? Will it recognize it by the name alone? EDIT: Never mind, thanks, Asymptotic.
     
  5. Jun 28, 2017 #4
    Let's see if I'm thinking about this correctly.
    You have created a CSV data file with the Python program, and want to load it into Excel.
    In that case, run Excel, and import the CSV as described above. If you want to end up with an XLS formatted file, then save it in that format.

    ... or do you want the Python program create an XLS file directly?
     
  6. Jun 28, 2017 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    The first is the case, thanks. Yes, I realized the import function would do it. Would Excel be able to do it directly without using Python, i.e., could I import an .html into Excel and save it as a .csv?
     
  7. Jun 28, 2017 #6
    I don't think so, if you are using Python to scrape data from a web page then writing it into columns within the CSV file.
     
  8. Jun 28, 2017 #7

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Excellent, thanks for everything.
     
  9. Jun 29, 2017 #8

    Borg

    User Avatar
    Science Advisor
    Gold Member

    @WWGD, I'm not sure from your posts if you realize that CSV stands for Comma Separated Values. As long as that is what you put in the file, Excel can natively read files with a .csv extension. Note that if the data in an individual cell has a comma in it, you need to surround that data with quotes. Excel will understand that as well.
     
  10. Jun 29, 2017 #9
    I have directed Excel from a Visual Basic program. I have never tried it with Python - but here's a website that describes that:
    http://pythonexcels.com/python-excel-mini-cookbook/
    You should be able to bypass the csv file altogether - going directly to the *.xls or *.xlsx..
     
  11. Jun 30, 2017 #10
    Excel allows the csv file to be imported as text through the wizard. In the wizard there are choices for how the data is delimitated. Select comma delimited and answer the questions as you click next within the wizard. Hope that helps
     
  12. Jul 3, 2017 #11

    harborsparrow

    User Avatar
    Gold Member

    Click on the file to open it in Excel. In the menus select Save As...
    and save the file. That's all.
     
  13. Jul 3, 2017 #12

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, but the file is not created " explicitly" that I can tell; what I did was just to use the import wizard.
     
  14. Jul 3, 2017 #13

    harborsparrow

    User Avatar
    Gold Member

    It seems I don't understand your question. Are you using office automation to animate Excel to do something?
     
  15. Jul 3, 2017 #14

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    No sorry, I think I am a bit confused myself, as a beginner in this general area. I am using Python to scrape data from a site and then store it in Excel. I ultimately want to stream data continuously from the website into cell phones, but this was just a practice step, to get my hands wet. Sorry for the confusion.
     
  16. Jul 3, 2017 #15

    StoneTemplePython

    User Avatar
    Gold Member

    In general I'd suggest taking a look at Pandas dataframes module for Python, when you're ready. (Basically excel spreadsheets inside python.)

    In the mean time you may want to check out "Automate the Boring Stuff", in particular, chapter 12 is about interfacing python and excel, and chapter 16 has stuff on automating text messages and emails, which is only slightly lower tech than what I think you're aiming for. (There's also chapters on reading and writing to files in general and web scraping and other stuff.)

    In any case the book is made freely available by the author here:

    https://automatetheboringstuff.com/

    all Python and explicitly targets people new to programming
     
  17. Jul 4, 2017 #16

    MarneMath

    User Avatar
    Education Advisor

    Is this question simply that you want to write the file to an excel?

    df.to_excel("something.xlsx")
     
  18. Jul 4, 2017 #17

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Yes, in part, and also general ideas/methods on scraping data how-to's.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: How to get Output from this Program into Excel?
Loading...