User Profile

jnordfors · Sep 8 '20, 07:34 PM

Hi Adezii,

It doesn't look like I have any simple projects and it's been about 13 years since I last had to work on anything like this. I've forgotten most of what I knew.

I have something like this.

Code:

\\\
'EXPORT Queries TO EXCEL
Public Sub ExportQuries()
    'locals
    Dim obj As AccessObject
    Dim dbs As Object
    'set object
    Set dbs = Application.CurrentData

...

jnordfors · Aug 26 '20, 10:26 AM

THanks for the help. I will see what I can do with this. :)

jnordfors · Aug 25 '20, 12:22 PM

Hi Adezii,

I have attached the sample data in a prior message :). It is named 'sampleCompany1 .txt'

jnordfors · Aug 24 '20, 09:20 PM

You are correct, however all 4 conditions need to be met for anything to be written to a table

As a Side note, if all 4 conditions are met then rest of the data for '07DR Record' will also be written to their respective fields in that table as well.

Example: Position 1-5 written to [Plan Number] ect....

I hope this makes sense.

I did program this into seperate queries for each 'Record'....

jnordfors · Aug 24 '20, 05:29 PM

Hi,

I think I may be making this more difficult than it should be.

For example [RecordName] “3A Record”, has a number 1 in the [IsRecordIdentif ier] and 3A in [FilterIdentifie r] with the starting position, ending position and field length for the [FilterIdentifie r].

I would go to the refenced position for the [FilterIdentifie r] and filter for 3A.

Any data that has the 3A record...

jnordfors · Aug 24 '20, 12:52 PM

Hi ADezii,
Thanks for that!

The unique identifier is [CompanyName].

What I am trying to do is to use the information in TBL_RecordSpeci fications to parse out the data in participant dates.txt file in your example.

1. The [IsRecordIdentif ier] in TBL_RecordSpeci fications indicates what is needed to parse the participant dates.txt. (it’s a true/false field)

2. The [FilterIdentifie r]...

jnordfors · Aug 22 '20, 02:48 AM

Hi ADezzi,

Sorry about that.

I have a table with all the specifications in it. I would rather refer to that table in the code rather then hard coding the specifications into the code itself.

Is there a way that I can do that?

I also found that some of the file layout specifications for what the Record Identifier are not just the characters 30-31 as I had originally thought.
...

jnordfors · Aug 21 '20, 07:02 PM

Hi ADezii

Well That certainly makes all the queries I built to do this not needed. And yes that is what I am looking to do.

So If I am thinking about this correctly, This would be one table with all the different layouts within it based on the field names from the specifications.

Then I would parse it out based off the individual Record Specifications. .. OR would this create a new table for the individual...

jnordfors · Aug 21 '20, 03:10 PM

Great thanks!

I was trying to see if it could be done or if this is something that would have limitations in Access.

jnordfors · Aug 21 '20, 01:18 PM

Hi ADezii,

You are correct in your assumption.

There are no field names in the first row.

I have been able to create a form that allows the user to browse for the file location and choose witch specification to use while importing

Thanks

jnordfors · Aug 20 '20, 08:23 PM

There are about 60 different specifications.

jnordfors · Aug 20 '20, 08:22 PM

Hi

I built an import specification to import the text file as 1 column in a table in the database with a dialog box to be able to choose an import format and file location.

This is because we have different users that can parse out this data depending on the project they are working on.

I guess I need some help reading through the table once it is in access.

The format is attached,...

jnordfors · Aug 18 '20, 03:23 PM

That sounds like what I am dealing with. I do have the layouts of all the different record types.

However, each record type can have a different number of records to it depending on the file.

I can have 600 records in layout "A" on one day and 1000+ on a second day, this is the same with every layout.

Since I am a newbie to VBA is there some information you can point me to about this?

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Is there a way to export multiple tables from Access to different excel/text files

Leave a comment:

Leave a comment:

Leave a comment:

How to Import a txt fixed with file with multiple data specifications it it.