Import .DAT File

**Ian Brown** · Sep 21 '07, 05:29 PM

You can use the Left$ Right$ and Mid$ to 'cut' the line of text into the data you whant. for example:

Code:

Text = "FN9099070829070828...."
Franchise = Mid(Text,2,4)
Date = mid(Text,7,6)

So you would end up with
Franchise = 9099
Date = 07/08/29 set this as a date format and this will sort out the format

This can be done in Access, VB etc

Hope this helps!!

**BradHodge** · Sep 21 '07, 05:53 PM

Just subscribing.

Thanks.

**Clint Schaefer** · Sep 21 '07, 06:32 PM

Is the .dat file by chance a fixed width text file? If so, you can set up and save file specifications when you import the .dat file. File|Get External Data|Import. You can try changing the file extension to .txt before importing as Access will not recognize the .dat extension. Once you have selected your file, select "fixed width" and click "Advanced" near the bottom of the window. This will open up another window where you can enter your specs. Be sure to SAVE your specs as you can load them the next time you need them.

Once you successfully import the file, all data will be in a table and each field will be labelled according to your specs. If you need the proper formatting (ex. for dates) then you can at least work with one field at a time.

**mbatestblrock** · Sep 21 '07, 08:12 PM

Originally posted by Clint Schaefer

Is the .dat file by chance a fixed width text file? If so, you can set up and save file specifications when you import the .dat file. File|Get External Data|Import. You can try changing the file extension to .txt before importing as Access will not recognize the .dat extension. Once you have selected your file, select "fixed width" and click "Advanced" near the bottom of the window. This will open up another window where you can enter your specs. Be sure to SAVE your specs as you can load them the next time you need them.

Once you successfully import the file, all data will be in a table and each field will be labelled according to your specs. If you need the proper formatting (ex. for dates) then you can at least work with one field at a time.

Yes! I think this may be it. I think I will just have to work out some details as far as my lines beginning with a "+" continue the same record. I think once I get these continued in the same field i can accomplish my task.

I think I really like this website, thanks a ton!!! I'll post back progress.

**NeoPa** · Sep 22 '07, 03:17 PM

Please do.
You can also use your import/export specs (once defined) in VBA to transfer the data without having to start the wizard every time.

HOWEVER, continuation lines (implication being that records are not all in the same format) do rather stuff up this process.

If necessary, you may have to import the data into a very generic record of one long text string then write some code to extract the data from there into a more specifically defined table (or even into multiple tables if necessary).

**ADezii** · Sep 22 '07, 11:48 PM

Originally posted by mbatestblrock

okay... So this is my first post. this was my last resort after searching for three days straight..

so Ill just jump RIGHT in and pray that someone can help me out.

_______________ _______________ _______________ ______

-MY SITUATION-

Okay, everyday there is a large file sent to our server. This file is sent in a .dat format. File size= about 2.9KB (not the smallest thing ever)

it is FILLED with a ton of information pertaining to our stores such as sales, employee info, the works.

it is also highly coded. I have a, literally 50 page map on how to navigate the file and what all the field abbreviations mean.

Here is a sample of two records of the text file

//-----------------------------------------------------------------------------------------------------

1FN909907082907 0828AD003608239 137AAAAAAAAAA00 253100360840501 5LB070828EM0708 28LB070822LB070 823LB070824LB07 0825LB070826LB0 70827SA070828
+CA070828TR0708 28SS070828CF070 828MS070828OD07 082800000000000 000000000000000 000000000000000 000000000000000 000000000000000 0000000000000
+00000000000000 000000000000000 000000000
FN9099070829070 828AD0197287809 21AAAAAAAAAA002 531019728734014 SA070828CA07082 8TR070828SS0708 28CF070828MS070 828OD070828LB07 0828LB070822
+LB070823LB0708 24LB070825LB070 826LB0708270000 000000000000000 000000000000000 000000000000000 000000000000000 000000000000000 0000000000000
+00000000000000 000000000000000 000000000

---------------------------------------------------------------------------------------------------------//

for instance (without my map) the first record would mean something along the lines of

franchise number- 9099
date- 07/08/29

so on and so forth......

so now you know what the file... this information needs to be viewable by a few people and also readable...

I have racked by brain with trying to figure it out. I was under the impression that a query in access could handle something of this magnitude. I how to use tables, forms and other "easy things" in access very well. I have only needed reports to satisfy my needs with access so I am quite lost on this query business.

If someone could SHOVE me in the right direction I would be FOREVER in your debt. I am willing to use anything, not just access to do this. I just need to know what, I looked into xml and php parsing.. etc I dont know!!!!

please help!

-lost user
Mike

To me it obviously looks like a Fixed Width ASCII Dump and I think that Mr. Schaefer has the correct idea.

Change the File Extension to .txt.
File ==> Get External Data ==> Import.
Files of Type = Text Files ==> Import.
Select Fixed Width.
Do not set any Field Breaks, treat as a single Field ==> Next.
Select In a New Table ==> Next.
Under Field Options, set Data Type = Memo ==> Next.
Let Access add a Primary Key ==> Next.
Supply a Table Name for the Import ==> Finish.
It this process works for you, it will be a simple matter to Parse the Field and break it down into its constituent Fields as long as you know the Start and Stop Positions for each of the Fields.
Let us know how you make out with this.

**mbatestblrock** · Sep 25 '07, 03:44 PM

OKAY... wow, this is certainly a process and a half.... I have until Monday to look good and get this done..

here is the progress thus far.

the purpose of this is to pretty much have this automated as much as possible for some accounting people to get the info they need. Every day this file needs put into excel doc. That needs to be the end result.

I thank all of you for all your help, and I really help you can help out with this last stretch that is left....

to help out, here is a sample file http://www.mykesdesign s.com/frdata222.txt

I have a 50 page map of all the record types that are contained in this file. This file contains all the info for about 20 stores. The file is exported daily.

We need about 6 of the 49 record types that in here. On the map each record has about 3-15 fields. I have all the exact positions of these fields

for example the first record I need is "CD"

record layout-

position | length
Franchise # 2 6
Business Date 8 6
Record Type 20 2
Store Number 22 6
sequence Num 58 1
Voucher Points 70 8

I think you get the picture....

I have (with your help) successfully got this to output to excel using the import data and defining where these fields are positioned. However there are two problems with this. One it is not easy for people to reproduce. (I created a macro which makes it way easier though) 2. these are 5 other records they need to be exported in the same excel doc, and these records have some of the same positioning and different field names of the other records so they are clashing.

so in a nut shell I need to import this entire file and have it export into excel with broken up field and names. and too add to it, this needs to be done daily and added to the same excel file for a period of 13 weeks.

so right now I think (i dont know which is why I am here) I need to be working ALOT more with queries???? I dont know? I was really hoping you guys would be able to hep again!

I look forward to all your responses THANKS!

**ADezii** · Sep 25 '07, 04:57 PM

Originally posted by mbatestblrock

OKAY... wow, this is certainly a process and a half.... I have until Monday to look good and get this done..

here is the progress thus far.

the purpose of this is to pretty much have this automated as much as possible for some accounting people to get the info they need. Every day this file needs put into excel doc. That needs to be the end result.

I thank all of you for all your help, and I really help you can help out with this last stretch that is left....

to help out, here is a sample file http://www.mykesdesign s.com/frdata222.txt

I have a 50 page map of all the record types that are contained in this file. This file contains all the info for about 20 stores. The file is exported daily.

We need about 6 of the 49 record types that in here. On the map each record has about 3-15 fields. I have all the exact positions of these fields

for example the first record I need is "CD"

record layout-

position | length
Franchise # 2 6
Business Date 8 6
Record Type 20 2
Store Number 22 6
sequence Num 58 1
Voucher Points 70 8

I think you get the picture....

I have (with your help) successfully got this to output to excel using the import data and defining where these fields are positioned. However there are two problems with this. One it is not easy for people to reproduce. (I created a macro which makes it way easier though) 2. these are 5 other records they need to be exported in the same excel doc, and these records have some of the same positioning and different field names of the other records so they are clashing.

so in a nut shell I need to import this entire file and have it export into excel with broken up field and names. and too add to it, this needs to be done daily and added to the same excel file for a period of 13 weeks.

so right now I think (i dont know which is why I am here) I need to be working ALOT more with queries???? I dont know? I was really hoping you guys would be able to hep again!

I look forward to all your responses THANKS!

Let's assume that your Import Tables consists of a single Field named [ImportData] (MEMO). This Field contains the encoded Data in a single String Format, and is repeated on a Record by Record basis. You would then create a Query which would parse this single Field into its constituent components, since we now know the Field Names, Starting Positions, and Lengths. You would then Export this Query which should give you the desired results. The SQL would look something like this:
[CODE=sql]SELECT ImportData, Mid$([ImportData],2,6) AS [Franchise#], Mid$([ImportData],8,6) AS Business_Date, Mid$([ImportData],20,2) AS Record_Type, Mid$([ImportData],22,6) AS Store_Number, Mid$([ImportData],58,1) AS Sequence_Num, Mid$([ImportData],70,8) AS Voucher_Points
FROM tblStoreData;[/CODE]
OUTPUT using Dummy Data:
[CODE=text]
Franchise# Business_Date Record_Type Store_Number Sequence_Num Voucher_Points
123456 789ABC JK LMNOPQ M YZ123456[/CODE]

**mbatestblrock** · Sep 25 '07, 05:33 PM

I am going to give this a shot here in just a few minutes, thank you very much!

**mbatestblrock** · Sep 25 '07, 05:50 PM

kay, I think I understand that, but then how would I pull all the other record sets??? Sorry querys are sooo beyond me! .. which yes, I understand is not a good thing right now.

**mbatestblrock** · Sep 25 '07, 05:57 PM

I cannot get this at all

The syntax of the subquery in this expression is incorrect. I have no clue what I am doing!

how frustrating

**ADezii** · Sep 25 '07, 06:24 PM

Originally posted by mbatestblrock

I cannot get this at all

The syntax of the subquery in this expression is incorrect. I have no clue what I am doing!

how frustrating

For someone who is relatively inexperienced, you are tackling a not-so-easy project. Give yourself some credit and don't give up.

**mbatestblrock** · Sep 25 '07, 07:22 PM

I completely agree with you, however, if I can pull it off the benefits may be good.

Thinking about this over lunch.... is it possible to export multiple tables to an excel file???

**FishVal** · Sep 25 '07, 07:54 PM

Hi, there.

Just subscribing this time.

This is going to be not a simple project, but not very complicated.
Decoding logic may be advantageously stored in properly designed table set, as well as decoded data. Decoding process will require some rather simple programming.

I'll be back with more particular suggestions in 1-2 hours when get home.

Regards,
Fish.

Import .DAT File

Import .DAT File

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment