Free Microsoft Excel 2013 Quick Reference

Convert Multiple text files to excel files

Hi,
I would like to convert multiple text files to excel files respectively..
I have nearly 800 text files which needs to be converted to excel files individually..

Can any one guide me please


Post your answer or comment

comments powered by Disqus
I am trying to convert muliple text files that have a csv format into an excel file where each text file is a different tab in the excel file. I would like the name of each individual excel file tab to be the name of each seperate text file.

The text files have this general format:

" 1",278.6
" 2",278.8
" 3",277.6
" 4",278.9
" 5",277.4
" 6",278.7
" 7",276.2
" 8",277.6
" 9",277.8
" 10",278.4

.. all the way to "80", however, the last row of each text file has "0 ","0 " which comes after "80". There are a total of 789 text files.

I would like each excel tab to have two columns displaying the two values - 1 278.6 without parathesis or commas.

Thanks for the help!

How do you convert a text file to excel using macro??????????

Hello,
I need to convert several hundred text files to excel, format the text files, and then save them as excel.
What I have is a folder with all of the text files and an excel sheet with the file name of each of the text file in column 1, the folder path in column 2 and .txt in column 3. I then try to run this macro to open each text file, edit it, save it as an excel and then repeat. When I do though I get an error 1004 saying "excel cannot find the text file to refresh this external data range"
When I just run the formatting part, plugging in the file names, it works fine for one file. And the other part of the code works fine when the formatting section is not in place. I just cant get them both to work at the same time.
Any help would be awesome! I havent used VBA very much and don't really know what to do to fix the problem.
-Sean


	VB:
	
 TexttoExcel() 
     '
    Dim curbook As Workbook 
     ' curbook is the current workbook
    Dim x As String 
     ' x is going to be the file path
    Dim y As String 
     ' y is the name of the file
    Dim i As Long 
     ' i is the counter
    Dim lr As Long 
     ' I think this stands for last row
     
    With Application 
         
        .DisplayAlerts = False 
        .ScreenUpdating = False 
         
    End With 
     
    Set curbook = ActiveWorkbook 
     
    lr = Cells(Rows.Count, 1).End(xlUp).Row 
     
    For i = lr To 2 Step -1 
         
        y = Range("B" & i).Text & Range("A" & i).Text 
         
        x = y & Range("C" & i).Text 
         
         ' This is the macro to open the file
        Workbooks.OpenText Filename:=x, Origin:= _ 
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _ 
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _ 
        TrailingMinusNumbers:=True 
         
         ' This is how I want to edit the text files
        With ActiveSheet.QueryTables.Add(Connection:= _ 
            "TEXT;x", Destination:= _ 
            Range("$A$1")) 
            .Name = "y" 
            .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 = 3 
            .TextFileParseType = xlFixedWidth 
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 
            .TextFileConsecutiveDelimiter = False 
            .TextFileTabDelimiter = True 
            .TextFileSemicolonDelimiter = False 
            .TextFileCommaDelimiter = False 
            .TextFileSpaceDelimiter = False 
            .TextFileColumnDataTypes = Array(1, 1, 1) 
            .TextFileFixedColumnWidths = Array(31, 47) 
            .TextFileTrailingMinusNumbers = True 
            .Refresh BackgroundQuery:=False 
        End With 
         
         'This next part closes the file
        ActiveWorkbook.SaveAs Filename:=y & ".xls", _ 
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 
         
        ActiveWindow.Close 
         
         'This re-activates the workbook with list of files
         
        curbook.Activate 
         
    Next i 
     
    With Application 
         
        .DisplayAlerts = True 
        .ScreenUpdating = True 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a text file de-limited with tab. I need to convert the same file to
excel and use chart options to draw a graph. Though i am able to import the
text file in to excel file i am unable to draw graph with that data. Please
help me do it.

Good day, i stay for a couple of days reading this forum, but i can't find how to solve my problem.

I have this code

	VB:
	
[FONT=monospace]Sub CombineTextFiles()[/FONT] 
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

Import multiple text files to 1 column

Hello All,
I have been using the following code to import text files into 1 excel sheet. This code takes file 1 and imports it into Column A. The next file it puts in column B. the problem is I need code to all the txt files into column A? is there a way to make this macro import everything into 1 column?

excel Version = 2007
Sub Opentext()


Dim fpath As String
Dim fname As Variant



fpath = "C:logtocap"
fname = Application.GetOpenFilename _
(filefilter:="TXT Files (*.*), *.txt", MultiSelect:=True)

For X = LBound(fname) To UBound(fname)

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& fname(X), Destination:=Cells(1, X))

.SaveData = True
.TextFilePlatform = 437
.Refresh BackgroundQuery:=False

End With

Next X

End Sub


Dear All,

I have attached 2 files. I need to transfer (using a macro), the data from text file to excel file.

For every new text file the data comes in the first empty column of the excel file.

Can somebody help me with program ... ?

As, not all the data from text file is to be copied to the particular column, I understand that a way of doing it can be that first the whole .txt file is copied to excel sheet and the particular data is then pasted in the required cells. Finally, the whole pasted data can be deleted.

Also, is there a possibility of using the "Application.GetOpenFilename" command or some similar command whereby the required text file can be searched and then data imported from it ?

Thank you in advance
Regards
Kanwar Arora

hi

i want to copy data from text file to excel sheet

for ex

the data int the text file is

1 0 1 0 1
0 1 0 1 0
1 0 1 0 1

we have get this data into excel sheet like this

A B C D E
1 1 0 1 0 1
2 0 1 0 1 0
3 1 0 1 0 1
4
5

So,Plz help me regarding this

thanks
sree

When import text files to Excel, Column Data format by default is General format. How could I change the default setting to Text format instead. It is time consuming to change the column setting from General to Text column by column during the importing process.

Hello All,

I am doing experiments and I have to copy certain values from text file to excel. The number of experiments are hung and for each experiment, I have to do copy values from text file and paste it to excel each time. I want to use macro so that once I will run command the values for given components should paste to it's desire position in excel. For example in text file values are written as
experiment.txt----------------------
methane 20
ethane 40
Phenol 40
experiment.xlx-----------------------
methane
ethane
Phenol
---------------------------

I am trying to use macros but couldn't do it. Does anybody know's how to do it?
thanks in advance

I am lookign for a way to convert text files with over 65000 lines of data to excel in a manner that would split the data to multiple worksheets. Does anybody know of a way to do this? Preferably a free way, but i'm open to other ideas.

Hello -
I hope I am posting this to the correct discussion group.

When converting a .tsv file to Excel using the Text Import Wizard > Column
Data Format > Step 3, which is "Do not import column (Skip)," is there any
way I can globally exclude columns? I have 50 columns to exclude, and
clicking each column individually and excluding each column one-by-one takes
a lot of time. In other words, is there a way I can exclude columns in
groups vs. one at a time?

My .tsv files are too large to convert to Excel and delete the columns there.

Thanks a lot in advance,

I'm trying to convert a text file with multiple rows (10) of repeated data.
The "Text to Columns" function in Excel can only sort one row. Is there a way
in Excel where I can format it to convert these data into just one row?

I am trying to import multiple text files form a specific directory (C:Documents and settingsuserdesktoptest) into a new excel workbook, pasting each text file onto its own sheet in order. The text files are numerically named 25, 500, 1000, 2000, 3000, etc.. up to 40000.

The naming of the sheets is not important, but the text files must be placed into the sheets in order.

Ultimately, I would like to run a script that checks my test folder automatically every 10 minutes for text files, and if found, puts them all in the excel workbook and saves it, then runs a set of macros which I have already written to process the workbook into reports.

For now, I just need to know how to write a script to call up check the folder for text files, if found then call up excel and paste in order to sheets in a workbook, then save the workbook. If no files are found, I need it to wait 10 minutes and then run the check again.

I am using Excel 2007.

Here is the code I have so far:
Sub LoadSpaceDelimitedFiles()


Dim idx As Integer
Dim fpath As String
Dim fname As String

    idx = 0
    fpath = "c:documents and settingsuserdesktoptest"
    fname = Dir(fpath & "*.txt")
    While (Len(fname) > 0)
        idx = idx + 1
        Sheets("Sheet" & idx).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))
            .Name = "a" & idx
            .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 = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileOtherDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            fname = Dir
        End With
    Wend
    
End Sub
When I run the macro, nothing happens. I have text files loaded into the test folder, but there appears to be no activity when the code is run and no errors.

Thanks in advance.

Hi

I have a .txt file I need to import into csv/excel, I'm struggling to align the columns etc and the result is garbage. The text file is created from Crystal reports.

Any ideas?
Am I able to upload the text file onto this site?

Thanks in advance

Is it possible to import several Text files into Excel through one step? I
realize that each file will open a seperate workbook, but if I could cut out
the step of opening every file individually it would save a lot of time.

The files are ASCII format (text delimited), which were converted from an
*.SBF file. The SBF files I am referring to are datalogging files generated
from the software we use for our mechanical testing rig. The test computer
and software is supplied by Servotest
(http://www.servotest.com/homepage.html). I am looking to just increase my
efficiency on importing the data, since I perform this task regularly.

Thank you for your help-
Joe

Dear Friends,

I am very much disappointed and unable to find any answer to my this problem, that means this file has to be converted to excel, I have tried finding the solution on net, any one on the board will please come forward to help me in this project of converting this file to excel.

I do hereby attach the file to be converted, and the formated / converted excel sample file.

Please some one help me to sort this ?

I found a macro that will import multiple text files into Excel. However, the macro assumes that the data being imported uses the pipe character (|) as a delimiter between fields. However, my data does not have the pipe character. I've imported one of the text files manually and gotten what the fixed widths need to be (all text files are identical in layout). I've tried mycolwidths, but am doing something wrong. Here's the original code with delimter in it.... Thanks!

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 <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.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:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Is it possible to import several Text files into Excel through one step? I
realize that each file will open a seperate workbook, but if I could cut out
the step of opening every file individually it would save a lot of time.

The files are ASCII format (text delimited), which were converted from an
*.SBF file. The SBF files I am referring to are datalogging files generated
from the software we use for our mechanical testing rig. The test computer
and software is supplied by Servotest
(http://www.servotest.com/homepage.html). I am looking to just increase my
efficiency on importing the data, since I perform this task regularly.

Thank you for your help-
Joe

Hello

I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.

What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.
Sub text_to_excel()

    Workbooks.OpenText Filename:= _
        "I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
    Range("C1:C2").Select
    Selection.Copy
    Range("M25").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("H1:H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O25").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("J1:J6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("U25").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("G20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AA25").Select
    ActiveSheet.Paste
End Sub


hey guys

i have many file text which i want to open in an excel workbook in a way that each text file will be displayed in an excel sheet.

is that doable? thanks for your help in advance
regards

I am trying to import the contents of multiple text files into excel, based on the text file name.

So for example, I have 5 text files in a test folder called SystemFile1, SystemFile2, SystemFile3, SystemFile4, SystemFile5 (these are just dummy file names for the purpose of this post).

Then I have an excel spreadsheet - in columnA cells 1-5 I have the values "File1" , "File2", "File3", "File4", "File5".

Is it possible to have a macro which reads in the values in column A and looks for a text filename containing part of this text (not a complete match), then load the contents of that text file into a cell in column B?

See attached example to hopefully explain the problem a bit clearer. I have no knowledge of macros other than what I have read on google so really struggling with knowing where to start with this. Any help is greatly appreciated!

Thanks

I have some code I have downloaded from Microsofts website.
It enables me to import a text file into excel by using a built in wizard.
However, I would like start the import file at a specific cell (B9).
I have included the code below but which bit of the code would I have to change or add in order to start the import at B9.
Here is the code and I am not that experienced with code so any help would be gratefully accepted.
If it helps, the worksheet which the text file is being imported to is called Import.

Sub ImportTextFile()
Dim DestBook As Workbook, SourceBook As Workbook
Dim B9 As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
Set DestBook = Import
Set DestCell = B9

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("B9"), Range("B9").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
SourceBook.Close False

End Sub

Hi Dave,
I have read many threads but couldn't find the code for this.
I have multiple text file and I want these files name(without .txt extension) & data(only one line data) into separate column in excel.
Pls help asap. Thanks in advance....


No luck finding an answer? You could always try Google.