Import Text and Text Files into Excel

I frequently have to import data from an accounts report txt file into Excel
2K, via the Text Import Wizard, fixed width mode. Is there any way to save
the column width settings for future re-use?

Does anybody know of a way to automatically fire the Text Import Wizard
in excel. What I am trying to do is create an asp page for my users
which contains links to reports. These reports are text files which
need to be imported into excel. By clicking on the link my users will
open the file in excel and be prompted with the Text Import Wizard to
format the columns. As we run many thousands of different reports I
think that it would be impossible to predefine the columns, so this
must be done by the end user when they open it.
I have tried naming the text file as .csv and .xls, but they just open
with everything in the first column.

I'm having some trouble in Excel. If I create a plain text file like the following:

1,2,3
4,5,6
=sum(a1:a2), =sum(b1:b2)

Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications.

I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for...

Any good sugestions?

Regards

If I am in an excel spreadsheet and I try to open a .txt file, the test
Import wizard used to open so I could determine columns and data etc. Now,
when I try to open the file, the wizard does not open..excel opens with a new
spreadsheet. How do I get the wizard to open again? My firm re-imaged my
machine and all kinds of things are off.
--
Old Bob

This is probably wishful thinking, but is there a way to import one text file into Column A, for example, and another text file into Column B, so they line up next to each other ? They are large files, so I don't want to have to cut and paste, if possible.

Thanks for any help !

I can NOT import a simple comma delimited file below in to Excel, althought I
used " as text qualifier, and COMMA as delimiter. Excel (version 2003)
persistenly parse it as having 3 fields rather than having only 2 fields.

"Income - $10,000 - $19,999", "Income - $20,000 - $29,999"

Could you please enlighten me? Thanks!

I remember my original Excel, when I opened a txt file, I would be allowed to delineate where columns ended, and move the column markers around. I am using Execl 2000, and I am not allowed to set column separators, other than by specifying the delimiter character.

My problem is that I am importing an error log that only has spaces to separate columns, but also has spaces in the descriptive section that should be one column.

What happened to Excel allowing you to combine columns? Is there any way around this problem?

I want to import text files into excel. I need all columns to be imported as text, so as to keep them looking exactly as they are in the text file. I had no problem at all doing this manually, by choosing, in the import text wizard to import all columns as text, and not as general, which is the default. But I would like this to be done automaticaly. Is there any way to change the default for importing text to "text" instead of "general"?
Thanks to anyone who can help me on this.

Hi!

I am trying to use MS Query to first combine text files into one table of data and then import it to Excel. I have a data with identical amount of columns and with identical column headers in every file.. what would be the best way to do this..? thanks!

Hi everyone,
I have imported a tab delimited text file into excel. One of the columns consists of a string of text - a comment section. Within this column, there are periods and for some reason it seems that Excel is treating these periods as row separators. Does anyone know how I can prevent Excel from separating the text into rows?
Thanks in advance for any insights.
mkang

I'm trying to import a text file into excel. The file is tab delimited and uses double quotations as a text qualifier. One of the fields in the text file is a note field and every now and then the person entering the note uses a carriage return (hits the enter key) while doing so. When this comes through in the text file it looks like this...

"this is a note.
this is the second line of the note"

My understanding is that everything between the two instances of text qualifers should be treated as a single value and the carriage return disregarded. However when I import it the data comes in exactly as it is above which causes field types and column headings to not match up.

Can anyone tell me a way around this short of manually deleting each carriage return?

I have numerous PDF documents in a folder (could convert them to JPEG or PNGs if necessary). I want to run a macro that imports each document into a master workbook so that each document has its own worksheet. I DO NOT want hyperlinks... I want to import them either as images in text boxes or any other way that allows me to view the image automatically when I go to the worksheet, instead of clicking a hyperlink. Thanks!

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
bbowler@bigelow.org |
+-------------------+---------------------------------------------------+

Hi,

I have a pipe delimited file, which has commas in some of the text. This file that I am importing is a .txt file and I am finding that when I import it all of the information is in the first column just how it should be apart from when there is commas in any of the sentances of the information that I had imported it places the text after the comma in the next cell.

How can I stop that from happening or how can I change it back to commas from tabs.

I hope you can help me here, as I have tried doing a script for it using PHP, but I am not having much success at doing that either.

Thanks!

Hi

I have a comma seperated value text file and want to import to Excel (easy) but I want each row of the text file to be inserted into a specific row of the spreadsheet based on the first value in each row of the text file.

Why I need this (for info)...

The text file is produced from web form submissions and so rows differ month on month (different people submitting at different times).

My first worksheet contains the imported text file and subsequent worksheets are linked to each row of the first.

Get it?

Is this possible?

thanks

I import a text file that some cells have blanks because there suppressed
repetative data.
Meaning if column a had
boy


bug

car
=======================================
I want someway to fill blanks like
boy
boy
boy
bug
bug
car

I know if I import into excel I could select data edit goto special blanks
enter = in active cell point to cell above and press CTRL + ENTER simultainiously.

The text file is too large for excel so I need that function in Access.
Thank you for your anticipated response.
Yaneckc@aol.com

Hello

I am writing a macro that changes the filepath for an existing text file import as a QueryTable. I want to set the connection parameters to variables and reapply them with a different filepath.

Code:

            
Dim DataType() as Variant
Dim Platform as Integer
Dim TableCell As Range
Dim TabDelimiter as Boolean, CommaDelimiter as Boolean

Set TableCell = Wksht.Range("A10")           
ReDim DataType(1 To UBound(TableCell.QueryTable.TextFileColumnDataTypes))
            
DataType = TableCell.QueryTable.TextFileColumnDataTypes
TabDelimiter = TableCell.QueryTable.TextFileTabDelimiter
CommaDelimiter = TableCell.QueryTable.TextFileCommaDelimiter
Platform = TableCell.QueryTable.TextFilePlatform

With Wksht.Range("A10").QueryTable
    .Connection = ConnectPath
    .TextFileColumnDataTypes = DataType
    .TextFilePlatform = Platform
    .TextFileParseType = xlDelimited
    .TextFileTabDelimiter = TabDelimiter
    .TextFileCommaDelimiter = CommaDelimiter
End With

This works fine for all parameters except TextFilePlatform. I want to set it to the code page (eg. 437 for IBM-PC, 65000 for Unicode)

VBA help says that

XlPlatform can be one of these XlPlatform constants.
xlMacintosh
xlMSDOS
xlWindows

and .QueryTable.TextFilePlatform has a number that refers to one of these constants.

However, the macro recorder assigns a code page number to the TextFilePlatform property.

How do I assign a variable to original code page and not have it reset to the default Windows (ANSI)?

I have a text file that has about 50 fields and I am importing it into a table that has just 10 fields. The first time that I import it, I import it into a New Table. I skip over 40 of the fields. This works fine. I end up with exactly the fields that I want. And I save the import specification so I can use it again.

The next time I try to import the file (e.g., the next day), I do /File/Get External Data/Import and then in the Import Text Wizard I press the Advanced button then the Specs button and select the Import spec that I want to use. It shows all the field names and has the tick boxes showing that most of them will be skipped. I say OK and proceed. Everything seems to go fine until I say I want to store my data in an existing table and name it. Access then gives an error message saying that the first field on the text file does not exists in the table and then says that the data was not imported. Now that field was specified to be skipped. So why is this causing a problem?

Hope you can help me how to code the following
How to import a csv file where it should only import only columns with a specific text (header) as it can sometimes differs from columns

Eg in cell

A1 Client Name
B1 Contracted
C1 Address
D1 Sales
etc
Lets say I want only the columns Client Name, Sales. Etc imported to save speed and space

Can anyone help me pls?

Thanks

I have a file exported from an accounting system that has no extension. The type just says "file". When I had Windows XP, I added the .txt extension (in the file name itself without opening) and was able to import the file into Access without issue. With Windows 7, it seems that this practice leads to the following error when the file is imported to Access:

"The changes you requested to this table were not successful because they would create duplicate values in the index, the primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I know that none of the fields of the destination table are indexed and there is no primary key. The table has not changed in between the windows conversion. In addition, I get the same error if I import into a new table.

My current work around is to open the .lis file in Excel and save it as text with no further modification to the file. When this is done, the file imports flawlessly. I would like to avoid this as the file is very large and it takes some time to open.

I've searched this out quite a bit and haven't found a very good answer, other than something related to the primary key or index (which again i do not have).

Any help or suggestion is greatly appreciated.

I've been googling around on ways to import multiple text files without just specifying the directory in the macro. I sort of tried to combine two codes together, one that lets me select the text files, and one that lets me put all the data onto one spreadsheet, but it's not working. Any ideas?

Code:

Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    Dim myDir As String, fn As String, txt As String, y, delim As String
    Dim a(), n As Long, t As Long, maxCol As Integer
 
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    sDelimiter = "|"
    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If
 
    ReDim a(1 To Rows.Count, 1 To Columns.Count)
    delim = vbTab
    fn = Dir(FilesToOpen & "\*.txt")
    Do While fn  ""
        txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(FilesToOpen & "\" & fn).ReadAll
        x = Split(Replace(txt, vbCrLf, delim), delim): t = t + 1: n = 1
        a(t, n) = fn
        For i = 0 To UBound(y)
            n = n + 1: a(t, n) = y(i)
        Next
        maxCol = Application.Max(maxCol, n)
        fn = Dir
    Loop
    ThisWorkbook.Sheets(1).Range("a1").Resize(t, maxCol).Value = a
 
 
 
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
 
End Sub

I get a type mismatch error

I have a text file in notepad with 4 rows for each set of data. Is there a way for me to get the data all into 1 row in the text file or all into 1 row in excel.

Thanks

P

Hi

Currently I need a macro to import text file into a excel file and this is the code below. However every time when the text file is import, a new excel file will be created, can anyone help me to change the code, so that the text file will always import into the same excel file name " monthly standard working file".
Code:

      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      FileName = InputBox("Enter the Text File's name, e.g. test.txt")
      If FileName = "" Then End
      FileNum = FreeFile()
      Open FileName For Input As #FileNum
      Application.ScreenUpdating = False
      Workbooks.Add template:=xlWorksheet
      Counter = 1
      Do While Seek(FileNum) <= LOF(FileNum)
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          Line Input #FileNum, ResultStr
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
          If ActiveCell.Row = 65536 Then
             ActiveWorkbook.Sheets.Add
          Else
             ActiveCell.Offset(1, 0).Select
          End If
          Counter = Counter + 1
      Loop
      Close
      Application.StatusBar = False
     
      End Sub

Hi there,

I need some help on a Macro. I have more than 160 Text files, I need to copy the text files into seperate sheet of excel. You all know it is a time consuming.

Today I google for the macro, luckly i found this one:

Code:

 
Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:="|"
    x = x + 1

    While x

Hi everyone,

I have put together a macro that will successfully loop through text files in a given folder and import all the data within each file into an excel sheet. The data comes from MS Word forms. The data from the forms are then saved comma-deliniated text files.

The problem I face is that if anyone uses a return carriage when inputting their data in the Word form (before transformation into the text file) then during the import into excel at each return carriage a new row is begun. Therefore as a precautionary mesaure I am forced to open each text file, search for any return carriages and replace them with a space so that each text file will only occupy one row.

Can anyone assist me in resolving this issue in vba? I have thought of two possible options: (1) open each text file, perform the replace and then save it again replacing the old file; (2) somehow indicating to import to ignore all return carriages. I don't know which is more feasible (or is a better approach). Also if anyone has any ideas on how to make the code more efficient I would happy to hear their thoughts.

Thanks in advance.

abousetta

Hi,I am looking for Excel VB code where i can import data from multiple text(notepad) files into multiple worksheets of a new excel workbook. Also the name of the tabs should be as the names of the text files.Any Suggestions???

I have tried to import some data from Access to Excel and the text in the larger fields are being cut off. How do I fix this?

Hello again everyone.

I've got another problem that is over my head. I'm trying to take a MODFLOW output file(basically just a text file) that is ~ 9 million lines and extract only a set portion of data from it for import into excel.

The data that I need from the text file always begins at the line that starts with
Code:

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

Note: the {14 spaces} is just that, 14 blank characters. The time step number changes throughout the file so there is other text after this, but the beginning is always those characters. I need the data from that line through the next 13565 lines copied/imported into excel.

Once this data has be imported, I would like to continue to move through the text file until the next occurrence of
Code:

 {14 spaces}HEAD IN LAYER{3 spaces}1 AT END OF TIME STEP

is found and repeat the procedure, only importing it into a new worksheet. This would continue until I reach the end of the file.

I did a search and found this code, but the whole reading from a text file is so foreign to me I'm not sure I know what it is doing.

Code:

Sub snb()
  Open "C:\ExcelTemp\M4A00P13.out" For Input As #1
    sq = Split(Input(LOF(1), #1), "              HEAD IN LAYER")
  Close #1
 
  For j = 0 To UBound(sq)
    With ThisWorkbook.Sheets.Add
      sn = Split(sq(j), vbCrLf)
      .Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
    End With
  Next
End Sub

Any help or direction is greatly appreciated. Thanks all.

Hi all, I need to know how to avoid WebQuery to recognize formats on the fly, and just import everything as text.

Because of this "feature" Im loosing data when querying a web page, and must fix it manually...

For example, in the web page there is a number like "500.000" but after query it land as "500" in worksheet ( point "." is thousands separator) so i need the query to get it as text so i dont loose any digits.

Some things Ive tried already and no working:
- Search many forums and google, cannot find working solution.

- Setting cells format to text prior to query. Not working (excel will change format to number anyway)

- Change format to Text after query. Not working. Digits are lost for good, so nothing to do after the query.

- .WebDisableDateRecognition. This works only for dates, not for numbers.

- .PreserveFormatting. Not working .Tried true and false, no difference.

- .WebFormatting. Not working. All combinations, same result.

- Change Windows thousand separator in Control Panel. This might work, but code wont be fully portable to other users using different configuration.

So problem is very simple, i need web query to import EVERYTHING as plain text. Dont need Excel to be smart in this case...


Thanks in advance!!

Nestor

Hi

I'm using Excel 2007

Every time I use the Text Import Wizard, it seems to deafult certain settings like the Delimiter as Space - is there anyway to change the default settings? I never use spaces in this and its irritrating to have to constantly change this.

Similarly, it always sets the default column type to General, is there an option to change this to Text (or whatever) before importing?

Thanks in advance

Gaz

There are a lot of times where I would need to save data in a text file before I can use the "Get External Data: From Text".

How can I import my clipboard so the text import wizard can be used?

I have a data file that I need to download and convert from text to xls to then do further manipulation. When I manually fire up excel, open the text file and leave all the text import wizard defaults I can then work with the resulting xls file programmatically. When I use the following code I get errors when I try to open the resulting xls file. I haven't been able to find much about the different options in the OpenText file command so I'm not sure what properties I'm setting incorrectly that the wizard automatically sets correctly. It's a tab delimited file. Can anyone tell me what options I need to set to get it converted correctly? Thanks.

Code:

Excel.Application application = new Excel.Application();
                object missing = System.Reflection.Missing.Value;
                System.Threading.Thread.CurrentThread.CurrentCulture = new 
CultureInfo("en-US");
                System.Threading.Thread.CurrentThread.CurrentUICulture = new 
CultureInfo("en-US");
                application.Caption = "Test";
                application.Visible = true;
                application.Workbooks.OpenText
                (
                    sFileToSave,
                    missing,
                    1,
                    Excel.XlTextParsingType.xlDelimited,
                    Excel.XlTextQualifier.xlTextQualifierNone,
                    missing,
                    true,  //TAB
                    missing,
                    false, //COMMA
                    missing,
                    missing,
                    missing,
                    missing,
                    missing,
                    missing,
                    missing,
                    missing,
                    missing
                );

                application.Save(sExcelToSave);
                application.Quit();
                application = null;

Hi,

Is there anyway to invoke the Import Text Wizard from the command line ? What I am looking to do is something like

excel myfile.txt /invokeTextWizard

TIA,
JC

im looking for a macro that will use the import feature that will import text and delimit it.

i want to manually choose the file. all i know is workbooks.open filename: function, not the import

My question is is it possible to automate the Text Import Process. The reasons why I want to do this is I get approximately 25 ".txt" files daily which I currently manually import into excel using the wizard which is time consuming and repetitive then I have to formatted and saved a an ".xls" file. I would like to automate the formatting part too.

Examples of the files names below

W080410.txt
W090410.txt
W100410.txt

See Post "VBA to removed unwanted rows" Posted on 19/04/201009:23 PM full detail of the above files.

Any assistance will be greatly appreciated.

How can I import a whole text field into a single cell in Excel, without the line breaks making Excel start a new row? I'd like the whole text document in a single cell.

I know I could copy and paste but I want to automate it.

Eventually, I would like to write a script to go through a whole directory and import each text file into a different cell of my spreadsheet. But small steps...

Hi Experts,

I need to import certain lines from a large text file, say 79496 lines or even may become more in future. I would like to import only certain rows from the file, e.g. Line 79196 to the bottom. I tried to record a macro to get the codes, but found that the StartRow value for importing a text file could only be an integer, which means 79196 will not be a value that Excel allows. Do you have any idea how to do this? Thanks!

p.s. Considering the appending size of this text file, I really don't want to import the file as a whole.

I am trying to import a text file into excel.

Right before the import takes place, I get a dialog box that states:

the text file contains more data than will fit on a single worksheet.

To continue and import as much of the data as will fit, click ok. You can then import the rest of the data by repeating the import operation on another worksheet and using the text import wizard to exclude data already imported.


Is there anyway to code this with doing this automatically into the next sheet?


thanks for any help

Josh

Greetings and merry christmas for anyone celebrating it.

I want to open or import a text file. It has range of up to 10,000 and row to up to 200. I'm using excel 2003 at office.
I've tried import the data using text import wizard (try using excel 2007). However not all column can be displayed in step 3.

Is there anyway I could just open the text file and all data will be transposed?

If this issue has been raised please help me to where is it located?

Thanks.

I am importing a text file into access. I have to import it as 'fixed width' format. This does not give me the option of selecting the 'first row as headers', therefore the headers are field1, field2 etc and the actual headers from the text file import as the first data row.

Is there a way round this?, a quick way to change the data row to be the header row, or an update/append query solution.

Any help much appreciated.

I have space delimited text files that need to be imported into Excel. These files have several hundred columns, but no more than 100 rows. I need to import the files into Excel but cannot due to the column limitation. I am looking for some VB code that will transpose the files on import so the column limitation will not be an issue.

Hi,

I have a large text file, 75mb, that I would like to import into Excel but it goes well beyond the 65536 lines available.

Is there a process to get these files into excel? File splitter?

Regards

Luke

I am trying to import information that I want in one cell but because
of multiple colons and semicolons it is automatically put in different
cells. Is there any way to turn this off and/or turn off the text
import wizard?

Hi Everyone,

I'm tryng to convert a text file that is comma separated into a csv. I've done this hundreds of times in the past but one thing really puzzles me and I'd appreciate any guidance on how I can fix this.

In the text file I have some bank details whic show sort code and bank account number. These are separted in teh text file correctly with commas as in teh example below.

,11-11-11,01234567,

When I open the text file using excel and import it. I select the format of Text for all columns and excel displays the data. Now to my problem, in the column where the sort code is contained "AH" (in the example above that is 11-11-11) Some of the cells show these as a date format such as 11/06/1941 while other cells show this information correctly formated as 11-11-11. I can see no logical reason why for example these cells format incorrectly

AH2
AH3
AH10
AH28
AH30

Those are just a few examples to show the apparent randomness of the issue. The text file itself looks 100% error free.


It's as if Excel ignores the format command on some cells during the import. Can anyone help me with this problem?

I'm using Excel 2007

Thanks

Steve

Hi all,

Importing text files into excel is farely simple having googled round the topic. The text file i'm importing is awfully big though, so I wondered if I could just read in the columns I need to speed things up.

(I could import the whole thing then delete the columns I don't want after but that won't make the import stage any quicker)

So does anyone know how I can import just, say, the 1st,2nd, 4th and 10th columns for example?

cheers!!!!!
Dan