×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting DAT Files to Access

Converting DAT Files to Access

Converting DAT Files to Access

(OP)
I have been given an OLD database running in DOS (looks like Clarion - how would I know?).  I need to convert this to Access.  Any ideas on how to do this (programs, process, etc.)?  I really only need the data moved for purposes of creating new reports, etc.

Any help would be appreciated!
pbt1234

RE: Converting DAT Files to Access

You do not provide much information. It is possible that the old, unnamed, database allows for files to be exported to a suitable format.

RE: Converting DAT Files to Access

I've run into this a few times.  You might be able to import directly into Access.  In one case, I had to create a parsing routine to split out the data from the DATs and write to already created tables.

< M!ke >
Holidays cause stress.  Crazy people crack under stress.  Consider yourself warned.

RE: Converting DAT Files to Access

AFAIK DAT is no specific format, it could be several things, not necessarily a Clarion database.

http://www.fileinfo.net/extension/dat
gives 3 possible explainations.

I also found Clarion, if it's a clarion file you might be able to view with this viewer: http://www.batchconverter.com/Clarionviewer-download-21609.shtml

For all file formats the specification surely is not the file extension only, but a file format always has it's footprint by some predefined composition of headers, chunks, whatever. Open the file with a hex editor and see if there is anything in it revealing what it might be.

Bye, Olaf.

RE: Converting DAT Files to Access

(OP)
OK, I tried the viewer that was suggested by OlafDoshke and was able to open the files.  Since the application is a shareware item, I was able to only view/export 100 files.  I found another program CCVT.exe that allowed me to convert the Clarion *.dat files to dBase III files, which could be viewed in Excel or imported into Access.  I have hit two hurdles with this . . .

1) the field names came over a bit scrambled (i.e. in the conversion, the field names were changed to first three letters of the table name and then some characters from the field name; not all, some) - I corrected this by viewing the *.dat files field names then converting the dBase III files to Excel and editing the field names there.  I could then import into Access with no field name issues.  What this highlighted is that some fields which were in the *.dat files apparently were blank and therefore were not transferred to the dBase III files.  So I added columns in the Excel for these fields, but really feel unsure about whether they should be there at all.  Any suggestions?

2) One of the pieces of data is a float in the dBase III files.  When I bring this field into Excel, it looks fine, but when I import to Access from Excel, it looks like this - 1.5010960E+11.  I tried importing from dBase III, but got the same results.  I believe this to be an error due to the size of the field or type of data.  Any suggestions?

Thanks again to everyone, this has got me started, which was the first big hurdle.  Also, if anyone has any more insight into the Clarion DB structure (important files, easy ways to determine linked fields from table to table, etc.) that would be appreciated too!

Regards,
pbt1234

RE: Converting DAT Files to Access

Quote:

When I bring this field into Excel, it looks fine, but when I import to Access from Excel, it looks like this - 1.5010960E+11.
The field might or might not be numeric, but it looks like a number to Excel.  When you import to Excel, you're choosing (by default) the "general" data conversion, which Excel sees as license to convert numerics to scientific notation.  Solution:  choose a different conversion before you light-off the import.

RE: Converting DAT Files to Access

(OP)
harebrain -

Thanks for the help!  Could it be that Access is doing the same thing?  I have tried to do as described and also to import directly to access from the dBase III file and get the same problem.  From Excel, I have tried formatting the field as a number, text and general.  Neither of these netted me a good import.  

I finally added an apostrophe at the front of each number (even the 0's) in the Excel file and got a good import.  Now just need to get rid of the leading apostrophes - anyone with easy way to do this?

Field now looks like '150103900012 and should be just 150103900012

Thanks again to all!
pbt1234

RE: Converting DAT Files to Access

You can format Excel's cells (select the entire worksheet, a specific column, etc.) as text and that will eliminate the numeric notation AND the need to put a single quote in front of all the values.

Yeah, I know.  Why didn't I tell you that BEFORE you added all the quotes....

< M!ke >
Don't believe everything you think.

RE: Converting DAT Files to Access

Oh - and as you do the import in Access, you'll need to specify the data type for each column....

< M!ke >
Don't believe everything you think.

RE: Converting DAT Files to Access

>Oh - and as you do the import in Access, you'll need to specify the data type for each column....

You'll need to do the same in Excel:  supply the correct datatype when you import the data.  If the spreadsheet is already open and you try to change from "general" format to something else, your data is already corrupt.  This is the MS equivalent of closing the barn door after the horse is gone.

RE: Converting DAT Files to Access

good catch, hair!

< M!ke >
Don't believe everything you think.

RE: Converting DAT Files to Access

It may be easier to go directly to Access.  Excel has a nasty habit of not handling null entries very well.  I think Access may handle this better.  Often fields with no data are not handled very well or completely bypassed, and then you get data in the wrong field/column.  There may be controls to default blank data to a specific value by default.

If you do not like my post feel free to point out your opinion or my errors.

RE: Converting DAT Files to Access

(OP)
Sorry that I have not posted a reply to these helpful words of wisdom sooner, but have been traveling way too much these days!

I want to thank all of you for your help!  I have finally got all the data moved over to Access and am now working on re-creating the reports.  I am sure there will be more posts related to those trials and tribulations as I begin that journey starting today!

Thanks again to all that posted and suggested, without your help, I would never have gotten to here!

Regards,
pbt1234

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close