Importing data from a .dat file to append to a table?

[TKTheKid]

Hi all,



Please see the attachment.

I'd like to be able to filter through a .dat file like that (which is pretty much just like a .txt file as far as I can tell) and have that data appended into a table by way of a command button.


The annoying thing is that the data would have to be pulled out of the .dat file by position into columns like this:

Example line:

09  19192                                  00010212b1            5010570223

19192 = Ticket (Will be 5 digits long)
0001 = Quantity (Should be 4 digits long)
0212b1 - Location (Will be 6 characters long)
5010570223 = Article (Will be 10 characters long (not numbers))

Is this feasible? I'm really not sure how to even begin with this. By the way, let's say the .dat file will be in folder C:\CENTURY\WTERM.

I'd appreciate any help.

Thank you very much!

Tony

[June7]

Importing from text files is common topic.

If the file has a consistent stucture, the import wizard might be adequate. Code could involve TransferText or opening text file as an object and reading line by line.

Review this recent discussion and referenced links in it: https://www.accessforums.net/import-...ess-30365.html

[TKTheKid]

Okay, so I tried to use the import wizard to append to tableTestImport that I already have in place. I used the import spec "Inventry Import Specification."

It looks like I have it all set up okay, but I get errors about hundreds of records being "lost due to key violations." What do I have wrong? And why can't I set the type of each field that I'm trying to import? Where would I do that?

[ssanfu]

I noticed a couple of things that could be a problem.

As far as the import, the "lost due to key violations" is due to the field "Ticket" being set as the PK for the table. A PK field by its nature has unique values and usually a Long type. You can use text type fields as the PK, but it slows down Access considerably. Remove the PK from the field "Ticket", Add another field - I used "ImportID" - of type Number.

You will need to run a delete query (where the fields Loc, Quanity and Article are NULL) to remove the first and last record in the text file.


-----------------------------
You should use only letters, numbers and the underscore as object names. No spaces or special characters.

It is not a good idea to have field names begin with numbers. Plus it is not very descriptive. "0-60" what??

Said this before, but PK and FK fields should be Long integers.

[TKTheKid]

Ah, it was an issue with the primary key! Thank you Steve!

[ssanfu]

You're Welcome