Importing text files in an Excel sheet

Comments

All comments about this page:

Comment by: Kevin (11-3-2010 10:18:56) deeplink to this comment

Thanks this is very informative.

Comment by: Christopher McGath (11-3-2010 13:52:57) deeplink to this comment

Is there any way to combine a range from an Excel file into another Excel file? Conversely, is there any way to extract a range from an Excel file into a new file? (Other than copying and pasting?)

Comment by: Greg (28-4-2010 08:24:37) deeplink to this comment

In Excel 2010, where is the "Fill down formulas in columns adjacent to data" option. Is it not available in 2010 external data properties any more?

Comment by: Jan Karel Pieterse (28-4-2010 10:18:54) deeplink to this comment

Hi Greg,

If you look at section "Refresh your data", below "Excel 2007/2010", you'll see a screenshot of the ribbon showing how to refresh the data. On that same screenshot, there is a properties button which takes you to that external data properties dialog.

Comment by: Joe (4-5-2010 01:38:21) deeplink to this comment

Hi, thanks for the tip. I managed to import most of the data into excel 2003, however, the data is too large and not all of it is being imported. Is there any way to get it to continue importing into the next tab?

Comment by: Jan Karel Pieterse (5-5-2010 07:36:03) deeplink to this comment

Hi Joe,

In the import wizard, you can enter a row where the import should start, but the number you can enter there is restricted to a max of 32767, not very helpful.
Is this a one-off job?

Comment by: Joe (8-5-2010 06:23:50) deeplink to this comment

Hi Jan,

Thanks for the reply. I need to this monthly and the data entries exceed 1,500,000 entries at a time. Could a macro substitute the import wizard and create different tabs as the rows reach the limit?

Comment by: Jan Karel Pieterse (9-5-2010 22:59:54) deeplink to this comment

Hi Joe,

A macro is certainly possible.
But do you really need all 1.5 million records to be in Excel cells?
Seems to me such an amount of data has to be aggregated into a summary report. In that case, a Pivot table should do the work. Pivot tables can hold such large amounts of data. When you start building the pivot table, you can select to use an external data source.

Comment by: Joe (10-5-2010 01:43:21) deeplink to this comment

Hi Jan,

Unfortunately I need all 1.5 million records. The data needs post import manipulation. Are you able to help me out with the macro?

Thanks.

Comment by: Jan Karel Pieterse (10-5-2010 02:13:03) deeplink to this comment

Hi Joe,

Chip Pearson already wrote such a routine, see:

http://www.cpearson.com/EXCEL/ImportBigFiles.aspx

Comment by: Joe (16-5-2010 05:50:41) deeplink to this comment

Thanks Jan. Will give it a bash.

Comment by: Andrew (15-6-2010 16:46:25) deeplink to this comment

Hi thanks for the info, it was helpful.

How do you delete a text import without scrolling down and deleting all the cells? Even if you delete all the cells, if you try to import a new file it says "edit text import" indicating that the text import isn't really gone. I want to completely delete the old import.

Thanks!

Comment by: Jan Karel Pieterse (16-6-2010 00:29:47) deeplink to this comment

Hi Andrew,

Are you saying you want to completely remove the import definition and all of its data?
I expect selecting all data and DELETING (right-click, Delete) the entire range (as opposed to emptying the range by hitting the del key) will remove the definition.

Or do you simply want to make sure the data from a previous import is completely removed before a new import with the same specs is done? This should happen automatically, depending on which settings you made in the one-but last step above (clicking the properties button).

Comment by: Phil (17-6-2010 05:54:16) deeplink to this comment

Hi there,

Great Article!

I've been trying this with a text file where the values are separated by spaces.
The preview works perfectly, but when I click "Finish" all the data is corrupted in excel.

Some data is in wrong columns
Some data is totaly junk

I've also tried this with a file that has tab separation and it does the exact same thing.

I need to import these tables, remove unnecessary columns and export as csv - but I can't even import them.

Any ideas - I can send you the file if you like.

Thanks heaps,
Phil

Comment by: Jan Karel Pieterse (18-6-2010 01:35:58) deeplink to this comment

Hi Phil,

Sounds as if the file has some problems indeed.
What if you open (a copy of) the file in a text editor and remove some lines, does that make a difference?

Comment by: Phil (18-6-2010 05:36:59) deeplink to this comment

Thanks,

Solved it. I had not changed the "Column data format" from "General" to "Text".
It seems "General" "Column data format" did not like the Hexidecimal numbers I was trying to import. It really mucked them up.
Changing it to "Text" worked perfectly

Thanks,
Phil

Comment by: Nigel Carpenter (29-7-2010 02:36:50) deeplink to this comment

This works fine but i have to import a different customer file name each import. How do you add the new data onto the bottom of the existing list. We are basically generating a database of customers doors that are required for a specific date. The data has been generated by a customer order program which exports individual customers doors as a csv file. I could also do with importing the the file name against each customer.

Thanks I have found this site very helpful.

Comment by: Jan Karel Pieterse (16-8-2010 03:22:29) deeplink to this comment

Hi Nigel,

The example given imports the file in thw same location every time.
What you would need is a small macro that -after the import- copies the new information to another worksheet (at the bottom of that sheet). You'd use that other sheet as the source for your reporting.

Comment by: brainscrew (10-9-2010 04:39:28) deeplink to this comment

if a txt file have more than 1048576 then what to do ?????//
i have file which have more than 1000000 data how to back it up????? to excel

Comment by: Jan Karel Pieterse (10-9-2010 07:35:43) deeplink to this comment

Hi Brainscrew,

Excel 2007/2010 cannot hold more rows than one million.
You could however import the file in MS Access and then use Excel to do pivot table reporting on the file.

Comment by: Ahmed El Sayed (16-9-2010 13:07:54) deeplink to this comment

hello,

i am trying to open text file in excel and but the problem is the file contains more than 1048576 row which Excel 2007 can not handel what can i do?

regards,
Ahmed

Comment by: Jan Karel Pieterse (17-9-2010 05:02:21) deeplink to this comment

Hi Ahmed,

Depends on what you need to do with your file. If you want to summarize the data, consider building a pivot table, using your file as its source (in the pivot table wizard, you can specify to use an external data source). In that case, there is no limit to the number of rows (except Computer memory).

Comment by: Reut (7-10-2010 02:55:36) deeplink to this comment

Hi,

I want import one text file to one excel file in separated sheets.
I want that when I will open the file it will be ready and order in the separated sheets.

Doed Excel support this option?

Comment by: Jan Karel Pieterse (7-10-2010 06:29:26) deeplink to this comment

Hi Reut,

Depends on the size of the txt file. You can tell Excel to skip a fixed number of rows during the import. The number of rows to skip however is limited to about 32,000 so if your file is larger, this will not help. The alternative would be to use VBA.

Comment by: Mohammad Khan (23-11-2010 07:25:56) deeplink to this comment

Thank you so much. This was a life saver.

Comment by: Belinda Montgomery (1-12-2010 08:27:17) deeplink to this comment

After importing the data into Excel, should you make nay changes to cell data types?

Comment by: Jan Karel Pieterse (1-12-2010 09:26:47) deeplink to this comment

Hi Belinda,

Depends on your requirements. I expect it is best to leave the cells in General format and use the text import wizard to define the format of the data for each column (step 3 of the wiz).

Comment by: mahvash (14-12-2010 14:09:28) deeplink to this comment

Thanks this helps a lot. Now I have 12 different text files to import. I save the query following your instructions. then I go to refresh. At this point I want to be able to bring in the second file, but not to wipe out the first. However when I do this, it replaces files 1 with the contents of file 2.
is there any way to import multiple files using the same saved query?

Comment by: Jan Karel Pieterse (15-12-2010 00:07:16) deeplink to this comment

Hi Mahvash,

Simplest would be to write a small macro that copies the imported rows to a new sheet, e.g.:

Comment by: Eric Spence (16-12-2010 14:01:03) deeplink to this comment

I have to import files with a format that has many rows in one row, separated by tabs (and columns within each row separated by commas), but Excel (as far as I can tell) is only set up to detect breaks for columns, not for rows too. For example:

Input File: row1col1, row1col2     row2col1, row2col2

What is should look like:

row1col1     row1col2
row2col1     row2col2

Can you help?

Thanks,
Eric

Comment by: Jan Karel Pieterse (17-12-2010 05:34:30) deeplink to this comment

Hi Eric,

If the textfile has a consistent structure, then what I would do is import the textfile and use an additional worksheet to rework the content.
Have a look at the OFFSET worksheet function, that would be the base function I'd use to get this working.

Comment by: vidhya (11-1-2011 00:14:51) deeplink to this comment

separate date month year format pls condition reply me.

Comment by: Jan Karel Pieterse (11-1-2011 04:59:02) deeplink to this comment

Hi Vidhya,

Please ask your question here:
www.eileenslounge.com

Comment by: Guru (11-1-2011 18:07:22) deeplink to this comment

Hi Vidhya,

Come to excel-macros@googlegroups.com

You will get answers to all your doubts.

Have a nice day.

Guru

Comment by: Caitlin (2-8-2011 09:57:04) deeplink to this comment

I'm importing a continuously updating .txt and while its importing everything correctly it gets rid of anything I add to the columns on the right of my imported data upon refresh.

I would like to take some of the numbers and crunch them through a formula as the data is coming in so it won't have to be done afterwards. Is this possible?

Using Excel 2003 & Windows XP.

Comment by: Jan Karel Pieterse (22-8-2011 02:53:33) deeplink to this comment

Hi Caitlin,

Yes, you can have formulas next to the imported range. Make sure you set the properties of the import range properly so the formulas contract and expand with it. Also check the settings for what the range should do when new data is imported.

Comment by: silvia visentin (30-8-2011 05:17:03) deeplink to this comment

In Excel 2007, where is the "Fill down formulas in columns adjacent to data" option. Is it not available in 2007 external data properties any more?

Comment by: Jan Karel Pieterse (30-8-2011 07:42:06) deeplink to this comment

Hi Silvia,

Right-click inside the import range and select "Data Range Properties". the checkbox you need is at the bottom of the dialog.

Comment by: Rakesh Pandey (1-9-2011 09:20:28) deeplink to this comment

please let me know how do excel can pick the text files from a folder and put it into excel in the desired format.

Comment by: Jan Karel Pieterse (2-9-2011 03:07:11) deeplink to this comment

Hi Rakesh,

I am not sure I understand your question, the article already shows you how to set things up so Excel prompts for a file and imports it according to the desired settings?

Comment by: Denis (7-9-2011 03:00:07) deeplink to this comment

Dear,
in excel 2007 when you clicked on the refresh button, Excel proposed a file, which has been used on the previously. In 2010 there is no 'suggestion' anymore, or can we select that kind of option?
Thank you

Comment by: Jan Karel Pieterse (7-9-2011 07:56:51) deeplink to this comment

Hi Denis,

Sure you can. In Excel 2010, right-click within the imported data and select "Data Range Properties...". Check the box that says "Prompt for filename on refresh"

Comment by: Hans Jensen (14-9-2011 06:47:05) deeplink to this comment

Hi

Do you know if it is possible to set/change the default settings in the External Data RAnge Properties dialog?

Ie. I do NOT want "Prompt for the file name on refresh" to be ON as default.

Comment by: Jan Karel Pieterse (14-9-2011 07:23:39) deeplink to this comment

Hi Hans,

I don't think you can change the default setting, no.

Comment by: tanababa@gmail.com (8-10-2011 03:07:33) deeplink to this comment

Hi,
Thank you for the post.
However, I need to summerise CSV files that have the same structure and I thought to do it via Pivot Table that will automaticaly be updated as the CSV changes. I thought to import the CSV into an excel Table but am not able to do that. Is there a way to do it?
Thank you,
Tamir

Comment by: Jan Karel Pieterse (9-10-2011 23:52:01) deeplink to this comment

I think the simplest way is to pull the CSV data into a worksheet like shown above and create the Pivot table from there.

Comment by: Jeff Axt (15-10-2011 06:49:50) deeplink to this comment

(@Rakesh) I think what you're asking is when I right-click on a text (or other) file in Windows Explorer and, for "Open with..." I choose Excel, the application presumes a non-delimited import, placing each line (record) into a single cell. What I would like is for Excel to automatically open up the Import wizard rather than presuming an import specification. I find it frustrating that I have to manually open Excel, close the open workbook, then browse to the desired file (which is often on another server buried deep into the folder structure), and THEN use Open (which activates the Import wizard), especially when I already have that file available to me in explorer.exe!

Hope that makes more sense...thanks!!

Comment by: murphyconor@yahoo.com (24-10-2011 03:35:56) deeplink to this comment

Simple when you know how, very frustrating when you don't.

Now I know how and it's down to this extremely succinct explanation.

Thanks!!

Comment by: RAVEEN KR (30-10-2011 03:21:22) deeplink to this comment

WHEN WE IMPORT THE DATA INTO EXCEL FILE DATA SHOW IN COLUMN
I WANT DATA SHOW IN DIFF COLUMN FOR EXAMPLE
INVOICE DATE         ITEM NAME RATE     QTY     TOTAL
123     24/10/2011    XYZ        1.50    1000     1500

Comment by: William (30-10-2011 15:15:26) deeplink to this comment

I want to import data from text files in existing cells.

Like if cell A1 is having "Content="

I want to import the file containing the value of "Content"
in existing cell.

After importing all the data from text file, the cell A1 should look like this "Content=52".

Is it possible?

Comment by: Jan Karel Pieterse (31-10-2011 01:07:10) deeplink to this comment

Hi Raveen, William,

The advice I would like to give the both of you:

Use two worksheets, one for the import and another that uses formulas to extract the information you need from the sheet with the import.

Comment by: Grace Jiang (7-11-2011 19:08:50) deeplink to this comment

Dear sir or madam,
First, thanks for sharing your knowledge with us.
I have a small business with hundreds of customers. A third party will collect rental for me every day depending on customers' billing days, then they send Invoice to me. After receiving the Invoice, I need to enter the payment I received from the customer to my customer list every day. It is very time consuming. I wander how I can use Excel data function to make the work easy and faster.
My data base which contains all customer information is an Excel 2003 file. The Invoice I received from the third party is a .cvs or .dat file.
My question is how I can transfer the rental to my customer list by using Excel 2003 data function.

Thanks,
Grace Jiang

Comment by: Jan Karel Pieterse (8-11-2011 00:55:15) deeplink to this comment

Hi Grace,

This is too complex a question for me to answer on the website I'm afraid.

I suggest you to either contact me directly (see email address below) so we might start a project for this, or to ask your question in smaller portions at http://www.eileenslounge.com.

Comment by: Andrea (11-1-2012 09:14:08) deeplink to this comment

The text file I am working with is a copy of a detailed financial report,usually over 50 pages. It contains headers, blank rows, subtotals, and lots of extraneous stuff that I don't want. (Unfortunately, a transaction report is unavailable, and this is all I have to work with.) Therefore, once I import the file, it requires much manual manipulation to parse it down to the real data to import into my transaction database. The problem then is that it contains old and new transactions, and there is no distinction between the two. I need it to import the new data without overwriting the old, because those records have been changed in my database and overwriting them would remove my changes. Is there a way for excel to compare the two sets of data and import only the "new" records, without overwriting the other records?

Thanks in Advance!

Comment by: Lani (11-1-2012 16:31:02) deeplink to this comment

Hello!
I am importing data into excel which has around 21,000 columns, so I am importing on different worksheets using the "do not import column" option in the "text import wizard" to exclude the columns i have already imported on the previous sheet. The only issue is that the data preview area (from which I choose the columns not to import) will not preview enough columns so i cannot import the last 2000 or so columns. How can I get around this?
thanks so much for your help.

Comment by: Jan Karel Pieterse (11-1-2012 23:42:38) deeplink to this comment

Hi Andrea,

I'm afraid there isn't a simple solution, you will need to (have someone) write VBA code to do all of this.

You could try splitting your problem into small parts and asking your questions over at Eileen's lounge (see link above comment box).

Comment by: Jan Karel Pieterse (11-1-2012 23:44:21) deeplink to this comment

Hi Lani,

The only way I would know is by using VBA code to handle the entire import. Unfortunately I don't have time to create an example for you. Perhaps someone at Eileens lounge can help? (Link above the comment box)

Comment by: mark nelson (21-1-2012 02:00:28) deeplink to this comment

Great site!
I am writing a program that repeatedly dumps out a csv file that I load into excel. I had been doing all the settings each time, so the info you provided saves me lots of time and headaches.
One additional question: in some generic-type csv files that my program creates, all I want is to import the file into excel and have the first row frozen (freeze pane). I sort of doubt there is a way to do the following, but I will ask just in case: is there anything I can put at the top of my csv file that will tell excel, once I click on the file, that I want the top row frozen? (My program could generate whatever command is needed and print it as the first line of the file).

Thank you.

Comment by: Jan Karel Pieterse (22-1-2012 22:23:45) deeplink to this comment

No, not in the CSV file. But I would think you can just freeze panes before the import, it should "stick".

Comment by: John Wilkinson (9-2-2012 15:56:52) deeplink to this comment

Great article! I set up some spreadsheets which do imports on monthly files for me based on your specifications and it works great and saves me a lot of time. One question though - I copy my templates into a new month folder each month, and when I do the refresh I have to make sure to specify the new month folder because it defaults to the last folder used. Not a huge problem, but I would like to create a little VBA procedure that updates the link automatically (or on the press of a button). But I can't figure out how to get access to the "link" object. Can you tell me how in VBA I would do that? Then I would just do a text replace on the path. It apparently can't be done in the excel UI, but it obviously keeps the information somewhere.

Comment by: Jan Karel Pieterse (10-2-2012 01:26:37) deeplink to this comment

Hi John,

You could use code like this to do what you need:

Comment by: John Wilkinson (10-2-2012 10:47:51) deeplink to this comment

Perfect! Thanks a lot Jan. I haven't done a whole lot of excel programming, but I'm about to. Your site looks like a great resource.

Comment by: johan (18-2-2012 07:59:02) deeplink to this comment

great article Jan!

i have a question, how to append new data instead of overwrite existing data ?
i have try all the options but all end up with the same result (my prev. datas replaced by my new datas).
any solution ?

thanks for your help

Comment by: Jan Karel Pieterse (20-2-2012 07:42:16) deeplink to this comment

Hi Johan,

I don't think there is a built-in way to have it append the imported data. You would have to write a bit of VBA that copies the new results to a separate worksheet.
Best to ask a question on eileen's lounge on how to do that, you will certainly be helped there.

Comment by: Kurt (5-3-2012 09:17:37) deeplink to this comment

Hi Jan,

I have an excel connection set up to a .csv file that is created through a timer in Access. The problem is if excel tries to read the data from the .csv file the exact time that Access is re-creating it (this file drop happens every 30 minutes). Is there a way for Excel to ignore the error and grab the .csv file on the next loop?

Thanks,
Kurt

Comment by: Jan Karel Pieterse (6-3-2012 00:42:19) deeplink to this comment

Hi Kurt,

You could consider having Access also drive the update in Excel, that way you can time the csv update in Excel.

Why use a csv file, if the data is already in Access, you can pull it in directly using a query, can't you?

Comment by: Kumar (10-3-2012 01:08:23) deeplink to this comment

Hi,
I've followed your tutorial but cannot select the individual columns in the 3rd step of the import wizard process. I'm trying to import data from the site below.

http://datawarehouse.hrsa.gov/Download_HCC_LookALikes.aspx

Can you help me import it correctly? When I tried doing it, it imported all the data into one column and not separate columns like it's supposed to.
Thanks.

Comment by: Jan Karel Pieterse (12-3-2012 01:03:12) deeplink to this comment

Hi Kumar,

Looks like this is a fixed-width textfile, not a delimited text file (which is not what the pdf file in the download zip file suggests). So in step 1 of the text import wizard, choose "Fixed width".

Comment by: Kurt (13-3-2012 05:51:29) deeplink to this comment

Hi Jan, this is Kurt again.

I've been working on this problem now for sometime and not getting the results I need. I've tried pushing the data to Excel through Access Query Export, but that causes a problem that I don't even want to get into :)

The text file drop works perfect, but if the file is not there on the loop that Excel makes, it produces an error. That in turn, disrupts my PowerPoint that is running on a continuous, updating loop....Is there any way for Excel to not produce an error if it is looking for that text file?

Kurt

p.s. you can email me if you need more details of what I'm trying to accomplish

Comment by: Jan Karel Pieterse (13-3-2012 06:03:38) deeplink to this comment

Hi Kurt,

You can easily use the DIR function to check for the presence of a file, like so:

Comment by: Kurt (13-3-2012 06:39:49) deeplink to this comment

Thanks Jan,

I'm going to try to write the entire text import in VBA and use that as the error checking.

I'll let you know how it works out.

Thanks for your help,

Kurt

Comment by: Sarah (11-4-2012 15:24:28) deeplink to this comment

I have a slightly different problem. I often paste text from multiple sources into a spreadsheet to do quick analysis. The first time I do it, Excel (2007, at present) allows me to use the import wizard to parse it the way I want. But the next time I do it, Excel doesn't show me the import wizard and simply uses my choices from last time to format the text. How can I make it show me the wizard again so that I can modify the settings to what I need this time?

Comment by: Jan Karel Pieterse (12-4-2012 04:45:53) deeplink to this comment

Hi Sarah,

See:

http://spreadsheetpage.com/index.php/tip/clearing_the_text_to_columns_parameters/

Comment by: Renata (20-4-2012 13:59:32) deeplink to this comment

In Excel 2010, when refreshing data from an external file, the path & name of the external file seems to be lost when I am prompted for the import file (ie. no default file is offered) and I have to browse to find the actual import file. In the previous versions of Excel, the path/name of the import file was always remembered by Excel. Thanx.

Comment by: Jan Karel Pieterse (23-4-2012 23:21:55) deeplink to this comment

Hi Renata,

Indeed! Although it does seem to remember the location for me, it removes the filename. Rather inconvenient if you ask me.

Comment by: Brenda (3-5-2012 12:51:49) deeplink to this comment

I have a VBA macro in my Excel spreadsheet that creates a QueryTable using a text file on Sharepoint site. This works fine for me and everybody else, except for one individual who gets a 1004 Error on the .Refresh method. Here is my code:

Comment by: Jan Karel Pieterse (4-5-2012 07:31:35) deeplink to this comment

Hi Brenda,

I strongly suspect this is a security issue: the user in question may not have permission to download that particular file from Sharepoint.

NB: I'd advise to not run this macro every time, as it will add a new connection to your workbook on each run. You only have to create the connection once, after that, you can just refresh the existing connection.

Comment by: Brenda (4-5-2012 07:48:23) deeplink to this comment

I didn't include all the code, but the connection is deleted after it gets what it needs from the querytable. For reasons I won't bore you with, I create and drop the connection each time the macro runs.

This person can access the file in IE, so the security on the Sharepoint side is not preventing access. So I was hoping that someone might be aware of any setting in the laptop configuration, maybe in Excel, that might prevent access.

Comment by: Jan Karel Pieterse (5-5-2012 07:16:39) deeplink to this comment

Hi Brenda,

What if you try to set up the connection manually from within Excel using that person's account and record a macro doing so? Is the recorded macro different in any way?

Comment by: Brenda (7-5-2012 10:21:35) deeplink to this comment

The 1004 error also occured when a manual import of the text file was attempted. Tried to record it, but the macro was empty because of the error:

Comment by: Jan Karel Pieterse (8-5-2012 00:15:01) deeplink to this comment

Hi Brenda,

Odd. This must be some sort of user setup thing I don't know about I'm afraid.
If the user experiences the same problem when on another computer, this is an account problem your IT dept must try to solve. If it works on another PC, it is a local profile problem the IT dept can solve.

Comment by: Trey (17-5-2012 08:44:50) deeplink to this comment

Using your method above overwrites old files with newer ones. I have multiple text files to import, but they need to be in sequential order. Do you have any ideas for how to do that (without having to re-define import settings each time)?

Comment by: Jan Karel Pieterse (18-5-2012 07:20:23) deeplink to this comment

Hi Trey,

What I would do is write a small macro, something along these lines:

Comment by: Trey (18-5-2012 09:02:52) deeplink to this comment

OK, I'm not a macro expert. I recorded a macro opening a file the way I want, how do I change it to let me choose the file to open as in your example above?

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Data\aae20031119d.min" _
        , Destination:=Range("$A$1"))
        .Name = "aae20031119d"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 27
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 2, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(10, 13, 4, 13, 10, 10, 10)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Comment by: Jan Karel Pieterse (20-5-2012 23:20:44) deeplink to this comment

Hi Trey,

What you do is:

1. Manually import the file the way you want it to be processed at the location you need the data.
2. Paste my code into a normal VBA module
3. Save workbook as a filetype which can hold macro's
4. Run my code to import multiple files.

Comment by: Appan Parige (25-5-2012 07:27:48) deeplink to this comment

What is the difference between import text file through text file wizard and drag a text file in excel directly.

While drag an text file in excel, text import wizard is not displayed and more number of empty rows are created in this scenario.

Comment by: Jan Karel Pieterse (26-5-2012 02:57:42) deeplink to this comment

Hi Appan,

Obviously the difference must lie in the fact that the text import wizard does not start and hence the data in your file is treated differently by Excel during the import.

Comment by: VG (30-5-2012 20:07:27) deeplink to this comment

Hi i wrote a macro for importing as pr your advice .. Some time i get error as i have empty file( my .txt file is empty). Is there a way to handle it?

Comment by: Jan Karel Pieterse (31-5-2012 08:48:30) deeplink to this comment

Hi VG,

YOu can test for the length of the file:

Comment by: VG (1-6-2012 18:46:08) deeplink to this comment

Thank you so much.. Its working. I have another issue on the same requst. I have more then 65,000 records i am unable to import. Also when i try to find the count it says "2" when it reaches 65,535. How should i declare my variable to find the original count. As of now i have declared my varibale as below.
Dim LastCol_C, Lastrow_C As Long

Comment by: Jan Karel Pieterse (2-6-2012 16:02:47) deeplink to this comment

Hi VG,

Can you post your code?

Comment by: Bertrand (6-6-2012 15:48:35) deeplink to this comment

Going through the text import wizard does not let you format the data range as a table. The workaround that I have found is to import the csv file by means of MS Query which is more cumbersome.
Has anyone found a simpler solution to get the best of both worlds: simplicity of the text import wizard, ease of use of referencing tables cells ?

btw: thanks for sharing so much instructive information !

Comment by: Jan Karel Pieterse (6-6-2012 18:45:53) deeplink to this comment

Hi Bertrand,

You're welcome!

I wouldn't know a simpler route than the one you already suggest unfortunately.

Comment by: Lepraik (30-7-2012 14:38:08) deeplink to this comment

Great advice, thank you.

Do you have any sugestions as how to import a text file in a predefined directory? i.e. name of file changes (bank statement). Idealy the script would import the file, then delete the file out of the predefined directory. Even better still, process all files in the directory and delete processed files withing the directory.

Comment by: Tiftazani (31-7-2012 10:13:00) deeplink to this comment

Hi Jan,

Your tutorial is great, it saves a lot of time for me. But I think, I need another way to import text file (fixed width) and change it to the way i want. It's like I put a button on the excel sheet, then when i click the button, it will give me dialog to choose which file i want to import to excel. And after I choose the textfile, it would automatically converted into the table.

I am wondering, when i save the query to import textfile and change it to the table including its datatype, as u mentioned on your tutorial, if it's like a code,is it possible to copy it, and paste the code onto my macro?

Thanks in advance.

Comment by: Nick Bodni (1-8-2012 19:26:40) deeplink to this comment

I am using 2010 Excel, but I do not have an option in my properties to "Fill down formulas in columns adjacent to data". How is this possible in 2010?

Comment by: Ankit (3-8-2012 15:55:46) deeplink to this comment

How do I limit or specify number of rows to be imported from text file. I only to want to copy the 1st row discarding all other rows.

Comment by: Koen (3-8-2012 16:09:55) deeplink to this comment

If i import a text file in excel all of my values are multiplied by a million, the preview screen doesn't show these multiplied values, but the real values. How can i fix this?

Comment by: Jan Karel Pieterse (7-8-2012 10:58:49) deeplink to this comment

Replying to multiple questions here...

@Lepraik: You would need VBA code to perform these actions. They can rely on the method mentioned in this post, and the VBA code only has to ahndle the updating of the link to the files and subsequently refresh the text import and after that copy/paste the imported content to a new worksheet and delete the just imported file.

@Tiftazani : I'm not sure I understand. Using the method the post describer allows you to just refresh the table. Excel will rpompt for a new file.

@Nick: Odd, I have just checked and it is there for me! Just click the properties button after you have clicked the final step of the wizard, but BEFORE you OK the dialog in which you select the target region of the import. The option is also available from the ribbon, Data, Connections, Properties.

@Ankit: You cannot set the number of rows to import I'm afraid.

@Koen: This may be caused by a wrong setting of the decimal/thousands separator symbol in the import specification.

Comment by: shaun (10-9-2012 23:25:01) deeplink to this comment

Hi thanks for a great article, I've got a folder full of 100's of text files and a column in a spreadsheet with the name of each text file. I want the contents of the text files to be copied to the corresponding cell in the spreadsheet, basically I need to replace the text file name with its contents. Is this possible? thank you

Comment by: Jan Karel Pieterse (11-9-2012 09:52:43) deeplink to this comment

Hi Shaun,

This calls for a VBA macro.
Suppose you have a worksheet called "Import" on which you have set up the import settings like stated above in this article.

The code below will run through the cells you selected prior to starting the macro, change the filename of the import sheet and refresh the import for that file.

Then it copies the value of the cell on row 2 and column 2 (cell B2) of the imported data to the cell next to the cell with the filename.

Comment by: Darlene (21-9-2012 22:56:01) deeplink to this comment

I am having trouble saving a file from Excel to .txt without altering the structure...when I import a textfile into Excel and save as .txt it looses it's structure...can you give me some guidance on where to find some help?

Thank you,
Darlene

Comment by: Jan Karel Pieterse (24-9-2012 11:45:10) deeplink to this comment

Hi Darlene,

Perhaps this post on DailyDose Of Excel helps?
http://www.dailydoseofexcel.com/archives/2004/11/09/roll-your-own-csv/

Comment by: Peggy Parker (4-10-2012 17:27:49) deeplink to this comment

I am using excel 2010 and for some reason today I cannot import more than 65ish thousand records into one spreadsheet, yet my co-worker took the same Txt files and imported them into on sheet no problem.. where do I begin to look for what is different on my settings?

Thanks

Comment by: Jan Karel Pieterse (5-10-2012 14:04:26) deeplink to this comment

Hi Peggy,

Probably your Excel has the old Excel 2003 fileformat set as the default. That will limit the import to 65000 rows, as that is the maximum for 2003.

Look in File, Options, Save tab.

Comment by: Stephen (22-10-2012 17:39:12) deeplink to this comment

I have a text query that is refreshed via macro. The refresh opens up a file dialog box for the user to select the file (because it's going to be a different file name every time). I am trying to figure out how to capture the name of the file the user chooses, because after some manipulation, the VBA macro is going to export the data as a csv file of the same name.

Any suggestions?

Thanks.

Comment by: Jan Karel Pieterse (22-10-2012 20:21:52) deeplink to this comment

Hi Stephen.

You should be able to extract the file name from the Connection property of the QueryTable Object in question.

Comment by: Stephen (22-10-2012 20:42:34) deeplink to this comment

That did the trick! Much appreciated!

Comment by: Dave (29-10-2012 22:16:20) deeplink to this comment

I like the import feature! I have a text file with "username, pcname, date". Is there a way to only import pcnames with date less than 90 days old? I've been trying to write a script, but having some trouble identifying the date range. Any help would be appreciated!
Thanks!

Comment by: Jan Karel PIeterse (30-10-2012 08:58:09) deeplink to this comment

Hi Dave,

If you use the data from other sources and use MSQuery, you should be able to add a source type of text and use MSQuery to import the data.

Comment by: Vignesh (7-11-2012 10:31:14) deeplink to this comment

I need help to export Excel Records like Report format by Macro in Excel 2003
Eg- Excel sheet:
Name City State
aa bb cc
dd ee ff
Report:
    Name: aa         Name: dd
    City: bb         City: ee
    State:cc         State:ff

Thanks in Advance!

Comment by: Jan Karel Pieterse (8-11-2012 13:58:25) deeplink to this comment

Hi Vignesh,

Please go to www.eileenslounge.com to ask this question.

Comment by: Praveen (18-11-2012 19:46:37) deeplink to this comment

tahnks for your detailed `information I have big test file having more than 15,11,659 lines I want to convert it to Excel formet in one sheet .Please help

Regards
Praveen

Comment by: Jan Karel Pieterse (19-11-2012 06:48:09) deeplink to this comment

Hi Praveen,

Excel has a maximum number of rows of just over 1 million. This is a hard-coded limit.

You could import the data into a database package like SQL Server and then query the database to extract the information needed to create e.g. pivot tables.

Comment by: Josh (7-12-2012 00:27:43) deeplink to this comment

Hi, Jan,
Your code can import a text file to a excel sheet. I have 400+ .txt file. All of them have 4 cols inside and with the same text dilimited format in the same directory. I would like to have a VB sunb code to import all col-1's from all text files to sheet1 of excel and col-2's to sheet2, and so on so forth. All excel sheet have the format as col1 from text file1, col2 from text file2.

Thanks,
Josh

Comment by: Jan Karel Pieterse (7-12-2012 13:07:10) deeplink to this comment

Hi Josh,

Please go here to ask your question:

www.eileenslounge.com

Comment by: Lisa (26-12-2012 17:53:53) deeplink to this comment

I have imported a file and all the fields have the data like this: 'information' rather than without commas. Is there a way to fix that that isn't Skill Level Ninja?

What I want it to look like:
dog
cat
unicorn

What it actually looks like:
'dog'
'cat'
'unicorn'

Comment by: Jan Karel Pieterse (28-12-2012 14:25:58) deeplink to this comment

Hi Lisa,

I'm sorry, I wouldn't know right now!
Perhaps asking your question here helps:

http://www.eileenslounge.com

Comment by: Rabab (1-1-2013 15:57:56) deeplink to this comment

Hi;
I have a data contains more < 1.5 Millions rows, when I unzip the csv file and open it in excel 2010, always there is a msg that the file loading incomplete!!
thanks

Comment by: Jan Karel Pieterse (2-1-2013 13:48:37) deeplink to this comment

Hi Rabab,

Excel only can hold 1 million rows, so it makes sense your file cannot be loaded completely.

What are you planning to do with the data?

Comment by: Y Lu (14-3-2013 19:05:19) deeplink to this comment

How can I also input file name information into any cell when I input external data each tiem like this?

Comment by: Jan Karel Pieterse (15-3-2013 13:11:37) deeplink to this comment

Hi Y Lu,

I am not sure what you mean? Can you please try to explain a little more?

Comment by: Y Lu (16-3-2013 05:33:10) deeplink to this comment

Hi jan,
thanks for the reply!
I am looking for the methode, in which I can get name of the data file (from which the data is imput). Can we get the name of file and automatically imput it into some cell when everytime we imput the file data?

Comment by: Jan Karel Pieterse (16-3-2013 15:16:34) deeplink to this comment

Hi Y Lu,

You can only get the name of the datafile by using a bit of VBA:

Comment by: Y Lu (18-3-2013 19:43:18) deeplink to this comment

I tried VBA methode before, but it is no works.
I thought there should be some simple way we can get the name of file, because everytime when I do "Refrash Data", the name of the file for previous data I lmported always show in popup windows. I think the Excel should remember it in somewhere.

Comment by: Jan Karel Pieterse (19-3-2013 08:16:27) deeplink to this comment

Hi Y Lu,

Excel does remember the filename indeed. It is shown in the properties of the connection in question as you can see in Data, Connections group, Properties button.

Comment by: Ken (20-3-2013 11:11:46) deeplink to this comment

Hello,

How can I set Excel 2010 such that whenever I double click a .csv file it opens it in separate columns automatically and not all in one column comma separated.

Many thanks in advance
Ken

Comment by: Jan Karel Pieterse (20-3-2013 16:50:56) deeplink to this comment

Hi Ken,

You can change your control panel, regional settings so the system settings match the decimal and list separator that is in the csv file.

Comment by: Ken (21-3-2013 09:36:56) deeplink to this comment

Thanks Jan,

I have already changed my regional setting to comma separated but Excel 2010 still opens the .csv file on double click in one column and I have to go to Data - Text to Columns everytime to get it the way I want i.e. in separate columns.

Regards
Ken

Comment by: Jan Karel Pieterse (21-3-2013 11:09:20) deeplink to this comment

Hi Ken,

In that case I think it is easiest if you use the methods explained on this page.

Comment by: Kamran (5-4-2013 10:05:31) deeplink to this comment

I am using office 2007, I tried to import data from text file, when i select Data --> From text
no wizard open to select text file plz tell some solution.

Kamran

Comment by: Jan Karel Pieterse (5-4-2013 16:45:20) deeplink to this comment

Hi Kamran,

Have you tried clicking that button on an empty worksheet?

Comment by: Patricia Pinkston (10-4-2013 20:43:18) deeplink to this comment

I have a 29 page Excel workbook. I'm trying to get specific "text" from various cells in Sheets 2-29 to appear in the 'Master' (Sheet 1). (So that when you enter the words or numbers in any of the sheets 2-29, it will automatically populate into the master sheet 1.
I was able to pull the numerical data (dollar amounts, numbers) into the master sheet by using =sum(isscc!B4) but I can't get things like 'Yes', 'No', 'San Francisco, CA', 'Feb 3 - 9, 2013' to appear in the cells on Sheet 1

Comment by: Jan Karel Pieterse (12-4-2013 13:01:03) deeplink to this comment

Hi Patricia,

You are not using the SUM function the way it is intended: to SUM the values you give it. Instead, you should point to the cells in question directly:

=isscc!B4

Comment by: Juan (30-4-2013 10:11:24) deeplink to this comment

Hi there!

I just have a small question. I already know how to import some data from a .txt file to excel. The thing is that I need just to import data from row 14 until row 20. I know how to start it from row 14 but not how to tell the Macro that i just need data until row 20.
My txt file has more than 11 000 rows thats why is a big problem for me.
I appreciate so much the help!

Comment by: Jan Karel Pieterse (1-5-2013 13:41:59) deeplink to this comment

Hi Juan,

I would simply import all data and then use a different worksheet to use the part of the data you are interested in fo your calculations.

Comment by: Doug Foster (2-5-2013 18:30:42) deeplink to this comment

I've set up a macro to refresh and sort multiple .csv files in Excel 2007. When I send the spreadsheet to someone else and have them set up the same named .csv files (but not always in the same directory & folder, etc.) on their computer they get error messages saying that Excel can't find the file. When that happens I usually go through reimporting from their computer so that the refresh macro will work. Do I need to do this everytime I set the spreadsheet up on a new computer, or is there a way to get the same refresh to work with the same named .csv files regardless of which computer or which location the files are in? Thank you.    

Comment by: Jan Karel Pieterse (3-5-2013 11:31:48) deeplink to this comment

Hi Doug,

It should not be very hard to adapt your code so it can handle things like:

- it assumes the csv will be in the same folder the workbook itself is in
- assumes the csv is in a folder relative to the folder the workbook is in
- Prompts the user to find the csv file if it isn't found

Can you post the relevant (piece of) code?

Comment by: Doug Foster (3-5-2013 15:35:20) deeplink to this comment

Thanks. Yes, I'm not a programmer so I'm not sure exactly what you want, but I copied part of the routine (beginning and end):

ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Fix30").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Range("A7:I32").Sort Key1:=Range("A8"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets("Fix10").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Range("A7:I32").Sort Key1:=Range("A8"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("Conforming Update").Select
End Sub

Comment by: Jan Karel Pieterse (3-5-2013 16:19:04) deeplink to this comment

Hi Doug,

You could do it like so:

Comment by: Doug Foster (3-5-2013 17:42:03) deeplink to this comment

Thank you Jan. Not knowing how to read code, I'm not sure how to integrate this. But I'll see if I can figure it out and let you know. I really appreciate your help!

Comment by: mohamed taher (23-5-2013 02:30:49) deeplink to this comment

I just have a small question, I already know how to import some data from a .text file to .excel, but to ignore some rows aspirator between the data ,for example ----------------, or column and save the form, i don't know it.
Can you send me the relevant on my mail ?

Thanks for your cooperation.

Comment by: Jan Karel Pieterse (23-5-2013 10:42:55) deeplink to this comment

Hi mohamed,

You could use the Data, From other sources, From Microsoft Query option, that way you can set up criteria.

Comment by: Cristina (27-6-2013 01:23:53) deeplink to this comment

I have saved import steps from text and am able to refresh successfully on my own computer. I shared my template with a coworker and when they try to refresh the connection, they are not able to see any text files. What could be wrong? How do we share import steps with others having to import the same text file?

Comment by: Jan Karel Pieterse (27-6-2013 07:27:52) deeplink to this comment

Hi Cristina,

Are you sure the other users have access to the same folder?

Comment by: Tony Rudmik (11-7-2013 18:23:30) deeplink to this comment

Hi
I enter charts (fixed width text files) using text wizard. Quite easy. However, I have some column headers that span across 2-10 columns (eg Region/ Age Group). I would like it to be centered and not split up when assigning columns. Since I enter up to 100-200 tables at a time, is there a way to create a macro that takes care of the centering issue?

Comment by: Jan Karel Pieterse (11-7-2013 21:19:29) deeplink to this comment

Hi Tony,

Please ask your question here:
www.eileenslounge.com

Comment by: Ali (16-7-2013 07:06:59) deeplink to this comment

i am unable to refresh a text file in excel which i used to refresh previously. the refresh option on the data ribbion is inactive for spread sheet. how can i fix it.

Comment by: Jan Karel Pieterse (8-8-2013 21:13:57) deeplink to this comment

Hi Ali,

Probably you inadvertantly deleted the import definition.

Comment by: Denis Filipetti (3-9-2013 18:31:37) deeplink to this comment

Hi Jan,

    Many thanks for your very helpful column. I am using Excel 2010 to import data from a text file with fixed columns. It is rather complicated and I wonder how I can edit the column definitions without re-specifying them in a new template ?

Many thanks for your help,
Denis

Comment by: Jan Karel Pieterse (4-9-2013 20:14:35) deeplink to this comment

Hi Denis,

You could just copy an existing imported table to the other file and then go through the wizard?

Comment by: Denis Filipetti (4-9-2013 20:22:23) deeplink to this comment

Hi Jan,

Sounds right but when I open the xltx file it then asks for the data file to import after which all the entries in Data -> Get External Data (I used From Text originally) are grayed out. Am I missing something obvious ? Is there another way to spec the columns ?

Thanks again,
Denis

Comment by: Jan Karel Pieterse (4-9-2013 22:24:16) deeplink to this comment

Hi Denis,

After the first import you can find the settings on the Data tab, Connections button. Then click Properties, select the Definition tab and click the edit query button.

Comment by: Denis Filipetti (5-9-2013 20:34:35) deeplink to this comment

Great ! Many thanks Jan, it worked a treat. I had explored in that area but had not drilled down that much. I greatly appreciate you taking the time to help.

Thanks again,
Denis

Comment by: Bob Minchin (17-9-2013 21:23:59) deeplink to this comment

Hi, I'm having trouble importing a text file into excel 2003.
The text file format is outside my control and each line looks like

"1","","1.226","Kohm","09/16 19:06:22" where the last field is mm/dd hh:mm:ss and no year information.

when I import into excel the result column E gets corrupted as follows

01/09/2016 19:06:22

As there was no year information, Excel has picked up the dd data and converted it wrongly into yyyy and wrongly inserted 01 as the day of the month.

Some how I need more control of the import format interpretation than the standard import wizard allows.

Any clues please?

TIA
Bob

Comment by: Jan Karel Pieterse (18-9-2013 10:18:11) deeplink to this comment

Hi Bob,

I guess you have two options:

1. Use VBA to handle the entire import
2. Do the import and subsequently fix all dates using a formula like this:
=DATE(2013,YEAR(A4)-2000,MONTH(A4))+A4-INT(A4)

Comment by: Bob Minchin (18-9-2013 10:58:48) deeplink to this comment

Thanks Jan,
I'm not at all proficient in VBA so I'll use your second suggestion.

Bob

Comment by: mus (24-10-2013 17:58:38) deeplink to this comment

I want to have a VB code that help me import a text file in an Excel worksheet , I know how to do it manually, but a spreadsheet application would be much better because i have a project of a dozen of text files

Thanks

Comment by: Jan Karel Pieterse (25-10-2013 09:08:27) deeplink to this comment

Hi Mus,

If you browse through the comments of this page (click the show all collents link below), you will find som eexample code:

https://jkp-ads.com/articles/importtext.asp?AllComments=True

There is a small entry by me dated 12/15/2010 which might be useful.

Comment by: Adrian (1-11-2013 15:03:27) deeplink to this comment

Nice instructions, nice follow up on the comments. Nice job anticipating questions about different versions. Just saved this site as a favorite. Well done.

Comment by: Sam Desiderio (2-11-2013 16:08:49) deeplink to this comment

When I follow the above procedure for importing a .txt file created in Quicken, the file comes in with the column headings - a, b, c, etc. going right to left instead of left to right. What am I doing wrong?

Comment by: Jan Karel Pieterse (2-11-2013 20:55:08) deeplink to this comment

Hi Carlos,

Not sure what the problem is, but I would start trying a different "File origin" in the first step of the wizard.

Comment by: Harvey Vedder (5-11-2013 20:10:16) deeplink to this comment

I want to edit a long text string in an Excel 2007 column of many such strings. When I hover over one I see only a big hollow "+" instead of the cursor mark and have to go to the entry line on top instead of right into the cell where the whole block of text shows up. Strange things happen in the cell making most of the text disappear and I cant get to say, the tenth line of text in that cell to make my edit.

Comment by: Jan Karel Pieterse (6-11-2013 10:02:13) deeplink to this comment

Hi Harvey,

Odd indeed.

Though Excel isn't very good at holding large amounts of text in a single cel, you should be able to at least edit it reliably.

If you use this formula, how much characters does it show as a result (change cell address accordingly):

=LEN(A1)

Comment by: Guido Leenders (20-11-2013 01:05:47) deeplink to this comment

In Excel 2010 you can place 16.000 characters in a cell, but editing only works when you copy it to a notepad editor and then paste back. Also, the Excel search misses everything after 4K or so. But it works! And the integrated editor when you drop down the F2 formula bar (to the right of it), is usable.

Comment by: Irum Godil (25-11-2013 04:00:56) deeplink to this comment

Thanks for this article, really helpful!

Comment by: Gordon (8-1-2014 16:58:16) deeplink to this comment

I have an Excel 2007 spreadsheet with 10 tabs each importing a separate CSV file. When I 'refresh all' on my PC the file prompt comes up WITH THE FILE NAME suggested for each tab so I don't have to select it but can change if I want to. When I copied this file to an associate they get the dialog box prompting for the file but no suggested file name. It seems it must be in a setting somewhere in the query to make it work like my PC does but I can't find it. Can you help?

Comment by: Jan Karel Pieterse (9-1-2014 10:52:33) deeplink to this comment

Hi Gordon,

Perhaps your colleague does not have the same path to those files?

Comment by: Rachael (12-1-2014 02:41:43) deeplink to this comment

when I try to import the document the text comes up all sybols and coding. I have tried copying and pasting to a word document saing it the import it but it still comes up all coded. why would this be and how do I fix it

Comment by: Jan Karel Pieterse (12-1-2014 16:50:51) deeplink to this comment

Hi Rachael,

Looks like the file isn't really a text file, but rather some other fileformat.

Comment by: Lachmi (20-1-2014 18:18:57) deeplink to this comment

Hi Jan:

Once you have saved the import specification is there a way to go back and amend it having realised that you might have made some erros with the field start postions?

Comment by: Jan Karel Pieterse (20-1-2014 19:37:16) deeplink to this comment

Hi Lachmi,

Sure. Click the Connections button on the ribbon (Data tab). Then click the connection in question, clic the Properties button, click the Definition tab and finally click Edit query.

Comment by: Julie Jarman (5-2-2014 17:45:17) deeplink to this comment

I am importing a text file that has dates DDMMM,and when I import the dates change to DDMMMYY as required. However for this month only the year converts wrongly as 14 instead of the expected 13.

The text data needs to be added on to an Excel worksheet, then sorted by date order and turned back into a text file. When running it through the software I get an incorrect result.

So if I turn the text file back into Excel to check, the dates have again reverted to 14 instead of 13. Any ideas? I am using Excel 97-2003. thanks

Comment by: Jan Karel Pieterse (5-2-2014 19:24:25) deeplink to this comment

Hi Julie,

I guess all you can do is post-process the data after importing so all dates get the expected year. You could try search and replace perhaps?

Comment by: Marielle (9-4-2014 12:16:54) deeplink to this comment

Hi,

I seem to have the same issue as "Gordon (1/8/2014 4:58:16 PM)".

There is a prompt, but no suggestion anymore. I encounter this issue since we updated from WinXP to Win7. We worked with excel 2010 before and still do.

We are working with the same files and same paths as before. Must be a setting somewhere but what can it be?

Any tips or ideas would be much appreciated.

Comment by: Jan Karel Pieterse (9-4-2014 13:01:35) deeplink to this comment

Hi Marielle,

I'm afraid there isn't much we can do about this, as it seems this is caused by either the Office version, or the Windows version!

Comment by: Candy (26-4-2014 16:57:45) deeplink to this comment

I have several text files per week to convert into Excel 2010. When I open all of them, It doesn't open in wizard. Just a bunch of gobble goop. I love the External Data Range Properties dialog,at least that is one less step. I will never be changing the information just converting. What I do now is: Step1: click on delimit step2: unclick tab and in other put a ~, step 3: done. I asked this question from a teacher for Excel advance. He couldn't help.
Please help, this takes so long to do one conversion at a time.
Thanks so much
Candy

Comment by: Jan Karel Pieterse (28-4-2014 09:56:02) deeplink to this comment

Hi Candy,

Well, you could use the method described here and perform these steps:

1. Define text import as described
2. copy imported data to another sheet
3. Click Refresh All
4. Point to next file
5. Repeat from step 2 untill all files are done.

Comment by: Rakesh Ranjan (6-6-2014 08:50:44) deeplink to this comment

I have a csv file which has 38 Lakh rows. Now ,when i follow the same steps,it works fine but only imports 10,40,000 data and gives a message that the data in the sheet exceeds the number of rows.......
Please suggest how to import the remaining data..

Comment by: Jan Karel Pieterse (6-6-2014 11:49:55) deeplink to this comment

Hi Rakesh,

Excel cannot hold more records in a worksheet than a little over 1 million. I suggest to import that data into PowerPivot, which can hold lots more data.

Comment by: Mona (11-6-2014 09:32:56) deeplink to this comment

If text file is not containing regular and same deliminitor then how to convert it in excel file

Comment by: Jan Karel Pieterse (11-6-2014 16:35:33) deeplink to this comment

Hi Mona,

The text import wizard can be used to set more than one delimiter (tab, space, comma, other), but you can only define one custom delimiter. Which delimiters does your file have?
Which Excel version are you using?

Comment by: Jan Karel Pieterse (12-8-2014 10:42:38) deeplink to this comment

Hi Samir,

Perhaps PowerQuery (free addin for Excel 2010) helps with your problem!

Comment by: Thomas (13-9-2014 12:59:43) deeplink to this comment

Hi

Thanks for a great guide. The link to the external file is great. For a long time i only thought that was possible with Access.

Question: I can only get excel to expand/delete formulas adjacent to the right of the imported data to work. Not in cells adjacent to the left. Is this a known limitation?
Are there any known limitations to this feature it would be nice to know?

Comment by: Jan Karel Pieterse (13-9-2014 17:12:08) deeplink to this comment

Hi Thomas,

I expect you can only have formula columns to the right in this case. When you have a connection to a database, formulas can even be in the middle of the table.

Comment by: Anibal (20-10-2014 20:58:49) deeplink to this comment

Hi!

When migrating excel and text files form a folder to another the link breaks and it is required to update it to restore connection (data/connections/properties/definition/browse).

Is it possible to indicate for example that the txt file is always in the same folder than the excel file in order to ovoid the manual link update each time files are moved?

Thanks a lot. Regards.

Comment by: Jan Karel Pieterse (21-10-2014 08:27:12) deeplink to this comment

Hi Anibal,

You would typically need a bit of code triggered from your workbook_Open event, which loops through the workbook connections and then changes the path accordingly. Something like this:

In a normal module:

Comment by: paul (20-11-2014 16:54:55) deeplink to this comment

superb. you have just saved me about 10,000 clicks over the course of my life!

Comment by: katie (27-11-2014 21:07:18) deeplink to this comment

Amazing information. Totally helped me.

I have to build an excel spreadsheet. It has to be able to import data from a text file that is saved a specific location.

This helps a lot but I have to take it a step further. The user has to be able to select a certain txt file among many saved in a folder. Is there a way to build a macro or code in vba that will allow the user to see all the txt files saved in a folder and select the one that is needed?

Thanks,
Katie

Comment by: Jan Karel Pieterse (28-11-2014 13:27:52) deeplink to this comment

Hi Katie,

You would simply use the method I demonstrated in the article. Then when it is time to get a new textfile, just hit the refresh button and Excel will automatically prompt for a new file.

Comment by: Lynda Maynard (4-12-2014 21:45:06) deeplink to this comment

You saved me again! I wind up with lots of .csv files to import, and there are several fields that contain codes consisting of only numerals. Manually formatting all of those as "text" every single time has been very tedious. This is even better in the long run than being able to select multiple fields in the import dialog.

Comment by: Michael (18-12-2014 23:41:29) deeplink to this comment

Hi Jan,
Great information here, thank you!!
But how can i make all this import automatically?
Creating a import button in excel that automatically import all the information from 10 other files with the same formatation but with different information.

Regards,

Comment by: Jan Karel Pieterse (19-12-2014 11:55:03) deeplink to this comment

Hi Michael,

You could do something like this:

Comment by: Sasi (24-2-2015 14:59:30) deeplink to this comment

Hello,
I have text file with 6165202 lines.
I have managed to import first 1048576 lines in first sheet.
Next I have created the new sheet and typed 1040000 as the first line for import to this sheet.
When the import finished, I saw only 48576 rows in this sheet. Why ?
What can I do to make it possible to import the whole file to many sheets ?

Comment by: Jan Karel Pieterse (24-2-2015 15:39:06) deeplink to this comment

Hi Sasi,

Why are you trying to import that many lines into an Excel file? It is perhaps better to import your data into a database system like Access and then connect an Excel pivottable to that database. That way you do not need to place the data in worksheets and are not limited to 1 million rows.

Comment by: prince (2-3-2015 16:17:55) deeplink to this comment

Hi !
Will you please help me how can i access the name from one sheet to another sheet . the main thing is when i call the data from (sheet1!A1) after that 14 row should be blank in sheet 2. as well as on the row 15 of sheet2 , should store ( sheet1! A2) data. I have created such type of format on Sheet2

NAME :- Rozer 1 Roll-no 801

14 time blank line after that

NAME :- Sammul Roll-no 802

}}}}

All information are accessing from sheet1 . i want if i enter the data in sheet one, then sheet 2 automatically create such type of forms.I hope you will suggest me the best formula.The main problem arising in ( NAME & Roll-No ) column.
Thank you!!!

Comment by: Jan Karel Pieterse (2-3-2015 17:29:04) deeplink to this comment

Hi Prince,

Please go here to ask your question, as there will be more people available to try to help you:
www.eileenslounge.com

Comment by: wesley (3-3-2015 21:46:40) deeplink to this comment

Can I import just the last row and only the last row of a text file every time I refresh?

Comment by: Jan Karel Pieterse (5-3-2015 12:04:37) deeplink to this comment

Hi Wesley,

I don't think you can. However, it is not very hard to write a formula that searches the imported table and returns the last used rownumber. For a column with numerical data:

MATCH(9.99999999999999E+307,A:A)

Comment by: Sharlot (11-3-2015 21:50:50) deeplink to this comment

What could be the reasons for converting text to numbers after importing text into a spreadsheet

Comment by: Jan Karel Pieterse (12-3-2015 06:15:27) deeplink to this comment

Hi Sharlot,

It happens quite often that Excel misinterprets numbers during import and stores them as text in the cells. Reasons include:

- Data has text values or blanks on some rows
- Data has different decimal separator than your system

Comment by: Rickard Andersson (17-3-2015 16:34:05) deeplink to this comment

Hi,

I want to import a CSV-file into excel and group some rows together. Is that possible to prepare the CSV-file so that when the file is imported excel will automatically group some of the rows together in the import?

Eg:
Row1;
Row2;
Row3;    // group together with row4 (+)
Row4;    // group together with row3 (+)

Thanks
Rickard

Comment by: Jan Karel Pieterse (17-3-2015 16:51:17) deeplink to this comment

Hi Rickard,

No, you would have to do that after the import.

Comment by: Beckey (23-3-2015 13:52:51) deeplink to this comment

Hi, lovely tutorial and very useful thanks! Is it possible to suppress blank lines on the import or can it only be done after import, perhaps with a macro...?

Comment by: Jan Karel Pieterse (23-3-2015 15:00:09) deeplink to this comment

Hi Beckey,

No I suppose you'll have to do that after the import. ALternatively, try importing using MSQuery or (even better) use the new PowerQuery add-in, which is a free download for Excel 2010.

Comment by: Beckey (23-3-2015 16:41:45) deeplink to this comment

Thanks for the info, and answering so quickly :) I'll have a look at that add-in.

Comment by: John (2-4-2015 13:58:45) deeplink to this comment

Hi Jan.
I have a csv file with multiple sets of identically formatted data.
I want to try and get each set of data on a separate tab in excel.
Is there a simple trick to possibly trigger this?
Many thanks.

Comment by: Jan Karel Pieterse (2-4-2015 15:29:39) deeplink to this comment

Hi John,

For Excel it mostly is simpler to have all data on one sheet, from there it is far easier to summarize using pivottables and similar technology.

Comment by: Dean (14-4-2015 13:12:04) deeplink to this comment

Hi,I'm reading from a certain text file that only has 2 numbers "to begin with", 1 number in row 1 and another in row 2. In excel I am only ever reading the number in the second row of the text file and having it display in a cell in Excel. I have it set to update every minute. I have another program (labview) writing data to that same text file. It replaces the number in the second row and shifts all previous values down a row with the latest recorded number from labview at row 2 (The number in row 1 is bypassed intentionally). I only want excel to simply read and display whatever number happens to be in row 2 at any given time but no matter what i try it tries to take all the previous numbers and add them into excel shifting them into the cell below. Any help would be greatly appriciated!Dean

Comment by: Jan Karel Pieterse (15-4-2015 20:01:57) deeplink to this comment

Hi Dean,

I'm afraid it takes VBA programming to get that done. Is it a problem to have the data on a separate sheet and do the rest on another worksheet, just pulling the data you need from cell A2?

Comment by: Swami (13-5-2015 07:37:22) deeplink to this comment

Thanks. It was great help.

Comment by: bknows (3-6-2015 18:23:36) deeplink to this comment

Excellent work. Thanks!

Comment by: Drago (8-6-2015 10:44:03) deeplink to this comment

Hi,

Thank you. This article helped me a lot.

Comment by: Sado Cyrus (17-6-2015 14:24:13) deeplink to this comment

Imported pictures in Exel worksheet are frozen. I no longer import or delete existing pictures.

regards

Comment by: Marilyn (20-6-2015 03:01:57) deeplink to this comment

Greetings!
I have a text file that I need to convert into an EXCEL spreadsheet, turn into columns and delete a lot of unwanted information. The file is 10K pages, mostly of information I want to delete. Any suggestions on a quick fix?

Comment by: Jan Karel Pieterse (20-6-2015 16:16:57) deeplink to this comment

Hi Marilyn,

I suggest you to look at the Power Query add-in, which is a free download if you have Office 2010.

Comment by: Mark (17-9-2015 19:50:27) deeplink to this comment

After I click "Finish" I do not get the "Import Data" window so I can not select "properties." How can I get that window back. I assume I set a default the first time I imported data and now I'm stuck forever with it. Google has been no help.

Thanks.

Comment by: Jan Karel Pieterse (18-9-2015 11:10:26) deeplink to this comment

Hi Mark,

You can edit the settings by right-clicking the imported data and choosing "Data RangeProperties...".

Comment by: SALAU, Feyisetan Bidemi (3-10-2015 18:25:14) deeplink to this comment

Hi.
I do not have a suggestion, neither do I have any further questions. I am only sending this message to show my appreciation for the explanation found on this webpage - the procedures were very self-explanatory and properly detailed.
Thank you for sharing this knowledge with others.

Comment by: TJ (2-12-2015 11:08:36) deeplink to this comment

exactly what I was looking for, great post!

Comment by: BobJ (2-12-2015 17:36:42) deeplink to this comment

In the Text Import Wizard, on Step 2, do you know of any way to increase the number of rows being displayed in the Data preview? It only seems to show 5 lines, and -- while I know you can scroll down to see additional line -- it would be helpful if I could simply see more lines in the preview. Any ideas?

Thanks!

Comment by: Jan Karel Pieterse (7-12-2015 11:12:33) deeplink to this comment

Hi Bob,

No, I'm afraid you cannot.

Comment by: ANDREW SETCHFIELD (11-12-2015 14:27:18) deeplink to this comment

Hi.
I have a google product feed in notepad that is all .txt i want to download this into excel and then upload it to google as the feed for my shop. After I have done all the editing that is.. I thought I had got it right but google does not like it. what am I doing wrong except everything:/

Comment by: Jan Karel Pieterse (11-12-2015 15:51:29) deeplink to this comment

Hi Andrew,

I suggest to open the output in Excel, not change anything, save-as to text format and then open both files in e.g. Notepad to compare their content. That way you can compare what difference there is.

Comment by: david (16-12-2015 23:13:53) deeplink to this comment

How can i import CSV file into a worksheet table (and load to DataModel) by APPENDING to the existing raws AND NOT REPLACE them with new ?
with data CSV import and with PowerQuery it always replace them...

also, I will need to execute a duplicate removal from the table based on multiple key fields (same date, same name, same type etc... )

any advice is appreciated
Thanks

Comment by: Jan Karel Pieterse (18-12-2015 11:54:30) deeplink to this comment

Hi David,

If you're willing to use PowerQuery, that should be easy to accomplish.

Comment by: nick (8-2-2016 10:04:01) deeplink to this comment

hi ,

i am importing data from a web page to excel , my problem is that it overwrites or deletes data when a certain amount of rows has been reached , this is odds data from a betting website and there are changes happening every minute , so excel puts on top the new data and some of the old data gets lost , can you help me please ?

thanks

Comment by: Jan Karel Pieterse (8-2-2016 11:03:49) deeplink to this comment

Hi Nick,

If you want to keep old data, the only way to do that is by using some VBA code that copies the old data somewhere before refreshing the new data (or immediately after a refrehs).

Comment by: Peter Cohen (15-2-2016 04:14:19) deeplink to this comment

Hello JK - I had posted a question on ExcelForum.com and someone pointed me to your site. My original question was:
I have a real problem where I receive a comma separated report several times a day.
The data in the report looks something like this: "ABCD","00001",XYZ,"000034"
The report is used by operators who - if possible - just want to click on the report to open it in Excel.
I had appended the reports with ".csv" which comes up fine in Excel, but truncates the numbers.
So, the data on the spreadsheet looks as follows: "ABCD","1","XYZ","34"
This is wrong - I need the complete numbers.
I know how to open the file the correct way by making the filetype ".txt" and going through the "Text Import Wizzard"
However, this is tedious - so, my question is:
Is there a way to create a dummy filetype - let's say ".dum" - and create rules (macro/anything) in Excel which will recognize this filetype and open the file the way I need it opened, i.e. as plain text in this case?

I looked at your solution here - it comes close but does't completely solve my issue. Is there a way to create a macro using your steps and have the macro run every time I open a file of type ".dum" as in my example?

Comment by: Jan Karel Pieterse (15-2-2016 09:17:44) deeplink to this comment

Hi Peter,

What about using the steps outlined in this article, setting the import to refresh on open and prompt (if needed). Then you might instruct your users to simply open the same Excel file each time, which will automatically refresh when they open it, prompting for the csv if necessary.

Comment by: Peter Cohen (15-2-2016 12:40:53) deeplink to this comment

Hello JK,

That works - thanks so much.

Groeten,
Peter Cohen

Comment by: Peter Cohen (15-2-2016 12:58:22) deeplink to this comment

Actually, one more question.
Is there a way to open the text file "Read Only".
I want to make sure that the users don't accidentally overwrite it.

Comment by: Jan Karel Pieterse (15-2-2016 14:40:54) deeplink to this comment

Hi Peter,

The text file itself is imported into Excel. Hence you wouldn't overwrite it when you save the Excel file because you would save an excel file, not a text file.

Comment by: Stacy Moss (31-3-2016 20:59:18) deeplink to this comment

I have been trying to use the Get External Data feature to import a text file with 4 million rows of data. I am using Excel 2010. I can get the first 1,048,576 rows to come in. I am struggling with going beyond that. Whenever I enter row 1,048,577 as my starting point for the next data set, the wizard only pulls over that exact row of data instead of filling in the rest of the sheet. Can you offer a suggestion of how I could get the rest of the data to come over?

Comment by: Jan Karel Pieterse (1-4-2016 11:06:07) deeplink to this comment

Hi Stacy,

I think you'll be better off using PowerQuery for that purpose (Get and Transform in Excel 2016) and have it add the data to the datamodel (but not to Excel since there are too many rows). Then you can create a pivottable from the datamodel.
Alternatively, import that data into MSAccess and create a query from Excel as the source of a pivottable.

Comment by: Bridget (8-6-2016 16:10:45) deeplink to this comment

Hi

I need some help in regards to importing text data into excel by using macro function and other people being able to use the macro where I change the layout and print. From excel I go data, get data, use delimited, tab, date and press finish. When I run the macro from my end it works. When I get someone else to run the macro, it doesnt work for them because of 'source unknown'. My data source is saved in my mac in all 'my files'. Should it be saved somewhere else? I just have no idea how other people can run my macro on their system. Please help.

Comment by: Jan Karel Pieterse (9-6-2016 07:33:30) deeplink to this comment

Hi Bridget,

Do the other people have access to that file? Are they also using a Mac?

Comment by: Anwar (20-6-2016 08:21:18) deeplink to this comment

Hi, This is Anwar

I have a large .txt file which contains data in thousands of rows with 20 entries in one row. Due to nature of data i want excel to import it such that sixth, eleventh, sixteenth, twenty first rows etc... are all imported in one row. How can i do that? Anybody please....

Comment by: Jan Karel Pieterse (21-6-2016 16:06:22) deeplink to this comment

Hi Anwar,

Probably it is easier to use a little macro for that. Alterntively, perhaps Powerquery can do this too.

Comment by: Nirat Joshi (13-9-2016 17:55:35) deeplink to this comment

Thank you so much this was very much helpful.

Comment by: Robert (20-9-2016 07:27:33) deeplink to this comment

I have a workbook that on open imports a text file. This is done using a macro in ThisWoorkBook. I want to save it to a new workbook without the text link. I do not know how to unlink the text file so that the saved new workbook opens with the data saved within the cells and not dependent on the linked text file.
Thank you.

Comment by: Jan Karel Pieterse (20-9-2016 11:27:47) deeplink to this comment

Hi Robert,

Suppose your worksheet is called "Data":

Comment by: Robert (20-9-2016 17:35:41) deeplink to this comment

Thank you Jan it works!

Can I also copy over BUTTONS and the source format?
Thanks again

Comment by: Jan Karel Pieterse (20-9-2016 17:50:06) deeplink to this comment

Hi Robert,

Well yes, you could use another take at this and just copy the worksheet and subsequently delete the connection:

Comment by: Robert (20-9-2016 19:22:03) deeplink to this comment

Thank you Jan.
With your help it now works properly!

Comment by: Robert (20-9-2016 20:19:16) deeplink to this comment

Hi Jan,

One last thing...
Can I add a workbook name to the 'Worksheets(1).Copy'?
Say name="Sep-2016"

Thank you

Comment by: Jan Karel Pieterse (21-9-2016 09:29:52) deeplink to this comment

Hi Robert,

Sure:

Comment by: Robert (21-9-2016 21:48:52) deeplink to this comment

Thank you Jan.

Comment by: Robert (23-9-2016 17:55:25) deeplink to this comment

Hi Jan,

How can I copy the macros in PERSONAL.XBSM to the active workbook? (PERSONAL.XBSM is already open)

Thank you

Comment by: Jan Karel Pieterse (26-9-2016 10:13:28) deeplink to this comment

Hi Robert,

If you need everything form a certain module, you can simply drag the module (in the project explorer) from one file to the other. Otherwise, just selecting the text of the macro and opy pasting it into a module of the other file works.

Comment by: nuke (2-10-2016 14:33:27) deeplink to this comment

Hi Jan,

I am trying to record a macro which tries to import several .txt files and place in the corresponding cell values of my excel. Now comes the issue.
If my .txt has one row of output it is importing and placing in the right cell but if my .txt has more than one row of output as shown below:
Sample.txt:

123-345-678-91-0
123-345-678-910
345-678-819-000

it is importing but it is placing in three corresponding rows(A1,A2,A3) instead of that particular cell(A1). Hope i made my query clear.

Please help me. Im stuck on this for a long time.

Thanks in advance,
Nuke

Comment by: Jan Karel Pieterse (3-10-2016 06:22:52) deeplink to this comment

Hi Nuke,

Just import the file as-is, but on a separate worksheet and have that cell A1 on your current sheet point to cell A1 on the new import sheet.

Comment by: Sukhman (4-10-2016 05:58:59) deeplink to this comment

Hi

I saved a multiple tab excel workbook to .txt accidentally.
Is there any way I can retrieve all the tabs and put them back to excel ?

Comment by: Jan Karel Pieterse (4-10-2016 10:15:49) deeplink to this comment

Hi Sukhman,

I'm afraid not.

Comment by: Robert (13-10-2016 16:31:02) deeplink to this comment

Hi Jan,
I would like to import a PDF hyper link to the ActiveCell. The PDF resides in a folder on the C drive. C:\releve\xxx.pdf. Once imported I would like to click on the cell and open the pdf.
Can you please get me started.
Thank you
-Robert

Comment by: Jan Karel Pieterse (14-10-2016 16:30:38) deeplink to this comment

HI Robert,

I'm not sure I understand. Do you want a hyperlink in Excel so that if clicked the PDF opens? If so, click the cell in question and press control+k to create the link.

Comment by: Robert (14-10-2016 19:33:39) deeplink to this comment

Thank you Jan.
As usual, you make it so easy!
-Robert

Comment by: Robert (27-10-2016 20:27:12) deeplink to this comment

Hi Jan,
I have many hyperlink cells in my worksheet. They point to different PDF files on my hard drive.
The link names are very long.
Example 'c:\Releves\ArchiveGuill\June2016\Results.pdf'
Can I rename the cell to just 'June2016' keeping the link address in tact.
Thank you

Comment by: Jan Karel Pieterse (28-10-2016 12:13:37) deeplink to this comment

Hi Robert,

You should be able to, using the HYPERLINK function.

Comment by: Rx_ (2-11-2016 14:19:51) deeplink to this comment

I have large numbers of SQL Server reports in Text.
They have the data sorted in extremely specific order/ format. About 100 in total.

The customer wants them converted into Excel, with breaks at changes in categories, bold and double line dressing, and the Sum Formulas on each Sub Total and the Final Total, with the typical breakout and roll ups of Outline view.

I will need to program the infrastructure for this.
It would seem that someone would sell a tool for what I have to create. Even if it cost a few hundred, it would be worth itj. Any ideas?

Comment by: Jan Karel Pieterse (2-11-2016 14:48:38) deeplink to this comment

Hi Rx,

Seems to me (without knowing the data) this calls for a pivottable?

Comment by: Josh (11-1-2017 22:40:53) deeplink to this comment

Thanks. This article was very informative. For days I have been taking downloaded text files, and then converting them into Excel, just to start the process from the top. I couldn't tell you how much time this saved, and how much of a Rockstar I looked in front of my boss. This will now be my go-to website for everything Excel. This a great article. Thanks

Comment by: Jan Karel Pieterse (12-1-2017 10:49:59) deeplink to this comment

Hi Josh,

Thank you for your compliments, glad I could make your day and make you look good!

Comment by: Sara (14-1-2017 08:51:36) deeplink to this comment

I am refreshing a worksheet from a text file. This method works fine if my new file has the same or more data rows, but if it has fewer rows, old rows remain after my import. I have tried all combinations of the Properties options, to no avail..is there a non-VBA solution to this? I am using Excel 2010 but ideally it should work in 2003...

Comment by: Jan Karel Pieterse (14-1-2017 17:26:34) deeplink to this comment

Hi Sara,

Normally this should not happen, all three options in the settings mention something about removing data when less rows are returned. Try manually deleting all rows but the first and then refresh.

Comment by: Sara (15-1-2017 03:55:53) deeplink to this comment

Hello Jan, thanks for your reply.
yes, deleting first works fine of course; it is the same as overwriting a file with fewer rows, but I don't want to have to do that.
My old data has 13 rows, going from columns A to BN (67 cols). My new data has the same 67 cols, but only 7 rows.
The two Insert options add my new file's data from cell A1 BUT move the old data over to column BO (col 68).
It makes no difference ticking the Fill down formulas box.
The Overwrite option overwrites, with the new data, the 7 rows of my old data but leaves the old rows 8-13 in place. It will only clear unused cells within the rows it is importing.
Looks like I need a VBA solution if I don't want to manually delete my data first?

Comment by: Jan Karel Pieterse (16-1-2017 11:27:19) deeplink to this comment

Hi Sara,

I would redo the import on a brand new worksheet, it looks like something's wrong with this import as it does not behave the way it is supposed to.

Comment by: Tauqeer (2-2-2017 10:38:13) deeplink to this comment

Hi

I tried to import txt file in excel 2010, I couldn't get the right format as it was in txt file, is it possible I can send you the txt file and the excel file so you can point out the problem. Please advise.

Comment by: Jan Karel Pieterse (2-2-2017 10:51:10) deeplink to this comment

Hi Tauqeer,

Sure, go ahead.

Comment by: Gene (15-2-2017 02:31:34) deeplink to this comment

I'm trying to import a text file. Go through the steps that you mention. Hit "finish" and the result is all of my data appearing on one single line. What am I doing wrong?

Comment by: Jan Karel Pieterse (15-2-2017 07:11:52) deeplink to this comment

Hi Gene,

You have probably not selected the right delimiter in the second step of the wizard.

Comment by: Rose (10-5-2017 17:06:59) deeplink to this comment

Is there anything like this available for 365... with NON .txt files? My local install of Excel has the Data > From Text File option, but it is missing the "all file type" enable and it won't let me bring in files that aren't .txt or .csv
Thanks!

Comment by: Jan Karel Pieterse (10-5-2017 17:30:59) deeplink to this comment

Hi Rose,

Usually you can simply type *.* and press enter to get all files. Does that work?

Comment by: Jose Cordova (22-5-2017 07:29:26) deeplink to this comment

Hello, I tried import text directly from web page pasting URL in file name to open. In some pages it gives error while in other not, if you know can you please why excel sometimes do not allow to open data from from web sources?

Comment by: Jan Karel Pieterse (22-5-2017 09:53:37) deeplink to this comment

Hi Jose,

I think this has something to do with restrictions in the browser which is used to fetch the webpage in Excel. It does not seem to support all webpages.

Comment by: Robert (29-9-2017 14:10:51) deeplink to this comment

Hi Jan,
I lose partial text formating when saving and closing my .xlsx file.
It seems to have stopped working after about 400 rows with many cells containing partial text formating.
I am using MS Office 365.
I format say the first word only in a cell as BOLD and after saving and re-opening the file, BOLD format is lost.
Seems to work ok on a workbook with only a few text formatings.
Any ideas?
Thank you.
-Robert

Comment by: Jan Karel Pieterse (29-9-2017 17:07:56) deeplink to this comment

Hi Robert,

To be honest I wouldn't know. I hardly ever use partial cell format because it is a pain to work with. Excel isn't very good at this.

Comment by: Yvette Allen (17-11-2017 11:41:30) deeplink to this comment

I am trying to import a text file (from Notepad). I select data, then 'from csv or txt", I select the file and then click import but the wizard does not show up. I went into options, data and turned on the wizard and tried again but it still does not show. What am I doing wrong?

Comment by: Jan Karel Pieterse (17-11-2017 15:27:22) deeplink to this comment

Hi Yvette,

That is odd. I have no idea why it wouldn't show!

Comment by: Joe L (27-11-2017 22:52:21) deeplink to this comment

Does anyone know an easy way to bring multiple rows into one row. I have a nasty fixed width report that I need to condense into one row per record in excel. There are four rows of data for each record that need to be combined into one row and then broken out into a column for each field. This seems to be very difficult to do unless you're a VBA ninja. Thanks for the help.

Comment by: Jan Karel Pieterse (30-11-2017 10:42:20) deeplink to this comment

Hi Joe,

Can you share two rows of sample data?

Comment by: Pete Maz (18-10-2018 16:32:09) deeplink to this comment

Hi. I've been trying to find a way to import the delimited text file which was last saved to a directory. I have a directory with over two dozen files, each of which is date in the title;
i.e.
\Dir\TextFile_01012001
\Dir\TextFile_02012001
     etc.
I want to import only \Dir\TextFile_17012001 and ADD it to the files already delimited into excel.

Any ideas, please

Comment by: Jan Karel Pieterse (18-10-2018 16:57:48) deeplink to this comment

Hi Pete,

If you're onm Office 365 I'd recommend using "New Query", "From File", "From Folder" from the Data tab of the ribbon.

Comment by: Excel User (4-3-2020 17:10:00) deeplink to this comment

Is there any way to use the saved query definition without clicking on refresh?
I import two files each week in the same format (one from a legacy system and one from the new). The import layout is exactly the same.
Ideally I would like to be able to go through the import data from text process, but just pull up the saved layout.

Comment by: Jan Karel Pieterse (5-3-2020 10:31:00) deeplink to this comment

Hi Excel user,

WHat exactly do you mean by "use the query definition"?

Comment by: bittarelli (30-11-2020 15:58:00) deeplink to this comment

when i download data into text wizard it appears as "blink" It no longer shows the file I am attempting to download in the usual delimeters etc. Why is that. Yesterday I tried to create a file that would save headings and since then I cannot download files correctly.

Comment by: Jan Karel Pieterse (30-11-2020 16:53:00) deeplink to this comment

Hi bittarelli,

Hard to say, have you tried this with several txt or CSV files?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.