Convert .xls file to .dat file

[AndrewKent]

Hi there,

I am looking at the possibility of writing some coding to convert an excel spreadhseet of data into a ".dat" file. I've never done this before but the concept looks possible.

The spreadsheet itself is a list of names and addresses (just standard information) with no formulas or formatting.

How would I go about "exporting" this information into ".dat" format and writing a macro that will do this for me.

Many thanks,

Andy

 

[SydneyGeek]

From this thread on another forum, .dat is a text file with a pipe delimiter.
You can try just saving as delimited with a .dat extension, if that's no good, change your default list delimiter. One of the posts in the thread shows how.

Denis

 

[Andrew Poulsom]

What is .dat format? You can save as a text file (csv [comma delimited], txt [tab delimitted] or prn [space delimited]) and change the extension.

 

[Norie]

Andy

There really isn't such a thing as 'dat' format, a 'dat' file is just a text file, it could theoretically have any extension you want.

It could also be delimited however you want/need, it all really depends on what you are trying to achieve.

ie what are you going to use this file for?

If it's for use with another application then the requirements of that application will probably dictate how it's delimited/structured etc.

 

[BrianO]

hey all,

I work with andy and I thought I'd share my solution to this one

Sub TestDatFile()
 
    '   The delimiter can be set to any value depending on requirements
    Const DELIMITER As String = "|"
 
    Dim myRecord As Range
    Dim myField As Range
    Dim sOut As String
 
     '   this is the path to where you want the output file to go
    Open "[URL="file://filepath/UploadPipe.dat"]\\filepath\UploadPipe.dat[/URL]" For Output As #1
 
    For Each myRecord In Worksheets("Dat Test").Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Worksheets("Dat Test").Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
 
                '   The delimiter can be set either before or after the cell value
                sOut = sOut & myField.Text & DELIMITER
 
            Next myField
            Print #1, Mid(sOut, 1)
            sOut = Empty
        End With
    Next myRecord
    Close #1
    MsgBox "UploadPipe.dat has been created successfully", vbOKOnly
 
End Sub

This seems to work out better for us than changing the default list delimiter.

Thanks for pointing us in the right direction and if you see any faults in the code just let me know

Brian

 

[AndrewKent]

Hi,

Again, thanks for your help on this one guys (and thanks for figuring this one out Brian!)

Andy

 

[Ekkehard]

hey all,

I work with andy and I thought I'd share my solution to this one

Sub TestDatFile()
 
    '   The delimiter can be set to any value depending on requirements
    Const DELIMITER As String = "|"
 
    Dim myRecord As Range
    Dim myField As Range
    Dim sOut As String
 
     '   this is the path to where you want the output file to go
    Open "[URL="file://filepath/UploadPipe.dat"]\\filepath\UploadPipe.dat[/URL]" For Output As #1
 
    For Each myRecord In Worksheets("Dat Test").Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Worksheets("Dat Test").Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
 
                '   The delimiter can be set either before or after the cell value
                sOut = sOut & myField.Text & DELIMITER
 
            Next myField
            Print #1, Mid(sOut, 1)
            sOut = Empty
        End With
    Next myRecord
    Close #1
    MsgBox "UploadPipe.dat has been created successfully", vbOKOnly
 
End Sub

This seems to work out better for us than changing the default list delimiter.

Thanks for pointing us in the right direction and if you see any faults in the code just let me know

Brian

Excellent help - solved a technical task that I faced in work. Thank you very much.

 

[MaheshG1995]

This Code Helped me but when i convert non english data using that it create some junk character like ???? can anyone help with modified code?