Import from a .DAT file

[Louis Nickerson]

I have a .dat file (approximately 550 MB).

I can open it in Excel, but because the file contains more than 65535 records, Excel truncates the data...not good.

It is too large for notepad to open.

It will eventually open in Wordpad, but it hangs the PC.

I need to get this data Imported into an Alpha 5 table.

This is disk 1 of 10 and ultimately, I need to import all 10 CDs.

Any help or advice would be most appreciated.

Louis

[Stan Mathews]

Re: Import from a .DAT file

Not sure I understand the problem. A .dat file is basically a text file?

If the .dat has an easily discernible structure it should import as if it were a .txt. (uniform throughout the file) If not you will probably need to read it with the file methods and find the markers (words, phrases) that delimit the usable data.

[Louis Nickerson]

Re: Import from a .DAT file

Stan,

Thank you for replying.

I know the exact structure of each record. A specific number of character spaces is used for each field. If the data in a field does not fill the entire field width then it is paded on the right with the appropriate number of spaces.

I tried to import the file into a table, but Alpha does not recognise the .dat file extension as a type of file that can be imported. I also tried renaming the extension from .dat to .txt but the import Genie didn't see any text to import.

I am attaching two files to this thread. One is a small sampling of the data it is named 010207c.txt (I changed the extension from .dat to .txt so the message board would allow the upload) and the other is a readmetxt file that details what each field is and it's length.

Any suggestions are welcome and appreciated.

Louis

[Doug Page]

Re: Import from a .DAT file

I think from a quick look at the file that your biggest problem is that the record length is 1170 characters. I think that typical text file manipulation routines are limited to 1024 characters. The records also contain characters of 00 (zero-zero) which can also cause problems with text manipulation routines.

From your field description file I can see that this is a COBOL file. Having dealt with MicroFocus Cobol files in the past, they could have deleted records in them if the files have not been packed. MF Cobol files are similar to dbf's in that they mark records for deletion. The way they do this is to change the EOL characters from the traditional CR-LF to 00-00. Again, this will cause problems with text file routines as they now won't know where the end of the record is.

In order to deal with this, the best method is to have the client use a report writer to "dump" the data into a more friendly text file. Otherwise, start brushing up on your XBasic and learning the other file options Alpha has to deal with non-text files. You will then have to verify and manipulate the data and save to a table. A lot more work but can be done.

Edit: Also make sure that the client has packed the files. It just makes things so much easier to code on your end.

[Louis Nickerson]

Re: Import from a .DAT file

Doug,

Thank you for your input.

The data was provided by the State of FLorida. As best I can determine, this is the only format they will provide the data in.

I'm not too worried about undeleted records. This database will ultimately be used to populate marketing data to yet another database. If there is a record in this file that is not found in the marketing database, then there will just be no action taken for that record.

Since I can determine exactly where one record ends and the next begins by counting the number of characters, I was thinking that a consistent record delimiter might not be necessary. I welcome your thoughts on this idea.

Thanks again

Louis

[MikeC]

Re: Import from a .DAT file

Louis,
I had the same result as you with the renamed dat to txt file you provided--nothing showed up in the genie for import...but then opened it up in notepad, closed it and tried again and everything showed up in the genie and imported without a problem.

imported using ascii and no delimiter--set my own breaks prior to import

[Raymond Lyons]

Re: Import from a .DAT file

Mike beat me to it, but if you throw in more than one record I think you might also have to set it to remove leading blanks. Not sure though cuz my test 2nd record was phony, which might have caused the leading blanks I experienced. Plus I think Mike is talking about using the "Table format - the data in your file is in fixed width columns."

Ray

[MikeC]

Re: Import from a .DAT file

Ray,

I think Mike is talking about using the "Table format - the data in your file is in fixed width columns.

Yes--it just seemed to be the way it was organized and was the first thing I tried...I hope this works for Louis as getting one small sample to work may not be the same as getting the original dat file working.

[Doug Page]

Re: Import from a .DAT file

Mike and Ray,
You didn't just open the file with Notepad but you also SAVED it as well. When you did, it turned all the 00's to 20's (or spaces). This made it a text file that could now be read by Alpha.

Louis,
The good news is that it can read past the 1024 characters and read the whole record of 1170 characters. So now the problem just seems to be getting the 00's out of the file and changed to 20's. Your file is 550MB so opening in a text editor is not going to work. I would look into the file.open() and file.read() functions and then manipulate the data from there. For simplicities sake you could just change all the 00's to 20's and then file.write() back to a new filename. Then you should be able to import as normal.

[MikeC]

Re: Import from a .DAT file

Doug,
Right---another assumption on my part I guess--made sense to me while doing it and assumed it would be "matter of course" to do it that way! Darned assumptions can really add to confusion sometimes and am glad there are people like you to straighten it out for people like me who do this!! :)

It sure seems to me that I have opened up a file that was over 200 mb once--took a while but seem to remember it opening eventually--largest so far I can find of a file is only just past 50 mb and opens in about 5 seconds....this is with notepad used on winXP sp2. Will keep looking as I cannot find a definitive answer googling for the file limit on notepad--most allude to the old limit of 64 KB.


LATER:just opened a 450 mb file (a cab renamed to txt) in 3 1/2 minutes. So am guessing notepad will open even higher--Are you (everyone) certain that the file cannot be opened by notepad????

[Raymond Lyons]

Re: Import from a .DAT file

I stopped using Notepad years ago so know nothing about its file size limits.

I use NoteTab Pro which will open files up to 2GB. The Pro version is $29. Don't know the limits on the free version but it could be the same.

Ray

[Stan Mathews]

Re: Import from a .DAT file

I believe if you change the file association for .dat files to open with notepad they will appear in the Alpha ascii import dialog.

[MikeC]

Re: Import from a .DAT file

Stan,
I didn't even have to go that far--just by changing it and opening and closing it once in notepad allowed the file to be seen...prior to opening it the genie was blank. I tried this with an actual .dat file I found and it worked the same way--changed extension to .txt and did not show up in genie. Then opened and closed in notepad (without having the file association changed) and then it would show up.

Either way I think Louis has a way now which is good.

[Stan Mathews]

Re: Import from a .DAT file

prior to opening it the genie was blank. Either way I think Louis has a way now which is good.

True.
Additional note: Even if you can't see the file in the genie it doesn't mean you can't navigate to the directory where it resides and type in the filename.extension.

The developer is of course responsible for ensuring that such a procedure provides Alpha with a usable file.

[DaveM]

Re: Import from a .DAT file

Using you test file I got the following in a few minutes with word.

record1
P07000000010STEVEN,GROSSBARD,P.A.,ADOMP,44,WEST,FLAGLER,STREET,2100,MIAMI,FL33130,US01012007,N,FL,GROSSBARD,STEVEN,ESQ.,P44,WEST,FLAGLER,STREET,MIAMI,FL33130,P,PGROSSBARD,STEVEN,44,WEST,FLAGLER,STREET,#2100,MIAMI,FL33130,
record 2
P07000000020WALLY,ALLEN,INC,ADOMP,3010A,CRAWFORDVILLE,HWY.,CRAWFORDVILLE,FL32327,US12282006,N,FL,ALLEN,WALLACE,LJR,P341,REHWINKLE,RD,CRAWFORDVILLE,FL32327,P,PALLEN,WALLACE,LJR,341,REHWINKLE,RD,CRAWFORDVILLE,FL32327,VP,PALLEN,ANETTE,D,341,REHWINKLE,RD,CRAWFORDVILLE,FL32327,
record 3
L07000000010RIO,INVESTMENTS,LLC,AFLAL,5620,PINEY,LANE,DRIVE,TAMPA,FL33625,01022007,FL,SPIEGEL,&,UTRERA,P.A.,C1840,SW,22ND,ST.,MIAMI,FL33145,
P07000000030BACK,TO,HEALTH,OF,THE,PALM,BEACHES,INC.,ADOMP,3520,S,OCEAN,BLVD,F103,SOUTH,PALM,BEACH,FL33480,01012007,N,FL,VON,GUSTEDT,ANDREW,JESQ,P505,S,FLAGLER,DRIVE,STE,300,WEST,PALM,BEACH,FL33401,D,PFLAGELLO,JOSEPH,M,JR,3520,S,OCEAN,BLVD,F103,SOUTH,PALM,BEACH,FL33480,

this is broken with a linefeed and carriage return. i simply converted the spaces(2 or more) to commas and then eliminated the commas to 1 comma. It was fast and rude and could be better done. Just wanted to see what a record would look like from the file.

I have no conclusion except ease is not going to be the word of the day. It can be done though.