Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... :-) . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing multiple text files into Excel with corresponding filenames | Excel Programming | |||
Joel - Importing multiple text files to 1 spreadsheet, now importing from excel files | Excel Programming | |||
multiple text files to multiple rows in excel | Excel Programming | |||
excel files in my docs have numbered filenames and are duplicates | Setting up and Configuration of Excel | |||
Acquiring filenames for multiple files with GetOpenFilename | Excel Programming |