Convert .xls file to .dat file

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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.:)
 
Last edited:
Upvote 0
hey all,

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

Code:
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
 
Last edited:
Upvote 0
Hi,

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

Andy
 
Upvote 0
hey all,

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

Code:
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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
Back
Top