Shop OBEX P1 Docs P2 Docs Learn Events
Import numbers into Excel — Parallax Forums

Import numbers into Excel

MoskogMoskog Posts: 554
edited 2011-08-26 11:52 in General Discussion
Think I've seen a way to import data (numbers) from a simple .txt file into Excel spreadsheet but can't remember where or when. Does anyone have any ideas here? What kind of separators would be recommended, comma, dots or simply space?

A simple file could look like this, numbers separated with comma:
123,234,456
235,452,652
236,452,124

Comments

  • stamptrolstamptrol Posts: 1,731
    edited 2011-08-25 05:24
    A simple text file with commas as delimiters will be imported into excel ( or nearly any spreadsheet) with one number per column. Each CR moves down to the next row.

    In Excell, you can also choose another delimiter if you wish.

    Cheers,
  • max72max72 Posts: 1,155
    edited 2011-08-25 06:07
    When I have a similar file the best way it to open in notepad, select all, copy and paste in the openoffice calc (or libreoffice).
    With this approach i think openoffice/libreoffice is more intuitive and forgiving than excel.
    You could also try to use tab delimited in place of comma delimited.
    I usually substitute commas with tab using VI, also with windows, because it handles regexp fairly well (also for swapping columns, and similar tasks).
    Anyway even notepad could work.
    Massimo
  • Dave HeinDave Hein Posts: 6,347
    edited 2011-08-25 06:39
    KjellO,

    Save your comma-delimited file with a CSV extension and Excel will open it without any problems. If you save it with a TXT extension Excel will prompt you for information about the delimiter you used.

    Dave
  • Martin_HMartin_H Posts: 4,051
    edited 2011-08-25 06:45
    If the file name ends in ".csv" you can load it into Excel by double clicking on it. The file can then be saved as an Excel file, or copy and pasted into another Excel spreadsheet.
  • Duane DegnDuane Degn Posts: 10,588
    edited 2011-08-25 08:15
    Moskog,

    I don't have Excel on this computer but I'm pretty sure the "import" feature is accessible from the "file" menu.

    By default Excel uses tabs as delimiters. You'll need to check "comma" as a delimiter.

    For some reason, Excel wouldn't open the file correctly if it was saved as a .csv file. (I know of several other forum member who had the same problem.)

    Duane
  • Dave HeinDave Hein Posts: 6,347
    edited 2011-08-25 08:47
    Duane,

    If you do a "Save As" in Excel can you select the "CSVComma delimted)*.csv" option? If so, you should be able to open CSV files in Excel. Maybe your file associations aren't set up for CSV files.

    Dave
  • Duane DegnDuane Degn Posts: 10,588
    edited 2011-08-25 10:47
    Dave,

    If the csv file is created by Excel, then Excel can open them just fine.

    I've had trouble with files created by the Propeller on a SD card using commas (ASCII 44) to separate fields and carriage returns (ASCII 13) to separate rows.

    I mention the "import" trick because it worked for me and several people on the forum who also were having trouble with Propeller generated csv files.

    It is very likely, myself and the other forum members were doing something wrong with the way we created the csv files.

    Excel would open the csv file with all the data from one row crammed into a single cell. (As I said, I wasn't the only one who had this problem.)

    I don't use Excel much any more. I'm still not sure what the root problem was with the csv files. Do they need some sort of special header? (Personally, I'm not really worried about it.)

    I know when I suggested importing txt files to at least two other forum members, their files opened the way they were expecting them to in Excel.

    Duane
  • ctwardellctwardell Posts: 1,716
    edited 2011-08-25 11:09
    Duane Degn wrote: »
    I've had trouble with files created by the Propeller on a SD card using commas (ASCII 44) to separate fields and carriage returns (ASCII 13) to separate rows.

    You should use LF (ASCII 10) or CRLF (ASCII 13 + ASCII 10) as the line (row) seperator.

    C.W.
  • MoskogMoskog Posts: 554
    edited 2011-08-25 22:27
    Ok, just rename file to .csv with comma as delimiter didn't work but I could start Excel and then do a Open File and choose .txt file. A few instructions later, including checking comma as delimiter, and I had the numbers nice into the spreadsheet.
    Thanks a lot to all of you!
  • FranklinFranklin Posts: 4,747
    edited 2011-08-26 11:52
    You may not have .csv files associated with excel although it is pretty standard.
Sign In or Register to comment.