importing .txt files

[dpmicka]

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?

 

[dln]

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]

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]

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]

Clarkr try this link to check ways of how to export to text files. A simple loop should do the job for each sheet in yourworkbook.
http://www.mcgimpsey.com/excel/textfiles.html#fixedfield

Edit: Didn't notice the 2004 post date...

 

[ktab]

To transform from .txt to xls try this:

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]

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]

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):

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]

Give it a try like this (remove function):

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]

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.