importing .txt files

dpmicka

Board Regular
Joined
Jun 13, 2002
Messages
122
I have a whole lot (hundreds) of .txt files that contain fixed width data that I need to convert to Excel format. I can open each file and step through the Text Import Wizard, then save it as an .xls file, but I'm wondering if there's a way to convert all the files in a given folder from .txt to .xls without having to open each one while (here's the kicker) still placing the data between the spaces on the .txt file into individual cells and columns like the wizard does. Any ideas?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

dln

New Member
Joined
Sep 20, 2002
Messages
14
dp,
Are you able to program macros?

I had a similar situation (each .txt file of similar/same CSV format, but not en-masse - mine come in on a more or less daily basis). I first recorded a macro to import one file. Unfortunately such cannot be used to import another because Macro Recorder hard codes the original filename (rats!), so I edited that to a variable and slapped in a front-end call to the standard File | Open dialog box... (ripping apart my code, it goes something like this)

strFileNm = Application.GetOpenFilename("Filename (*.txt), *.txt", , "Import")
If strFileNm = "False" Then
MsgBox "File selection problem. Execution ending"
Exit Sub
End If
With ActiveSheet.QueryTables.Add(Connection:= "TEXT;" & strName, Destination:=ActiveCell)
.Name = "LABOR1"
.etc, etc (from the M-Recorder)

Now if your hope is not to sit there entering hundreds of filenames one after the other, but to have the system run through each *.txt file in a designated folder in turn, then you could front-end this code with a call to identify only the directory and use the dir and file functions to control the looping and designate the next file for attention/import/conversion.

If you need more help, contact me privately.
Regards,
=dn
 

clarkr

New Member
Joined
Dec 15, 2004
Messages
1
I have a similar situation to the one described above, except I want to convert a lot (hundreds) of Excel spreadsheets in a directory to fixed format text files. I recorded a macro to do this but want to automate the process to do the same to all the Excel spreadsheets.

Based on DP's comments, it sounds like the call to identify the directory and using the dir and file functions to control the looping would work. However, I am not sure how to do this.

I would also like to have two other things happen. Is there a way to initiate the process automatically, including starting up Excel and triggering the file conversion process?

Finally, I would like to transfer the converted text files to a mainframe, e.g., using ftp, and have this process run automatically as well.

Any suggestions?
 

crunchtime9999

Board Regular
Joined
Feb 26, 2007
Messages
214
Hello,
I read this thread and this is exactly what I am looking for. I have over 100 txt files that I need opened (delimit = |) and then save as xls and close that file and move to the next file and do the same thing. I am not proficient at VBA so I don't understand how to make the front end have the user be able to select a directory and then cycle through the files.

Thanks
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
To transform from .txt to xls try this:
Code:
Public Sub test()
Dim myDir As String, myFile As String, FileNum As Long, x As Long
myDir = GetFolder() 'here you set the directory that text files are placed
myFile = Dir(myDir & "*.txt")

Do While myFile <> ""
    x = 1
    FileNum = FreeFile
    Open myFile For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, InputData
            RawData = Split(InputData, "|", -1) '"|" is the delimiter
            On Error Resume Next
            For r = 0 To 10 'here i assume that i need 10 columns of raw data to be "imported"
            Sheet1.Cells(x, r + 1) = Trim(RawData(r))
            Next
        x = x + 1
    Loop
    Close #FileNum
    'begin to save sheet as independent xl file, at same dir as txt files.
    'to save to another lockation then at line [ActiveWorkbook.SaveAs] replace
    '"c:\" to what ever you want
    Sheet1.Copy
    xlfile = Split(myFile, ".")
    ActiveWorkbook.SaveAs mydir & xlfile(0) & ".xls"
    ActiveWorkbook.Close
    myFile = Dir
Loop
End Sub

Function GetFolder() As String
'This function is taken by RichardSchollar
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function
 

crunchtime9999

Board Regular
Joined
Feb 26, 2007
Messages
214

ADVERTISEMENT

Good Day and thanks for your help. I tried the code this morning and got an error on this line saying it was not defined.

Dim fldr As FileDialog

What do I need to do to fix this?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
What version of Excel are you using? If I remember correctly, the FileDialog was only incorporated into Excel from the 2002 version (I could be wrong on this ie it could also be present in 2000, but it definitely isn't in 97). If you know which folder you want to run this against, then you can do away with the folder picker code altogether. The following shows Kostas' code modified to include a fixed directory (you can amend this each time you want to run it):

Code:
Public Sub test() 
Dim myDir As String, myFile As String, FileNum As Long, x As Long 

'*********The code has been amended on the following line******

myDir = "C:\MyFolder\"    'amedn this as required

'*********Code above has been amended from Kostas' original****


myFile = Dir(myDir & "*.txt") 

Do While myFile <> "" 
    x = 1 
    FileNum = FreeFile 
    Open myFile For Input As #FileNum 
    Do While Not EOF(FileNum) 
        Line Input #FileNum, InputData 
            RawData = Split(InputData, "|", -1) '"|" is the delimiter 
            On Error Resume Next 
            For r = 0 To 10 'here i assume that i need 10 columns of raw data to be "imported" 
            Sheet1.Cells(x, r + 1) = Trim(RawData(r)) 
            Next 
        x = x + 1 
    Loop 
    Close #FileNum 
    'begin to save sheet as independent xl file, at same dir as txt files. 
    'to save to another lockation then at line [ActiveWorkbook.SaveAs] replace 
    '"c:\" to what ever you want 
    Sheet1.Copy 
    xlfile = Split(myFile, ".") 
    ActiveWorkbook.SaveAs mydir & xlfile(0) & ".xls" 
    ActiveWorkbook.Close 
    myFile = Dir 
Loop 
End Sub
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Give it a try like this (remove function):
Code:
Public Sub test()
Dim myDir As String, myFile As String, FileNum As Long, x As Long, f As Long
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = 0 Then Exit Sub
    myDir = .SelectedItems(1)
End With
'myDir = GetFolder() 'here you set the directory that text files are placed
myFile = Dir(myDir & "*.txt")

Do While myFile <> ""
    x = 1
    FileNum = FreeFile
    Open myFile For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, InputData
            RawData = Split(InputData, "|", -1) '"|" is the delimiter
            On Error Resume Next
            For r = 0 To 10 'here i assume that i need 10 columns of raw data to be "imported"
            Sheet1.Cells(x, r + 1) = Trim(RawData(r))
            Next
        x = x + 1
    Loop
    Close #FileNum
    'begin to save sheet as independent xl file, at same dir as txt files.
    'to save to another lockation then at line [ActiveWorkbook.SaveAs] replace
    '"c:\" to what ever you want
    Sheet1.Copy
    xlfile = Split(myFile, ".")
    ActiveWorkbook.SaveAs myDir & xlfile(0) & ".xls"
    ActiveWorkbook.Close
    myFile = Dir
Loop
End Sub
 

crunchtime9999

Board Regular
Joined
Feb 26, 2007
Messages
214
Hello,
I am using Excel 2003. I tried to run your new code and it allows me to select the directory with my txt files....but it does not show any of the txt files in the dialog box. If I choose ok it just exits the program.

I created a test txt file that is pipe delimited and saved in in a folder on my C drive. I then copied the text file and pasted 10 copies into the folder.

Thanks for your help. Once this works it will make this part of my job so much easier.

Thanks again.
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top