Macro to open *.dat files and save as .txt (comma delimited text files)

[YehWei]

Hi,

Would appreciate if someone would enlighten me on how I can accomplish
the following:

1. Open each of the dat files in a directory in excel. These dat files
are semicolon delimited files.

2. Apply a set of actions to the opened files (which I've already
saved), then save the files as the original name except for a different
extension. i.e. .txt (comma delimited files).

3. How do I convert *001 in a field to display 001 instead of 1? Seems
like Excel will autoconvert the format to number instead of text.

4. How do I remove the trailing spaces after a name without impacting
the spaces within a name? For e.g. "Harry Lee " becomes "Harry Lee".

Thanks in advance for any help rendered.

YW

 

[Leith Ross]

Hello YehWei,

Here is the macro to copy and convert the DAT file into TXT file (CSV).
Add a VBA module to your project and place this code in it. You can then
run it by selecting "*CsvToTxt*" from the Macro List (press ALT and F8
to display the list).

______________________________

Sub CsvToTxt()

Dim Data As String
Dim Ext As String
Dim FF1 As Integer
Dim FF2 As Integer
Dim FileFilters As String
Dim File_Name
Dim FileName1 As String
Dim FileName2 As String
Dim I As Long
Dim Msg As String

'Display the Open File dialog
FileFilters = "Data Files (*.dat),*.dat,Text Files (*.txt),*.txt,All
Files (*.*),*.*"
File_Name = Application.GetOpenFilename(FileFilters)

'Get the file's extension
I = InStr(1, File_Name, ".")
If I > 0 Then
Ext = Right(File_Name, Len(File_Name) - I + 1)
End If

'Check extension is .CSV
If Ext <> ".csv" Then
MsgBox "Error - Source file must have .csv extension",
vbCritical + vbOKOnly
Exit Sub
End If

'Set source and destination file names
FileName1 = File_Name
FileName2 = Left(File_Name, I) & "txt"

On Error GoTo FileError

'Replace semicolons with commas
FF1 = FreeFile
Open FileName1 For Input As #FF1
FF2 = FreeFile
Open FileName2 For Output As #FF2
Do While Not EOF(FF1)
Line Input #FF1, Data
Data = Replace(Data, ";", ",")
Write #FF2, Data
Loop
Close #FF2
Close #FF1

Exit Sub

FileError:
Msg = "The following error has occurred" & vbCrLf _
& " Error #" & Err.Number & vbCrLf _
& " " & Err.Description
MsgBox Msg, vbCritical + vbOKOnly

End Sub
______________________________

To prevent Excel from converting the numbers, format the worksheets as
TEXT.

To remove the both leading and trailing spaces from a string, use
TRIM(" Harry Lee "). The result is "Harry Lee".

Sincerely,
Leith Ross

 

[YehWei]

Thanks Leith!

One minor problem regarding (3). I tried to formating the worksheet as
TEXT, but because the numbers have a unique form of "*011" with the
asterisk infront, performing a text to column action on the selected
column will automatically transform the *011 to 11 only.

Would appreciate any help on this, very near to the completion of the
macro. Thanks again!