Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Import textfiles
Deze pagina in het Nederlands

Importing text files in an Excel sheet

In this article I'll explain how to ease importing of .txt, .prn and/or .csv files into an existing sheet in Excel.

This article has been published on one of the Microsoft blogs: Importing Text Files in an Excel Sheet

Introduction

If you've ever tried to open a text file, you have probably used the Text import wizard.

If you have ever opened a CSV file, you have probably noted you can't set up how it gets imported. The text import wizard does not start for these file types.

What if you have the situation where you get files with similar structure, which need the same text import settings over and over?

How do you import the data from those files into the same worksheet each time, without having to re-define the import settings over and over again?

I'll tell you.

Setting up which file (type) to import

First select the sheet where your data needs to appear. Note that the following steps differ for the various Excel versions.

Excel 2013/2010/2007

On the ribbon, click the data tab and then click the "From Text" button on the "Get External data" group.

 Import data from text files on Excel 2007/2010 ribbon.

The "From Text" button, located on the Data tab of the ribbon.

Select your file from the next dialog:

Import text file dialog

The import text file dialog.

Click Import. (continue to read after the section on Excel 2003 and older)

Excel 2003 and older

From the menu, select Data, Get External Data, Import Data. The following dialog opens:

The select data source dialog of Excel 2003

The select data source dialog of Excel 2003, selecting source type.

In this dialog, choose the "Text files" option and then navigate to your file:

Select data source dialog, selecting a file

Select data source dialog, selecting a file

All versions: defining the import settings

So far we've selected what to import. Now it is time to define what settings we need for the import. This section applies to all Excel versions as of Excel XP (and maybe even older). The screenshots shown here may look slightly different from yours, but the principle is the same.

Click the Open button. The text import wizard opens up:

Step 1 of the Text Import Wizard, define file type

Step 1 of the Text Import Wizard, define file type.

In this example I have selected to import a delimited file and set the file origin to Windows (ANSI). Click Next when you're happy with the settings.

Step 2 of the wizard allows us to define the delimiters. I selected Comma:

 Step 2 of the Text Import Wizard: define delimiters

Step 2 of the Text Import Wizard: define delimiters

Click Next again to go into the third step, where you can select a format for each column of your file. I changed the date format of the first column to dmy order. Click a column to set up its formatting.

Step 3 of the wizard, Define column formats

Step 3 of the wizard, Define column formats

You can click the advanced button to set up details like the decimal and thousands separators (note I switched them here):

The Advanced text Import Settings dialog

The Advanced text Import Settings dialog

Note that any changes made in this dialog apply to all columns.

After you finished defining all columns, click the Finish button. Excel opens the Import Data dialog, asking where to put the results. Select the proper location.

The Import Data dialog

The Import Data dialog

Hold your horses, don't click OK yet! Click the properties button instead. The External Data Range Properties dialog opens up. There are some very important settings to be made here!

The External Data Range Properties dialog

The External Data Range Properties dialog

Note that I have highlighted some important features in this dialog. I'll discuss them below.

Save Query Definition

We wouldn't want to redo the settings each time, so lets keep this one checked!

Prompt for the file name on refresh

Checking this box forces Excel to prompt you for a file name each time you hit the refresh button. Leave this box unchecked if you are importing the same file over and over.

Overwrite existing cells with new data, clear unused cells

What selection is best here depends on your needs entirely. The first two will force Excel to push existing data down and pull existing data up, depending on the size of your file.

I recommend NOT to put anything below an area where you are importing data;
use a dedicated worksheet for this purpose.

Fill down formulas in columns adjacent to data

A very useful option. If you are planning to do calculations on each row, this option ensures each row of data will have your formulas.

Hit OK if you're happy with the settings.

Import your data

Finally, click OK to have your data imported. My sheet looks like this:

Results after importing the textfile

Results after importing the textfile

Now that you have properly set up your import, save your file!

Refresh your data

All of this was meant to predefine an import routine you have to do over and over again. So how do you import a new file? Easy. Click any cell within your sheet that holds data of a previous import.

Next, we'll need to split this up in two sections again, because the various Excel versions differ in how it is done.

Excel 2013/2010/2007

On the Data tab, find the group called "Connections". Click the dropdown "Refresh All" and select "Refresh:

The Refresh All button on the ribbon

The Refresh All button on the ribbon

If you have set the option to prompt for a file, Excel will open the appropriate dialog. Select your file and click Import.

Excel 2003 and older

In Excel 2003, the refresh button can be found in two places.

1. On the Data menu (you need have a cell selected within an imported table):

The refresh button on the data menu

The refresh button on the data menu

2. On the External data toolbar:

The refresh button on the External data toolbar

The refresh button on the External data toolbar

So there you go, all set!

Conclusion

Importing text files into Excel can be a labor-intensive process if you're going through the same steps over and over again. With proper use of the data import options Excel offers, you can save yourself quite some work and at the same time improve consistency.

This article has given you an overview of how this should  be set up.

Feel free to add comments and/or questions below.

 


Comments

All comments about this page:


Comment by: Kevin (3/11/2010 10:18:56 AM)

Thanks this is very informative.

 


Comment by: Christopher McGath (3/11/2010 1:52:57 PM)

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 (4/28/2010 8:24:37 AM)

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 (4/28/2010 10:18:54 AM)

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 (5/4/2010 1:38:21 AM)

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 7:36:03 AM)

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 (5/8/2010 6:23:50 AM)

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 (5/9/2010 10:59:54 PM)

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 (5/10/2010 1:43:21 AM)

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 (5/10/2010 2:13:03 AM)

Hi Joe,

Chip Pearson already wrote such a routine, see:

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

 


Comment by: Joe (5/16/2010 5:50:41 AM)

Thanks Jan. Will give it a bash.

 


Comment by: Andrew (6/15/2010 4:46:25 PM)

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 (6/16/2010 12:29:47 AM)

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 (6/17/2010 5:54:16 AM)

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 (6/18/2010 1:35:58 AM)

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 (6/18/2010 5:36:59 AM)

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 (7/29/2010 2:36:50 AM)

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 (8/16/2010 3:22:29 AM)

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 (9/10/2010 4:39:28 AM)

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 (9/10/2010 7:35:43 AM)

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 (9/16/2010 1:07:54 PM)

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 (9/17/2010 5:02:21 AM)

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 (10/7/2010 2:55:36 AM)

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 (10/7/2010 6:29:26 AM)

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 (11/23/2010 7:25:56 AM)

Thank you so much. This was a life saver.

 


Comment by: Belinda Montgomery (12/1/2010 8:27:17 AM)

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

 


Comment by: Jan Karel Pieterse (12/1/2010 9:26:47 AM)

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 (12/14/2010 2:09:28 PM)

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 (12/15/2010 12:07:16 AM)

Hi Mahvash,

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

Sub Copy2Other()
    Worksheets("Sheet1").UsedRange.Copy
    With Worksheets("Sheet2")
        .Paste .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
End Sub

 


Comment by: Eric Spence (12/16/2010 2:01:03 PM)

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 (12/17/2010 5:34:30 AM)

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 (1/11/2011 12:14:51 AM)

separate date month year format pls condition reply me.

 


Comment by: Jan Karel Pieterse (1/11/2011 4:59:02 AM)

Hi Vidhya,

Please ask your question here:
www.eileenslounge.com

 


Comment by: Guru (1/11/2011 6:07:22 PM)

Hi Vidhya,

Come to excel-macros@googlegroups.com

You will get answers to all your doubts.

Have a nice day.

Guru

 


Comment by: Caitlin (8/2/2011 9:57:04 AM)

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 (8/22/2011 2:53:33 AM)

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 (8/30/2011 5:17:03 AM)

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 (8/30/2011 7:42:06 AM)

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 (9/1/2011 9:20:28 AM)

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 (9/2/2011 3:07:11 AM)

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 (9/7/2011 3:00:07 AM)

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 (9/7/2011 7:56:51 AM)

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 (9/14/2011 6:47:05 AM)

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 (9/14/2011 7:23:39 AM)

Hi Hans,

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

 


Comment by: tanababa@gmail.com (10/8/2011 3:07:33 AM)

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 (10/9/2011 11:52:01 PM)

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 (10/15/2011 6:49:50 AM)

(@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 (10/24/2011 3:35:56 AM)

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 (10/30/2011 3:21:22 AM)

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 (10/30/2011 3:15:26 PM)

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 (10/31/2011 1:07:10 AM)

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 (11/7/2011 7:08:50 PM)

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 (11/8/2011 12:55:15 AM)

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 (1/11/2012 9:14:08 AM)

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 (1/11/2012 4:31:02 PM)

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 (1/11/2012 11:42:38 PM)

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 (1/11/2012 11:44:21 PM)

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 (1/21/2012 2:00:28 AM)

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 (1/22/2012 10:23:45 PM)

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 (2/9/2012 3:56:52 PM)

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 (2/10/2012 1:26:37 AM)

Hi John,

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

Option Explicit

Sub UpdateConnections()
    Dim sOldFolder As String
    Dim sNewFolder As String
    Dim oQt As QueryTable
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        For Each oQt In oSh.QueryTables
            If Left(oQt.Connection, 4) = "TEXT" Then
                sOldFolder = oQt.Connection
                Exit For
            End If
        Next
        If Len(sOldFolder) > 0 Then Exit For
    Next
    'a text connection looks like this:
    'TEXT;C:\Users\Administrator\Documents\SiteVisits.txt
    'Remove the first part to obtain the old folder
    sOldFolder = Split(sOldFolder, ";")(1)
    'remove the filename
    sOldFolder = Left(sOldFolder, InStrRev(sOldFolder, "\"))
    'now ask for the new folder
    sNewFolder = InputBox("Please enter the new folder name", "Update connections", sOldFolder)
    If Len(sNewFolder) > 0 And sNewFolder <> sOldFolder Then
        ReplaceLocations sOldFolder, sNewFolder
    ElseIf sNewFolder = sOldFolder Then
        MsgBox "Old and new folder the same; changing locations cancelled", vbInformation + vbOKOnly
    Else
        MsgBox "Changing locations cancelled", vbInformation + vbOKOnly
    End If
End Sub

Sub ReplaceLocations(sOldFolder As String, sNewFolder As String)
    Dim oQt As QueryTable
    Dim oSh As Worksheet
    If Right(sNewFolder, 1) <> "\" Then
        sNewFolder = sNewFolder & "\"
    End If
    For Each oSh In Worksheets
        For Each oQt In oSh.QueryTables
            oQt.Connection = Replace(oQt.Connection, sOldFolder, sNewFolder)
        Next
    Next
End Sub

 


Comment by: John Wilkinson (2/10/2012 10:47:51 AM)

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 (2/18/2012 7:59:02 AM)

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 (2/20/2012 7:42:16 AM)

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 (3/5/2012 9:17:37 AM)

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 (3/6/2012 12:42:19 AM)

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 (3/10/2012 1:08:23 AM)

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 (3/12/2012 1:03:12 AM)

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 (3/13/2012 5:51:29 AM)

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 (3/13/2012 6:03:38 AM)

Hi Kurt,

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


If Len(DIR("c:\users\username\File.txt"))=0 Then
'File is not there
Else
'File is there
End If

 


Comment by: Kurt (3/13/2012 6:39:49 AM)

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 (4/11/2012 3:24:28 PM)

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 (4/12/2012 4:45:53 AM)

Hi Sarah,

See:

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

 


Comment by: Renata (4/20/2012 1:59:32 PM)

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 (4/23/2012 11:21:55 PM)

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 (5/3/2012 12:51:49 PM)

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:


liCopyStartLocation = 1
lsSqlLocation = "http://abc.xyz.com/MY_SQL.txt"
    With ActiveSheet.QueryTables.Add( _
            Connection:="TEXT;" & lsSqlLocation, _
            Destination:=Range("$A$" & liCopyStartLocation))
        .Name = "QueryList"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "#"
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With


I had this person try to do a manual import of the text file, with the same result. However they can import a file that is stored locally, so I'm thinking it has something to do with security settings on their laptop??

Any idea why this one person can't access the Sharepoint file?

 


Comment by: Jan Karel Pieterse (5/4/2012 7:31:35 AM)

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 (5/4/2012 7:48:23 AM)

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 7:16:39 AM)

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 (5/7/2012 10:21:35 AM)

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:


Sub FromText()

'
' FromText Macro


End Sub


I did try to import the file "From Web". This actually did the import without error, so it seems not to be a security issue, rather something in the Text import vs. web. However that doesn't allow me to specify delimiters, so I don't want to import "From Web"

Here is the recorded macro from text:


Sub FromText()
'
' FromText Macro
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://lillynetcollaboration.global.lilly.com/sites/LRLIT/Strategy/LRLIM/IMTF_MRS_Queries/MRS_SQL.txt" _
        , Destination:=Range("$A$1"))
        .Name = "MRS_SQL_1"
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "#"
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Thanks for replying. -Brenda.

 


Comment by: Jan Karel Pieterse (5/8/2012 12:15:01 AM)

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 (5/17/2012 8:44:50 AM)

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 (5/18/2012 7:20:23 AM)

Hi Trey,

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

Sub ProcessFiles()
    Dim vFilename As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim lCount As Long
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("text files (*.txt),*.txt", , "Please select the file(s) to import", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilename) To UBound(vFilename)
        RefreshDataAndCopyElsewhere CStr(vFilename(lCount))
    Next
End Sub

Sub RefreshDataAndCopyElsewhere(sFilename As String)
    With ThisWorkbook.Worksheets(1).QueryTables(1)
        .Connection = "TEXT;" & sFilename
        .Refresh False
        .Destination.CurrentRegion.Copy
    End With
    With ThisWorkbook.Worksheets(2)
        .Paste .Range("A" & .Rows.Count).End(xlUp).Offset(1)
    End With

End Sub


 


Comment by: Trey (5/18/2012 9:02:52 AM)

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 (5/20/2012 11:20:44 PM)

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 (5/25/2012 7:27:48 AM)

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 (5/26/2012 2:57:42 AM)

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 (5/30/2012 8:07:27 PM)

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 (5/31/2012 8:48:30 AM)

Hi VG,

YOu can test for the length of the file:

If FileLen("c:\users\VG\SmapleFIle.txt")>0 Then
'File contains something
Else
'File is empty
End If

 


Comment by: VG (6/1/2012 6:46:08 PM)

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 (6/2/2012 4:02:47 PM)

Hi VG,

Can you post your code?

 


Comment by: Bertrand (6/6/2012 3:48:35 PM)

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 6:45:53 PM)

Hi Bertrand,

You're welcome!

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

 


Comment by: Lepraik (7/30/2012 2:38:08 PM)

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 (7/31/2012 10:13:00 AM)

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 (8/1/2012 7:26:40 PM)

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 (8/3/2012 3:55:46 PM)

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 (8/3/2012 4:09:55 PM)

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 (8/7/2012 10:58:49 AM)

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 (9/10/2012 11:25:01 PM)

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 (9/11/2012 9:52:43 AM)

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.

Sub Demo()
    Dim oImportRange As Range
    Dim oCell As Range
    Set oImportRange = Worksheets("Import").Range("A1")
    For Each oCell In Selection
        With oImportRange.QueryTable
            .Connection = "TEXT;" & oCell.Value
            .Refresh False
        End With
        oCell.Offset(, 1).Value = oImportRange.Cells(2, 2).Value
    Next
End Sub

 


Comment by: Darlene (9/21/2012 10:56:01 PM)

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 (9/24/2012 11:45:10 AM)

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 (10/4/2012 5:27:49 PM)

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 (10/5/2012 2:04:26 PM)

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 (10/22/2012 5:39:12 PM)

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 (10/22/2012 8:21:52 PM)

Hi Stephen.

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

 


Comment by: Stephen (10/22/2012 8:42:34 PM)

That did the trick! Much appreciated!

 


Comment by: Dave (10/29/2012 10:16:20 PM)

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 (10/30/2012 8:58:09 AM)

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 (11/7/2012 10:31:14 AM)

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 (11/8/2012 1:58:25 PM)

Hi Vignesh,

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

 


Comment by: Praveen (11/18/2012 7:46:37 PM)

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 (11/19/2012 6:48:09 AM)

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 (12/7/2012 12:27:43 AM)

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 (12/7/2012 1:07:10 PM)

Hi Josh,

Please go here to ask your question:

www.eileenslounge.com

 


Comment by: Lisa (12/26/2012 5:53:53 PM)

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 (12/28/2012 2:25:58 PM)

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 3:57:56 PM)

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 (1/2/2013 1:48:37 PM)

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 (3/14/2013 7:05:19 PM)

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 (3/15/2013 1:11:37 PM)

Hi Y Lu,

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

 


Comment by: Y Lu (3/16/2013 5:33:10 AM)

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 (3/16/2013 3:16:34 PM)

Hi Y Lu,

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

Sub GetFileName()
    Dim sFileName as String
    sFileName = activecell.QueryTable.Connection
    sFileName = Right(sFileName, Len(sFileName)-5)
    MsgBox sFileName
End Sub

 


Comment by: Y Lu (3/18/2013 7:43:18 PM)

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 (3/19/2013 8:16:27 AM)

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 (3/20/2013 11:11:46 AM)

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 (3/20/2013 4:50:56 PM)

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 (3/21/2013 9:36:56 AM)

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 (3/21/2013 11:09:20 AM)

Hi Ken,

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

 


Comment by: Kamran (4/5/2013 10:05:31 AM)

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 (4/5/2013 4:45:20 PM)

Hi Kamran,

Have you tried clicking that button on an empty worksheet?

 


Comment by: Patricia Pinkston (4/10/2013 8:43:18 PM)

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 (4/12/2013 1:01:03 PM)

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 (4/30/2013 10:11:24 AM)

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 (5/1/2013 1:41:59 PM)

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 (5/2/2013 6:30:42 PM)

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 (5/3/2013 11:31:48 AM)

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 (5/3/2013 3:35:20 PM)

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 (5/3/2013 4:19:04 PM)

Hi Doug,

You could do it like so:

Sub UpdateAll()
    UpdateQT Worksheets("Fix30")
    UpdateQT Worksheets("Fix10")
    Sheets("Conforming Update").Select
End Sub

Sub UpdateQT(oSh As Worksheet)
    Dim sFile As String
    Dim vFile As Variant
    sFile = oSh.QueryTables(1).Connection
    sFile = Replace(LCase(sFile), "text;", "")
    If Len(Dir(sFile)) = 0 Then
        MsgBox "File cannot be found, please find this file in the next dialog:" & vbNewLine & vbNewLine & sFile
        vFile = Application.GetOpenFilename("Text files (*.csv),*.csv", , "Please find file " & sFile)
        If Not TypeName(vFile) = "Boolean" Then
            oSh.QueryTables(1).Connection = "TEXT;" & vFile
        Else
            Exit Sub
        End If
    End If
    oSh.QueryTables(1).Refresh BackgroundQuery:=False
    oSh.Range("A7:I32").Sort Key1:=oSh.Range("A8"), Order1:=xlAscending, Header:= _
                         xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                         DataOption1:=xlSortNormal
End Sub

 


Comment by: Doug Foster (5/3/2013 5:42:03 PM)

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 (5/23/2013 2:30:49 AM)

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 (5/23/2013 10:42:55 AM)

Hi mohamed,

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

 


Comment by: Cristina (6/27/2013 1:23:53 AM)

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 (6/27/2013 7:27:52 AM)

Hi Cristina,

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

 


Comment by: Tony Rudmik (7/11/2013 6:23:30 PM)

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 (7/11/2013 9:19:29 PM)

Hi Tony,

Please ask your question here:
www.eileenslounge.com

 


Comment by: Ali (7/16/2013 7:06:59 AM)

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 9:13:57 PM)

Hi Ali,

Probably you inadvertantly deleted the import definition.

 


Comment by: Denis Filipetti (9/3/2013 6:31:37 PM)

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 (9/4/2013 8:14:35 PM)

Hi Denis,

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

 


Comment by: Denis Filipetti (9/4/2013 8:22:23 PM)

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 (9/4/2013 10:24:16 PM)

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 (9/5/2013 8:34:35 PM)

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 (9/17/2013 9:23:59 PM)

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 (9/18/2013 10:18:11 AM)

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 (9/18/2013 10:58:48 AM)

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

Bob

 


Comment by: mus (10/24/2013 5:58:38 PM)

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 (10/25/2013 9:08:27 AM)

Hi Mus,

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

http://www.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 (11/1/2013 3:03:27 PM)

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 (11/2/2013 4:08:49 PM)

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 (11/2/2013 8:55:08 PM)

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 (11/5/2013 8:10:16 PM)

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 (11/6/2013 10:02:13 AM)

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 (11/20/2013 1:05:47 AM)

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 (11/25/2013 4:00:56 AM)

Thanks for this article, really helpful!

 


Comment by: Gordon (1/8/2014 4:58:16 PM)

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 (1/9/2014 10:52:33 AM)

Hi Gordon,

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

 


Comment by: Rachael (1/12/2014 2:41:43 AM)

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 (1/12/2014 4:50:51 PM)

Hi Rachael,

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

 


Comment by: Lachmi (1/20/2014 6:18:57 PM)

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 (1/20/2014 7:37:16 PM)

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 (2/5/2014 5:45:17 PM)

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 (2/5/2014 7:24:25 PM)

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 (4/9/2014 12:16:54 PM)

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 (4/9/2014 1:01:35 PM)

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 (4/26/2014 4:57:45 PM)

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 (4/28/2014 9:56:02 AM)

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 8:50:44 AM)

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 AM)

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 (6/11/2014 9:32:56 AM)

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

 


Comment by: Jan Karel Pieterse (6/11/2014 4:35:33 PM)

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: samir (8/1/2014 9:40:04 AM)

hi
when i change form16(incometax) text to excel then,all data goes to zigzag pattern.in simple text file is easily converted but complex file is not done by this method.
please reply asap

thanks
samir.ranjan

 


Comment by: Jan Karel Pieterse (8/12/2014 10:42:38 AM)

Hi Samir,

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

 


Comment by: Thomas (9/13/2014 12:59:43 PM)

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 (9/13/2014 5:12:08 PM)

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 (10/20/2014 8:58:49 PM)

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 (10/21/2014 8:27:12 AM)

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:

Sub RedirectConnections()
    Dim oConn As WorkbookConnection
    Dim sConn As String
    For Each oConn In ActiveWorkbook.Connections
        If oConn.Type = xlConnectionTypeTEXT Then
            sConn = oConn.Ranges(1).QueryTable.Connection
            sConn = Replace(sConn, Left(sConn, InStrRev(sConn, "\") - 1), ActiveWorkbook.Path)
            oConn.Ranges(1).QueryTable.Connection = "TEXT;" & sConn
        End If
    Next
End Sub


In the ThisWorkbook module:

Private Sub Workbook_Open()
    Application.OnTime Now, "RedirectConnections"
End Sub

 


Comment by: paul (11/20/2014 4:54:55 PM)

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

 


Comment by: katie (11/27/2014 9:07:18 PM)

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 (11/28/2014 1:27:52 PM)

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 (12/4/2014 9:45:06 PM)

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 (12/18/2014 11:41:29 PM)

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 (12/19/2014 11:55:03 AM)

Hi Michael,

You could do something like this:

Sub RefreshThem()
    Dim vFileName as Variant
    vFilename = Application.GetOpenFilename("text files (*.txt),*.txt", , "Please select the file(s) to import", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilename) To UBound(vFilename)
        With Worksheets("Sheet1")
            .Querytables(1).COnnection = "TEXT;" & vFilename(lCount)
            .Querytables(1).Refresh False
            .UsedRange.Copy
            With Worksheets("Sheet2")
                .Paste .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
            End With
        End With
    Next
End Sub

 


Comment by: Sasi (2/24/2015 2:59:30 PM)

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 (2/24/2015 3:39:06 PM)

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 (3/2/2015 4:17:55 PM)

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 (3/2/2015 5:29:04 PM)

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 9:46:40 PM)

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 (3/5/2015 12:04:37 PM)

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 (3/11/2015 9:50:50 PM)

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

 


Comment by: Jan Karel Pieterse (3/12/2015 6:15:27 AM)

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 (3/17/2015 4:34:05 PM)

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 (3/17/2015 4:51:17 PM)

Hi Rickard,

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

 


Comment by: Beckey (3/23/2015 1:52:51 PM)

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 (3/23/2015 3:00:09 PM)

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 (3/23/2015 4:41:45 PM)

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

 


Comment by: John (4/2/2015 1:58:45 PM)

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 (4/2/2015 3:29:39 PM)

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 (4/14/2015 1:12:04 PM)

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 (4/15/2015 8:01:57 PM)

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 (5/13/2015 7:37:22 AM)

Thanks. It was great help.

 


Comment by: bknows (6/3/2015 6:23:36 PM)

Excellent work. Thanks!

 


Comment by: Drago (6/8/2015 10:44:03 AM)

Hi,

Thank you. This article helped me a lot.

 


Comment by: Sado Cyrus (6/17/2015 2:24:13 PM)

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

regards

 


Comment by: Marilyn (6/20/2015 3:01:57 AM)

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 (6/20/2015 4:16:57 PM)

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 (9/17/2015 7:50:27 PM)

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 (9/18/2015 11:10:26 AM)

Hi Mark,

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

 


Comment by: SALAU, Feyisetan Bidemi (10/3/2015 6:25:14 PM)

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 (12/2/2015 11:08:36 AM)

exactly what I was looking for, great post!

 


Comment by: BobJ (12/2/2015 5:36:42 PM)

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 (12/7/2015 11:12:33 AM)

Hi Bob,

No, I'm afraid you cannot.

 


Comment by: ANDREW SETCHFIELD (12/11/2015 2:27:18 PM)

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 (12/11/2015 3:51:29 PM)

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 (12/16/2015 11:13:53 PM)

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 (12/18/2015 11:54:30 AM)

Hi David,

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

 


Comment by: nick (2/8/2016 10:04:01 AM)

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 (2/8/2016 11:03:49 AM)

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 (2/15/2016 4:14:19 AM)

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 (2/15/2016 9:17:44 AM)

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 (2/15/2016 12:40:53 PM)

Hello JK,

That works - thanks so much.

Groeten,
Peter Cohen

 


Comment by: Peter Cohen (2/15/2016 12:58:22 PM)

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 (2/15/2016 2:40:54 PM)

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 (3/31/2016 8:59:18 PM)

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 (4/1/2016 11:06:07 AM)

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 (6/8/2016 4:10:45 PM)

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 (6/9/2016 7:33:30 AM)

Hi Bridget,

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

 


Comment by: Anwar (6/20/2016 8:21:18 AM)

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 (6/21/2016 4:06:22 PM)

Hi Anwar,

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

 


Comment by: Nirat Joshi (9/13/2016 5:55:35 PM)

Thank you so much this was very much helpful.

 


Comment by: Robert (9/20/2016 7:27:33 AM)

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 (9/20/2016 11:27:47 AM)

Hi Robert,

Suppose your worksheet is called "Data":

Sub Demo()
    With Workbooks.Add
        With .Worksheets(1)
            ThisWorkbook.Worksheets("Data").UsedRange.Copy
            .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        End With
    End With
End Sub

 


Comment by: Robert (9/20/2016 5:35:41 PM)

Thank you Jan it works!

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

 


Comment by: Jan Karel Pieterse (9/20/2016 5:50:06 PM)

Hi Robert,

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

Sub Demo2()
    Worksheets(1).Copy
    ActiveWorkbook.Connections(1).Delete
End Sub

 


Comment by: Robert (9/20/2016 7:22:03 PM)

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

 


Comment by: Robert (9/20/2016 8:19:16 PM)

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 (9/21/2016 9:29:52 AM)

Hi Robert,

Sure:

Worksheets("Sep-2016").Copy

 


Comment by: Robert (9/21/2016 9:48:52 PM)

Thank you Jan.

 


Comment by: Robert (9/23/2016 5:55:25 PM)

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 (9/26/2016 10:13:28 AM)

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 (10/2/2016 2:33:27 PM)

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 (10/3/2016 6:22:52 AM)

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 (10/4/2016 5:58:59 AM)

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 (10/4/2016 10:15:49 AM)

Hi Sukhman,

I'm afraid not.

 


Comment by: Robert (10/13/2016 4:31:02 PM)

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 (10/14/2016 4:30:38 PM)

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 (10/14/2016 7:33:39 PM)

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

 


Comment by: Robert (10/27/2016 8:27:12 PM)

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 (10/28/2016 12:13:37 PM)

Hi Robert,

You should be able to, using the HYPERLINK function.

 


Comment by: Rx_ (11/2/2016 2:19:51 PM)

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 (11/2/2016 2:48:38 PM)

Hi Rx,

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

 


Comment by: Josh (1/11/2017 10:40:53 PM)

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 (1/12/2017 10:49:59 AM)

Hi Josh,

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

 


Comment by: Sara (1/14/2017 8:51:36 AM)

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 (1/14/2017 5:26:34 PM)

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 (1/15/2017 3:55:53 AM)

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 (1/16/2017 11:27:19 AM)

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 AM)

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 AM)

Hi Tauqeer,

Sure, go ahead.

 


Comment by: Gene (2/15/2017 2:31:34 AM)

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 (2/15/2017 7:11:52 AM)

Hi Gene,

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

 


Comment by: Rose (5/10/2017 5:06:59 PM)

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 (5/10/2017 5:30:59 PM)

Hi Rose,

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

 


Comment by: Jose Cordova (5/22/2017 7:29:26 AM)

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 (5/22/2017 9:53:37 AM)

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 (9/29/2017 2:10:51 PM)

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 (9/29/2017 5:07:56 PM)

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 (11/17/2017 11:41:30 AM)

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 (11/17/2017 3:27:22 PM)

Hi Yvette,

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

 


Comment by: Joe L (11/27/2017 10:52:21 PM)

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 (11/30/2017 10:42:20 AM)

Hi Joe,

Can you share two rows of sample data?

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].