Import .DAT File

[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

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

1FN9099070829070828AD003608239137AAAAAAAAAA0025310 03608405015LB070828EM070828LB070822LB070823LB07082 4LB070825LB070826LB070827SA070828
+CA070828TR070828SS070828CF070828MS070828OD0708280 00000000000000000000000000000000000000000000000000 000000000000000000000000000000000
+00000000000000000000000000000000000000
FN9099070829070828AD019728780921AAAAAAAAAA00253101 9728734014SA070828CA070828TR070828SS070828CF070828 MS070828OD070828LB070828LB070822
+LB070823LB070824LB070825LB070826LB070827000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000000000000
+00000000000000000000000000000000000000

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

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

[Ian Brown]

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

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]

Just subscribing.

Thanks.

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

[mbatestblrock]

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]

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]

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

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

1FN9099070829070828AD003608239137AAAAAAAAAA0025310 03608405015LB070828EM070828LB070822LB070823LB07082 4LB070825LB070826LB070827SA070828
+CA070828TR070828SS070828CF070828MS070828OD0708280 00000000000000000000000000000000000000000000000000 000000000000000000000000000000000
+00000000000000000000000000000000000000
FN9099070829070828AD019728780921AAAAAAAAAA00253101 9728734014SA070828CA070828TR070828SS070828CF070828 MS070828OD070828LB070828LB070822
+LB070823LB070824LB070825LB070826LB070827000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000000000000
+00000000000000000000000000000000000000

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

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.

1. Change the File Extension to .txt.
2. File ==> Get External Data ==> Import.
3. Files of Type = Text Files ==> Import.
4. Select Fixed Width.
5. Do not set any Field Breaks, treat as a single Field ==> Next.
6. Select In a New Table ==> Next.
7. Under Field Options, set Data Type = Memo ==> Next.
8. Let Access add a Primary Key ==> Next.
9. Supply a Table Name for the Import ==> Finish.
10. 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.
11. Let us know how you make out with this.

[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.mykesdesigns.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]

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

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;

OUTPUT using Dummy Data:

Franchise#    Business_Date    Record_Type    Store_Number    Sequence_Num    Voucher_Points
123456        789ABC        JK             LMNOPQ         M                YZ123456

[mbatestblrock]

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

[mbatestblrock]

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]

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]

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]

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]

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.

[mbatestblrock]

fish, looking forward to your response!

[FishVal]

fish, looking forward to your response!

Hi, there.

I've written a simple db. So far I'm attaching it without explanations. All questions tomorrow, now I'll go bed. :)

The db has one record you've provided and one mapping you've provided.
I've made an assumption:
- record type signature has the same length (2 symbols), e.g. 'FN' for franchise number record so you are able to split record to record type and fields string when import file

Find sub Decode in Module1 and run it.
So far it outputs to VBA immediate window, so make it visible if not so.

[mbatestblrock]

FISH thanks a ton. I am dumb and couldnt get it to work.... *sigh* but here is an update on everything....




----------------------------------------------------------------------------------------------------------------

Okay, So I am back..


PREFACE
----------------------------------------------------------------------------------------------------
http://www.thescripts.com/forum/thread711882.html

thats a link for my former thread..

I have provided links with files that I HOPE will help anyone out who wants to help me out...

ENTIRE RAW FILE
http://www.mykesdesigns.com/tblrock1/frdata.txt

FILE WITH RETURNS AND + TAKEN OUT
http://www.mykesdesigns.com/tblrock1..._noreturns.txt

TXT file to show structure I need
http://www.mykesdesigns.com/tblrock1/structure.txt



EXAMPLE of what final output should look like in excel for the accounting users.
http://www.mykesdesigns.com/tblrock1/pic1.bmp



and last but not least the map file for the fixed width text file
http://www.mykesdesigns.com/tblrock1/map.pdf


----------------------------------------------------------------------------------------------------------------


I hope this helps ANYONE who can help me out with this....
heres what I need to happen.. i need to be able to take that raw file and turn it into the screen cap of that excel (which was made by hand to show what I need)

these files come in to our server daily, so Once I figure out how to get this data in excel, I can create an automation process with macros just fine. However I am running into problems because I don't understand queries (and I know this is a huge problem) But if anyone could help me out, some have given me tremendous amounts of help so far and its getting closer and closer. I hope these links will help you understand exactly what I need.

even if this cannot be done in excel (which I believe it can) if someone could shove me in the right direction to get this accomplished, that would be amazing!

Thank you to everyone who has helped me and who are going to help me.

Looking forward to the next round!!!

-Michael

[NeoPa]

Michael,

I've updated your posts so the links work now, but I noticed the link to your previous thread actually links back round to this one.

[mbatestblrock]

woops, yeah I made that other thread first, and then copied it to this one. my bad for leaving that thread link comment in there!!

[FishVal]

Hi, Michael.

Data on the links

ENTIRE RAW FILE
http://www.mykesdesigns.com/tblrock1/frdata.txt

FILE WITH RETURNS AND + TAKEN OUT
http://www.mykesdesigns.com/tblrock1..._noreturns.txt

doesn't meet specifications on

http://www.mykesdesigns.com/tblrock1/map.pdf

Please provide .DAT file as is.

Regards,

Fish.

[mbatestblrock]

That is the .dat file as is with the exception of some number changed, and some names taken out for security reasons. What isnt matching up???

[FishVal]

That is the .dat file as is with the exception of some number changed, and some names taken out for security reasons. What isnt matching up???

Predefined field positions aren't matching up.

Length of the records are not equal as you've posted. So you'll need more intelligent import routine than simple import of text file.
The field of particular importance [Segment Length] specified to be at 53 appears at 44.
While I almost sure the spaces at the end of each record don't exist in neat DAT file, I don't know whether records contain carriage returns (I guess they do as soon as "+" symbol is specified as record continuation symbol but not sure).

I completely agree with you for the security reasons, but I would like to ask you to overwrite security sensitive fields with equal quantity of dummy chars rather than removing them at all making your question even more challenging for those who would like to help you in your project. ;)

The perfect raw data example would be DAT file with 3-4 records of different type (certainly with secure info overwritten with what you like) processed in some HEX editor (e.g. XVI32) to ensure nothing was added by text editor.

Regards,
Fish

[NeoPa]

...
The perfect raw data example would be DAT file with 3-4 records of different type (certainly with secure info overwritten with what you like) processed in some HEX editor (e.g. XVI32) to ensure nothing was added by text editor.
...

I use XVI32 too Fish. Some excellent software but he (Christian Maas) is no longer developing it, so don't expect future versions unless he changes his mind.