Converting DAT Files to Access

[pbt1234]

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

 

[Remou]

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

 

[LNBruno]

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 >
[small]Holidays cause stress. Crazy people crack under stress. Consider yourself warned.[/small]

 

[Olaf Doschke]

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.

 

[pbt1234]

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

 

[harebrain]

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.

 

[pbt1234]

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

 

[LNBruno]

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 >
[small]Don't believe everything you think.[/small]

 

[LNBruno]

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

< M!ke >
[small]Don't believe everything you think.[/small]

 

[harebrain]

>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.

 

[LNBruno]

good catch, hair!


< M!ke >
[small]Don't believe everything you think.[/small]

 

[ceh4702]

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.

 

[pbt1234]

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