SEARCH

How-To Geek

Import Text into Excel 2007

Nothing equals Excel for crunching of numbers, but what if your data isn’t already in an Excel spreadsheet? It’s as simple as using the Text Import Wizard.

Open a blank or existing Excel 2007 spreadsheet.  Click the Data tab on the Ribbon and choose “From Text”.

Choose the text file you want to import and double click. This will open the Text Import Wizard.  Choose your data type and which row you want to start the data at and click next.

 

Step 2 of the Wizard allows you to manage the break lines between your data. 

Step 3 of the Wizard allows you to choose column formats, provides data preview, and other advanced settings.  When everything is organized click on Finish.

A final window will be displayed asking where you wish to put the data into the sheet.  Here you can see I chose A6.  Click OK…

Your Text Data will now be input into the Excel sheet!

Brian Burgess worked in IT for 10 years before pursuing his passion for writing. He's been a tech blogger and journalist for the past seven years, and can be found on his about me page or Google+

  • Published 05/28/07

Comments (55)

  1. Tiago Maymone

    In step 3 of the Text Import Wizard in Excel 2007 (SP1 included), if you
    click the «Advanced» button and change the settings, and then click OK, IT
    SIMPLY IGNORES YOUR CHANGES. If you click again on the «Advanced» button,
    you’ll notice the settings are wholly unchanged.
    So it’s impossible to instruct Excel 2007 to consider dots as decimal
    separators, for instance. It will simply ignore it.
    Does anyone know if there’s a turnaround, or some fix Microsoft has issued,
    or anything?

    Thanks

    TM

  2. JonhyJack

    Exactly the same problem with Excel 2007 SP1. We have an ERP that exports data in txt files with (dot’s) for decimal and (colon’s) for numeric separator. Our regional settings for Portugal are exactly reversed, and when we try to import it in Excel 2007 SP1 it just simples ignores the Advance Text Import Settings, I tried with Excel 2003 SP3 no problem.

    Same question: Does anyone know if there’s a fix ou patch from Microsoft that could fix this?

    Thanks
    JJ

  3. mysticgeek

    Not that I know of off hand … but I will look into this for you guys!

  4. Hau

    Thanks for the tip! I couldn’t find this in the 2007 until now. Definately saves me a few hour’s worth of work.

  5. RK61

    The only work-around I ahve found is to change your number separators in Regional Setting sin Control Panel. Import the text file then change your Regional Settings back. this works but is a pain when it worked just fine in Office 2003

  6. Nicander Lauwaars

    Hi Guys, can anyone give me an update on this one? Is there already a bugfix available? Thanks, Nic.

  7. Lagrange

    How can it be that Microsoft does not provide a bugfix for this when we are already beyond SP1? It is a critical issue for multi-country corporate use. Very strange!

  8. Taniwha

    I have csv files that I want to open with Excel (2007). the data show in one column, I know how to change it to see each field in a column, but how do I make Excel do this permanently?? I don’t want to have to convert each csv file each time I open it with Excel

  9. Dennis Olsen

    I continually need to import files that have 87 fields with a total of 1667 characters. How can I import the same format every time without inserting the field breaks in the same places repeatedly?

  10. Paul Frederick

    I have basically the same question as Dennis Olsen, only with a bigger data set. I am importing approx 900 1 digit fields. If I can’t find a way to save the import, I will have to find another way.

  11. Chris

    Can you scan a document directly into Excel 2007? Is it possible to insert a Word document into Excel 2007?

  12. Dennis Olsen

    Paul – I found a way to solve my problem. I recorded a macro to do the import. It took me about 30 minutes to set up the macro and now takes me about 10 seconds to import the files versus 20 minutes to do it manually. First, I got the Developer tab to show up in Excel. I set up a standard directory and standard file name to import. Then I clicked on Record Macro and went through all of the steps. Now I copy the file I want to download into that standard directory, rename the file to the name the macro is expecting, and run the macro. I should have done this about 20 imports ago. :-)

  13. Deb

    I’ve been all over this MS website and can’t find it– All I want to know is how many text characters I can put in a cell! Excel 2007, Excel 2003, Excel 95. Thank you Anybody!

  14. kenny

    No updates on this bug?

  15. Peter

    How can i import from an external text file that contains more row than the excel work sheet?
    ihave import as much as the worksheet can contain but have problem in importing the remaining row

  16. Ted Martin

    Good example. What is the best way to import data from QIF files?

  17. kenny

    I don’t think this is a bug after all. Since I have found some computers to cope quite well with thext imports.
    I just extracted the office key under HKEY CURRENT USER from one of the working computers to those where it wasn’t and it works just fine now.

  18. Michael

    I have to do this for each and every one of my text files? I asked how to import multiple text docs. Are we saying here multiple, but one at a time? Is there no way to import all text documents in a folder into one excel sheet?

  19. Don

    How do I import files that contain specific text and append to a worksheet then move to the next group and append to another worksheet within the same workbook?

    Random TXT Example: xx_01.txt, xx_02.txt, xx_03.txt, xx_04.txt, xx_05.txt, ……. xx_99.txt
    using cmd | findstr ” Sample #1 test ” and group the same worksheet

    xx_01.txt : findstr=” Sample #1 test ”
    xx_14.txt : findstr=” Sample #1 test ”
    xx_39.txt : findstr=” Sample #1 test “

  20. Kelly

    EXCELLENT! That was so easy it wasn’t even funny! THANK YOU!

  21. IVAN

    How do I import an outlook address book to exel file. I want to use this for a mail merge application

  22. Greg

    When importing text, is there any way to retain spaces? Excel always strips them off.

  23. NB

    Is it possible to post up the VB code for your import?
    I’m trying to create a macro, creating a button, click, then import my *.txt file into Excel, then run these steps…

    1.) Open Excel, then open your log file. This should bring up the file import dialog.

    2.) Select “Delimited”, Start Import @ row 3, and “Windows (ANSI)” as the file origin.

    3.) For delimiters, add a check next to “Space” and keep the one next to “Tab”.

    4.) Click Finish and your data will be imported properly.

  24. Ed

    I have 240 text files (dxdiag reports) that I would like to put into an excel spreadsheet. I do not need all of it but would like to have about nine different lines from the file placed in cells with one row per file (computer). Is this possible or do I have to take it to access via macros or some type of script? Thanks for any suggestions.

  25. Greg

    Thanks NB, it is actually a text file with variable length fields and no delimiters. I am trying to make a macro to import the file with the correct column breaks so I don’t have to go through the text file format and click on the same line breaks each time, but I also want to retain spaces.
    Is their any way to retain spaces using the fixed length column breaks?
    i.e. file format has field 1 being 5 bytes and field two having 10 bytes, etc with no delimiter

  26. BROMCHIT

    how can i sink the imported signatures and rubber stamping to be behind the existing lines, what command to use as in WORDS we use BEHIND TEXT command. Will highly appreciate your help. Thanks and regards, Bromchit.

  27. TL

    I pasted some text and then ran “Text to Columns”. Excel interpreted some of the data as correctly as time, but I am working in small time fractions and it rounded to 10th of a second. If I clear formatting pr treat as a number, the original value is gone. Problem 1!
    So, I decide to delete and start over, using the decimal as a separator., since I only care about the fractional seconds. Now, whenever I paste the original text, Excel tries to save me by re-interpreting it as columns, even when I start a new spreadsheet. Does anyone know of a way to stop Excel from ‘helping’?

  28. Bruce Cullinan

    Can I Scan a document with words and numbers into Excel and work on them

  29. Felipe

    Felipe

    I have similar problem to import data.

    How can i import from an external text file that contains more row than the excel work sheet?
    ihave import as much as the worksheet can contain but have problem in importing the remaining row

  30. nafiseh

    hello. if you know how wa can change the text import setting please help me. for e.g. instead fixed width, delimited was selected in setting.
    thanks

  31. Nata

    Hi. It seems I have got a problem with importing data – I do everything according to the instruction, but numeric data is not recognized: “decimal and thousand separators” are set correctly but there is no result.

    For example, I have to import “2,250.10-” into “-2250,10″ but this cannot be achieved.
    Could anyone help me with this question?

  32. Jake

    I have the same question as Michael (March 5, 2009)

    How do you import multiple txt data (x, y) data without doing it one by one? For example, it works when i go through the import wizard for the first txt file, but then I have to click on the next column and click the same buttons again in order for it to import all the data into the same worksheet.

  33. Nabil

    I have another question here, ” how to import CSV data file and SKIP some rows?” my question is because, I’ve a generator program which keep history of ALL transactions, but I want it to not repeat some records (dates), so I want to modified the repeated data via my program so they will be easy to import it into excel without doing any extra steps, and without loosing data in the history file”.
    example of the history file:

    *DATE: 01/06/2010
    *TIME: 09:00:23 AM
    *USER: MyCo/NABIL
    Job01,StartTime01,EndTime01,Duration01,Comments01
    Job02,StartTime02,EndTime02,Duration02,Comments02
    Job03,StartTime03,EndTime03,Duration03,Comments03
    :
    *DATE: 01/07/2010
    *TIME: 09:00:23 AM
    *USER: MyCo/NABIL
    Job01,StartTime01,EndTime01,Duration01,Comments01
    Job02,StartTime02,EndTime02,Duration02,Comments02
    Job03,StartTime03,EndTime03,Duration03,Comments03
    :
    *DATE: 01/07/2010
    *TIME: 09:00:23 AM
    *USER: MyCo/NABIL
    Job01,StartTime01,EndTime01,Duration01,Comments01
    Job02,StartTime02,EndTime02,Duration02,Comments02
    Job03,StartTime03,EndTime03,Duration03,Comments03
    :

    As you can see, date 01/07/2010 is repeated, but in EXCEL must load the 2nd history for analysing by skipping the first history.

    how to modify the 1st history record so they will be SKIPPED by EXCEL IMPORTING?>>

    Thanks.

    For any further information contact me on nabilaal@gmail.com

    Thanks in advance.
    NABIL

  34. LabRat

    Hi,

    I want to import a .csv file into and existing excel worksheet with equations. I want the imported data to appear in specific columns to correspond with the existing equations. (For example, I want my data to appear in columns D-J, because my equations in columns K-M are based on those imported values.) Anyway, whenever I specify the columns when importing, excel adds extra columns. How do I do this correctly?

  35. LabRat

    Hi,

    I want to import a .csv file into an existing Excel worksheet with equations. I want the imported data to appear in specific columns to correspond with the existing equations. (For example, I want my data to appear in columns D-J, because my equations in columns K-M are based on those imported values.) How do I do this correctly?

  36. joan

    Can a pdf label list be scanned into an Excel worksheet and be edited?

  37. Jacqueline Denegal

    When I select Data on the ribbon, “From Text” is not a highlighted icon that I can select.

  38. Carol

    I cannot find building blocks in excel. Besides using a macro, is there any other form of auto text options to use in excel?

  39. nitin

    I want to convert the text format numbers into numeric format

  40. MGA

    I have some information on my computer that I want to “upload” or import into an excel worksheet….it’s a lot of information and it would take too much time to enter it individually into the excel worksheet which has over 2000 rows of information that I have to fill with figures….the information has to go into 2 different columns in excel. The information is found in another program on my computer…It’s an icon on the desktop….the file in that program is set up like an excel worksheet (with colums and rows but it is NOT excel). I think that the number of rows and columns are the same as the file in excel…Can anyone tell me if it is possible to import that specific information into the excel file and if yes how it can be done? Thank you.

  41. nick

    i have two spreadsheets one of them is the format for people to input their data into and the other i want to update automaticly for people to view. so as people input their data into the first spreadsheet the second will record thier information for others.

  42. Andrew

    How do I limit the number of file origin options in the text import wizard? I currently am offered 107 different choices and typically only chose “65001 : Unicode (UTF-8)”

  43. David

    I have 666 text files I need to convert to Excel. I need all the information contained in each transfered.
    The files contain a mix of numbers and strings, which cuts-out many Matlab codes involving dlmread etc etc.
    The file involves around 12 header lines, the 12th row being column headers – six columns in total with around 600rows in each (Column info including Integers, strings numbers etc). In addition, some of the files have a variable number of rows to complicate matters.

    One of the column headers contains a space! which voided one script file I found online.

    I do have a Matlab script which uses textread to convert every word/number/string etc into an array (about 4000 rows long!), I can reconstruct this back into the relevant cells of an excel sheet using a while loop to cycle through the rows (and excell cells). This process however, takes about 2hrs per file, so I think it will take about 23days of constant processing!

    I would like to keep my while-loop for the header region as I can do a string comparison to take care of the variable header row numbers.
    What I need is a script that can read just the column information at a specified location or row number and place the info in five diferent arrays whcih I can convert into a writable format.

    Or, can some form of macro operation (which I know nothing about) deal with this

  44. vikrant

    thanks a lot indeed..

  45. Joyce

    Thnks alot…really saved me alot of time..

  46. Diane

    I am trying to import a text file to Excel that is comma delimited but also has duoble quotes surrounding the alpha-numeric fields. The very first column is “I” and Excel strips both double quotes from it with the result of I,”abc”,”123″, etc. When I try to use Text to Columns it removes all of the double quotes.
    Using .CVS and .PRN doesn’t work either.
    How do I get Excel to retain all of my data?

  47. Kay Malm

    Excel 2007 does not allow you to move the column breaks. This used to be easier and now it’s harder.

  48. Pc Styler

    don´t work at all

  49. Evelien Snel

    Like so many Microsoft “features” all this is a pain in the “rear”.

    I would like to transfer some data into my Excel sheet.
    Some of it may indeed be a table, some of it may not.
    Some of it may be delimited, some may be fixed width.
    Some of it may be a poem, some may be tomorrow’s stock exchange rates. ;)

    But Excel remembers some other table I converted last week and is trying to impose last week’s rules on me again. I don’t want that! How can I simply paste my text the way it is and make my own decisions on how to handle it today?

  50. Prince Cess

    after i’ve clicked the Import, nothing appears in my ms excel . what should i do .

  51. Prasanna

    Thank u so much!

  52. Rachel

    Very urgent help needed!!!

    I have a huge txt file that I want to import into Excel. And the number of rows exceed Excel’s maximum row limit. When I tried to import anyway, there’s a prompting which says that the Text Import Wizard has a supposed function to import the remaining data into a second worksheet without duplicating data.

    The thing is I can’t find that functionality in Text Import Wizard :(

    Would appreciate kind help in this matter. I’m using Excel 2007.

  53. SAMIR

    THANKS

  54. SteveW

    Same issue Rachel is having, plus the import of .txt file is cutting off at 65,500 records, when Excel 2007 is supposed to be able to handle much longer databases. What’s happening?

  55. raveen kr tanwar

    wh
    EMKAY AUTO MOBILE INDUSTRIES (P) L TD
    39/7, N.H.-8, DELHI-JAIPUR HIGHWAY, VILLAGE BEGUMPUR KHATOLLA, GURGAON
    Party wise & Item wise Report
    Party Name :- THAI SUMMIT NEEL AUTO PVT.LTD.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
    Inv.No. Inv Dt Item Name Qty. Rate Val.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
    4984
    29/05/2010
    SPRING 3 OFF 927 A 1126- 01010117
    4000
    1.82
    7280
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    4000 7280.00
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    en we cont the text file into excel file all data show as below

Enter Your Email Here to Get Access for Free:

Go check your email!