mporting multiple text files into Excel with corresponding filenames
 

Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA

mporting multiple text files into Excel with corresponding filenames
 

Hi

Take a look at Ron de Bruin's site. He has lots of code examples to
consolidate files.
http://www.rondebruin.nl/tips.htm
--
Regards
Roger Govier

"avi" wrote in message
...
Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA

__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

mporting multiple text files into Excel with corresponding filenam
 

Specify the folder and try the below..

Sub Macro()

Dim strFolder As String, strFile As String
Dim strData As String, intFile As Integer, lngRow As Long

strFolder = "D:\PhoneNo"
If Right(strFolder, 1) < "\" Then strFolder = strFolder & "\"

strFile = Dir(strFolder & "*.txt", vbNormal)
Do While strFile < ""
intFile = FreeFile
Open strFolder & "\" & strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
lngRow = lngRow + 1
Range("A" & lngRow) = strFile
Range("B" & lngRow) = strData
Loop
Close #intFile
strFile = Dir
Loop


End Sub

--
Jacob


"avi" wrote:

Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA
.

mporting multiple text files into Excel with correspondingfilenam
 

Thanks Jacob! Works perfect!
Just one note (that I forgot to mention): data in my text files is
comma delimited.
Your code imports correctly filenames & data BUT all the data is
imported into one cell.
OK, I can use "text to columns" but would like to have it
delimited.... :-)

mporting multiple text files into Excel with corresponding fil
 

That was not mentioned in the original post..I have modified to suit your
requirement.

Sub Macro()

Dim strFolder As String, strFile As String, lngRow As Long
Dim strData As String, arrData As Variant, intFile As Integer

strFolder = "D:\PhoneNo"
If Right(strFolder, 1) < "\" Then strFolder = strFolder & "\"

strFile = Dir(strFolder & "*.txt", vbNormal)
Do While strFile < ""
intFile = FreeFile
Open strFolder & "\" & strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
arrData = Split(strData, ",")
lngRow = lngRow + 1
Range("A" & lngRow) = strFile
Range("B" & lngRow).Resize(, UBound(arrData) + 1) = arrData
Loop
Close #intFile
strFile = Dir
Loop


End Sub

--
Jacob


"avi" wrote:

Thanks Jacob! Works perfect!
Just one note (that I forgot to mention): data in my text files is
comma delimited.
Your code imports correctly filenames & data BUT all the data is
imported into one cell.
OK, I can use "text to columns" but would like to have it
delimited.... :-)

.

mporting multiple text files into Excel with corresponding fil
 

Jacob Skaria wrote:
* * arrData = Split(strData, ",")

That will fail if a quoted value contains a comma.

Phil Hibbs.

mporting multiple text files into Excel with corresponding fil
 

Why ?

"Phil Hibbs" wrote in message
...
Jacob Skaria wrote:
arrData = Split(strData, ",")

That will fail if a quoted value contains a comma.

Phil Hibbs.

mporting multiple text files into Excel with corresponding fil
 

That will fail if a quoted value contains a comma.
Why ?

I assume that the CSV that the OP has conforms to the Microsoft
format, and that code will convert this:

Phil Hibbs,Programmer,"$100,000"

into this:

[Phil Hibbs] [Programmer] ["$100] [000"]

where [] indicates a cell.

Phil Hibbs.

mporting multiple text files into Excel with corresponding fil
 

Fair point. If the OP's files include commas within values (not just as a
thousands separator) more work or a different approach is required.

Regards,
Peter T


"Phil Hibbs" wrote in message
...
That will fail if a quoted value contains a comma.
Why ?

I assume that the CSV that the OP has conforms to the Microsoft
format, and that code will convert this:

Phil Hibbs,Programmer,"$100,000"

into this:

[Phil Hibbs] [Programmer] ["$100] [000"]

where [] indicates a cell.

Phil Hibbs.

mporting multiple text files into Excel with corresponding fil
 

I would probably go with a solution that involves using the Excel
import code to read each file into a blank tab, and then copy that
data from the tab into the combined tab. Here's an example of a macro
that imports data into a new tab:

ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Tempsheet"

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
+ FileName, Destination:=Range("A1"))
.Name = "CSVfile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2)
.Refresh BackgroundQuery:=False
End With

The big 2, 2, 2, 2, 2 line defines all columns (up to 100) as Text.
Next you would need to copy-paste the contents of this tab into the
real tab and fill in the file name, then delete the tab and import the
next file. You could turn off screen updates while this is happening.
Or hide the temporary tab.

Phil Hibbs.

mporting multiple text files into Excel with corresponding fil
 

Phil Hibbs wrote:
I would probably go with a solution that involves using the Excel
import code to read each file into a blank tab, and then copy that
data from the tab into the combined tab. Here's an example of a macro
that imports data into a new tab:



Phil Hibbs.

Other way is to use TextToColumns method. Using Jacob's code, it looks
like this. but, No test.

Sub Macro()

Dim strFolder As String, strFile As String, lngRow As Long
Dim strData As String, arrData As Variant, intFile As Integer

strFolder = "D:\PhoneNo"
If Right(strFolder, 1) < "\" Then strFolder = strFolder & "\"

strFile = Dir(strFolder & "*.txt", vbNormal)
Do While strFile < ""
intFile = FreeFile
Open strFolder & "\" & strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
lngRow = lngRow + 1
Range("A" & lngRow) = strFile
Range("B" & lngRow) = strData
Loop
Close #intFile
strFile = Dir
Loop
Columns("B").TextToColumns Destination:=Columns("B"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End Sub

Keiji

mporting multiple text files into Excel with corresponding fil
 

Hi Phil

Thanks for your thoughts. We are not sure since the OP has not come back. A
better way would be to use the .OpenText method as below...

Workbooks.OpenText Filename:="D:\PhoneNo\2.txt", StartRow:=1 , _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True


--
Jacob


"Phil Hibbs" wrote:

I would probably go with a solution that involves using the Excel
import code to read each file into a blank tab, and then copy that
data from the tab into the combined tab. Here's an example of a macro
that imports data into a new tab:

ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Tempsheet"

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
+ FileName, Destination:=Range("A1"))
.Name = "CSVfile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2)
.Refresh BackgroundQuery:=False
End With

The big 2, 2, 2, 2, 2 line defines all columns (up to 100) as Text.
Next you would need to copy-paste the contents of this tab into the
real tab and fill in the file name, then delete the tab and import the
next file. You could turn off screen updates while this is happening.
Or hide the temporary tab.

Phil Hibbs.
.