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

[ADezii]

Hello rabbit, hope this Post finds you well.

Company name doesn't seem to be required. Unless you're saying that a record identifier can be used by different companies and that the same identifier have different specs?

I was just going by what the OP stated in Post# 20, namely:

The unique identifier is [CompanyName].

Finding the correct record type should be possible without having to choose the record type before hand by evaluating the identifiers until a matching one is found.

Please expand, I am open to all suggestions.

Each different record type should be imported into a different table to save yourself headaches in the future.

I agree, there are 71 Unique [RecordName]s/Specifications with 270 Unique [FieldName]s. The demo simply populates an Imports Table that contains the Field Names defined in the Specification synchronized with the *.txt File, plus a couple of others. The overall game plane is to dynamically build the Tables as each Specification is declared.[/quote]

ADezii, you're doing a ton of work on this! Save something for the OP lol

Ironically, were it not for Covid-19, I would never be able to devote this much time for a Thread. Stay safe.

[Rabbit]

Sorry ADezii, I should have clarified that the first bullet was for the OP to verify that company is actually needed to identify the correct spec.

As far as

Finding the correct record type should be possible without having to choose the record type before hand by evaluating the identifiers until a matching one is found.

The idea is that you can loop through all spec record identifiers and their restrictions for each input row to automate finding the correct record spec. Similar to what you already do to verify that the row conforms to the chosen spec, but instead, you do it for every spec and every input row. This uses more cpu cycles but is easier for the user as it will process the entire file without the user having to preselect the spec to use.

Something along the lines of

for each input row
    for each spec
        fully conforming = true

        for each spec requirement
            if input row does not conform then
                fully conforming = false
            end if
        next spec requirement
    
        if fully conforming then
            spec count += 1
            spec to use = spec
        end if
    next spec

    if spec count = 1 then
        process row with spec to use
    end if
next input row

[twinnyfo]

I'm thinking along similar lines to Rabbit, but it is unclear from OP's text file exactly what I am supposed to be looking for.

I guess my question is this: For each record in the Text File, is there a way to know the spec that applies to that record? And, does that particular info apply to "every record"? To me, it seems a bit silly to send someone a text file, filled with records, with no easily identifiable way to distinguish between what types of records are contained therein. I fully recognize that, apparently, this is the problem. But is there a way to get the data with some sort of record-type indicator that would speed this process dramatically, rather than checking each line to see if it meets the specifications for each specification. Agreed, this is CPU overload.

I import text files all the time. There are lines of these text files that contain data I need and lines that I don't need. I simply examine several characters in each line to determine if it is a line to keep or discard. The import process for each line can be quite complex, but determining which line to keep--in my case--is the easy part.

I'm just wondering if there is a way to get these data files in a more usable format?

[ADezii]

@twinnyfo & Rabbit:
As always, your Input is always appreciated and well respected. At this point, I'm not sure what is up or down with this Thread (LOL).