Importing many text files into one excel worksheet - how to?

S

scott

Hi all,
I have many identical format comma delimited text files in a folder,
named as follows: B1010607.txt. The user needs to be prompted which
files to import, and then the macro should import them one after
another into the same worksheet.

I'm struggling with it. I have a macro to import all text files in a
folder, but the problem is that the first 2 digits are either B1 or B2
- and must be distinct in the worksheet. Maybe if the B1 files went
to one worksheet, the B2 ones went to another? Another idea - maybe
if upon importing, the B1 or B2 could somehow be obtained and pasted
in as the contents of the first column?

Each sheet contains many rows of identical format data.

Any ideas anyone? I'm REALLY struggling but it's really really
important.

Thanks in advance for any responses.

Scott.
 
S

scott

Hi Ron,
The indiv. files should be below one another. The example you posted
seems like it would do the trick if it pastes them one below the
other.

Thanks for replying so fast,
Scott.
 
S

scott

Oh, and I forgot to mention (not that it's a big thing). Rather than
a new workbook it should be a "pre-defined" sheet on a predefined
workbook. That way I can have other sheets in the same workbook with
pivot tables etc to extract whatever data I require.

Thanks again,
Scott.
 
R

Ron de Bruin

Hi Scott

Test this one
Copy all the code in a normal module of your workbook
It will copy the data in the activesheet

For all files in a folder check out also this page
http://www.rondebruin.nl/csv.htm


Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Public Function ChDirNet(szPath As String) As Boolean
'based on Rob Bovey's code
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
ChDirNet = CBool(lReturn <> 0)
End Function

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub Get_TXT_Files()
'For Excel 2000 and higher
Dim Fnum As Long
Dim TxtFileNames As Variant
Dim QTable As QueryTable
Dim SaveDriveDir As String
Dim ExistFolder As Boolean
Dim I As Long

'Save the current dir
SaveDriveDir = CurDir

'You can change the start folder if you want for
'GetOpenFilename,you can use a network or local folder.
'For example ChDirNet("C:\Users\Ron\test")
'It now use Excel's Default File Path

ExistFolder = ChDirNet(Application.DefaultFilePath)
If ExistFolder = False Then
MsgBox "Error changing folder"
Exit Sub
End If

TxtFileNames = Application.GetOpenFilename _
(filefilter:="TXT Files (*.txt), *.txt", MultiSelect:=True)

If IsArray(TxtFileNames) Then

On Error GoTo CleanUp

With Application
.ScreenUpdating = False
.EnableEvents = False
End With


'Loop through the array with txt files
For Fnum = LBound(TxtFileNames) To UBound(TxtFileNames)

I = LastRow(ActiveSheet)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & TxtFileNames(Fnum), Destination:=Cells(I + 1, 1))
.TextFilePlatform = xlWindows
.TextFileStartRow = 1

'This example use xlDelimited
'See a example for xlFixedWidth below the macro
.TextFileParseType = xlDelimited

'Set your Delimiter to true
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False

'Set the format for each column if you want (Default = General)
'For example Array(1, 9, 1) to skip the second column
.TextFileColumnDataTypes = Array(1, 9, 1)

'xlGeneralFormat General 1
'xlTextFormat Text 2
'xlMDYFormat Month-Day-Year 3
'xlDMYFormat Day-Month-Year 4
'xlYMDFormat Year-Month-Day 5
'xlMYDFormat Month-Year-Day 6
'xlDYMFormat Day-Year-Month 7
'xlYDMFormat Year-Day-Month 8
'xlSkipColumn Skip 9

' Get the data from the txt file
.Refresh BackgroundQuery:=False

End With

Next Fnum

CleanUp:
For Each QTable In ActiveSheet.QueryTables
QTable.Delete
Next

ChDirNet SaveDriveDir

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End Sub
 
S

scott

Hi Ron,
This works excellent, thankyou.

One thing I didn't realise before though...The date isn't actually in
the text file anywhere (which is useless). Is there a way we can
extract the date from the name of the text file (i.e. B1020607.txt)
and insert this into column 1 in the relevant places?

Sorry if this is too much work - I'm pulling my hair out.

Thanks in advance,
Scott.
(Note: I'll post this as a seperate in the same group as it kind of
goes off on a tangent from this point).
 
R

Ron de Bruin

Hi Scott

B1020607.txt

Are there always two characters (B1 in this file name) before the date
 
S

scott

Hi Ron,
Yes, there are always the 2 characters. They vary from either B1 or
B2, but always follow the same format.

Thanks in advance,
Scott.
 
R

Ron de Bruin

Tru this sub then

Sub Get_TXT_Files_Test()
'For Excel 2000 and higher
Dim Fnum As Long
Dim TxtFileNames As Variant
Dim QTable As QueryTable
Dim SaveDriveDir As String
Dim ExistFolder As Boolean
Dim I As Long

'Save the current dir
SaveDriveDir = CurDir

'You can change the start folder if you want for
'GetOpenFilename,you can use a network or local folder.
'For example ChDirNet("C:\Users\Ron\test")
'It now use Excel's Default File Path

ExistFolder = ChDirNet(Application.DefaultFilePath)
If ExistFolder = False Then
MsgBox "Error changing folder"
Exit Sub
End If

TxtFileNames = Application.GetOpenFilename _
(filefilter:="TXT Files (*.txt), *.txt", MultiSelect:=True)

If IsArray(TxtFileNames) Then

On Error GoTo CleanUp

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Loop through the array with txt files
For Fnum = LBound(TxtFileNames) To UBound(TxtFileNames)

I = LastRow(ActiveSheet)


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & TxtFileNames(Fnum), Destination:=Cells(I + 1, 2))
.TextFilePlatform = xlWindows
.TextFileStartRow = 1

'This example use xlDelimited
'See a example for xlFixedWidth below the macro
.TextFileParseType = xlDelimited

'Set your Delimiter to true
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False

'Set the format for each column if you want (Default = General)
'For example Array(1, 9, 1) to skip the second column
.TextFileColumnDataTypes = Array(1, 9, 1)

'xlGeneralFormat General 1
'xlTextFormat Text 2
'xlMDYFormat Month-Day-Year 3
'xlDMYFormat Day-Month-Year 4
'xlYMDFormat Year-Month-Day 5
'xlMYDFormat Month-Year-Day 6
'xlDYMFormat Day-Year-Month 7
'xlYDMFormat Year-Day-Month 8
'xlSkipColumn Skip 9

' Get the data from the txt file
.Refresh BackgroundQuery:=False

End With
Cells(I + 1, 1).Resize(LastRow(ActiveSheet) - I, 1).Value = _
Mid(TxtFileNames(Fnum), InStrRev(TxtFileNames(Fnum), "\", , 1) + 3, 6)

Next Fnum

CleanUp:
For Each QTable In ActiveSheet.QueryTables
QTable.Delete
Next

ChDirNet SaveDriveDir

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End Sub
 
S

scott

Hi Ron,
No errors - but nothing gets imported, either (it goes through the
file import process ok, and then pauses - as if it's importing - but
then just a blank sheet). If I go back to the original code posted by
yourself (before the mod for the date column) it works ok, however.

Thanks for bearing with me.

Scott.
 
R

Ron de Bruin

Strange

Can you send me one of the txt files private so I can test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi Ron,
No errors - but nothing gets imported, either (it goes through the
file import process ok, and then pauses - as if it's importing - but
then just a blank sheet). If I go back to the original code posted by
yourself (before the mod for the date column) it works ok, however.

Thanks for bearing with me.

Scott.
 
S

scott

Ah, yes it DOES work! The problem was that I had headings already in
for the columns (which I need, incidentally). Other than that, all is
good - but I now have a problem with it stripping the leading zero
from the date...

But you're doing great stuff here. Thanks for this!

Scott.

(oh, and if someone sees a duplicate comment by me on another post,
it's because I just replied to this post in the wrong window. D'oh!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine 2 columns in a dynamic range into one 7
Importing Text Files Into Access 1
Importing text files into same workbook. 2
Importing Multiple Text Files 7
Combine Text Files into One Worksheet 1
Importing text files into a "cached" worksheet? 4
Importing Word text into Excel 12
Import data from multiple txt files into one xls file 3

Top