Importing records from text file based on first character or code in one field

[tagteam]

I have two questions and might need two posts but have a feeling that they will both be solved the same way.

I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
for example:
If the record is a Non wheel repair then column 167 is a 50 character description
If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields

So I need to do the following


1. Read the first character in the line and determine if it is a number 1 and if not discard it
2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.

I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)

One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.

Thank you for any help

[JoeM]

I think the easiest way would be to import it into a Temporary table as two text fields, one that reads the first space, and then another for the rest.
Then, you could write queries that identify/split the records and fields as per your requirements, and then use Append queries to add them to your final table(s).

[Dal Jeanis]

Typically, I would
1) Import everything, including that first digit, into a temporary table.
2) Run a delete query that killed all the records that didn't have '1' in that first digit.
3) Run an update query that, if the type code was Wheel, copied the ten fields into a new table or new field in the same table, whichever is appropriate.
4) Run an update query that, if the type code was Wheel, truncated the note field to the correct length.
5) Run an update query that fixes any date issues.
6) Then, run a query that copies the records from the work table to the real tables, and deletes the work tables.

My phrasing of 3 is vague because I don't know whether you have normalized those ten fields into a second real table or not. My phrasing of 5 is vague because I can't see the data.

All of those, with the possible exception of number 5, can be efficiently done in SQL rather than VBA. Experienced programmers would tend to use VBA around those queries, rather than a macro, but it's just as effective to use plain SQL and macros.

If your date fields on the incoming file are exactly as stated in your question, then you could easily code an update where, if the last two digits are space, they are updated with either the first or last day of that month.

Hmm. Wouldn't even need that. Create and populate a translation table with six-digit from and to values, something like this:

tbl_FixDates
OldDate NewDate
1302     130228 
1303     130331 
1304     130430 
1305     130531 
1306     130630

Run an update query to change all the first to the equivalent second in a date field, wherever they occur. Once again, it's an easy SQL fix, no VBA required.

UPDATE tbl_Work
INNER JOIN tbl_FixDates
ON  tbl_Work.MyDate = tbl_FixDates.OldDate
SET tbl_Work.MyDate = tbl_FixDates.NewDate;

[JoeM]

Seeing that you re-posted the question here: https://www.accessforums.net/program...ort-35687.html, I take it you didn't like our approach.
You could certainly do it that other way you like, but it will be a bit harder. You will need to be fairly proficient in VBA and dealing with recordsets in order to do that (or have someone who is do it for you).

[tagteam]

No, not necessarily. I am just trying to think of a couple of different ways to do it. I prefer the method in this post because it is something I can do with my skill lever. However, I was thinking that cleaning it up before import might make for a more simple import. And yes, I will have to pay someone to do it if I do the coding way and I don't like that because I won't understand it as much. I am much better (as are most people) at reverse engineering something since I am not a vba coder but do a lot of php, html, msyql etc and pick things up.

I realized during the second post that I will actually need two tables because I can't have overlapping fields so if i cleaned the file up first, I could create two new files, import each one into their own table, then run a query to append them to a combined table. That leaves several blank fields but I dont really want to have to deal with constantly querying two tables for each report related to the repairs.

Today I will be building the method mentioned in this post since it is something I can manage. I have the other post because I want to explore that option, see costs, benefits, disadvantages etc. Not only for this project but the next time I do another like this I will have a better grasp on the possibilities.

Step 1 is no problem
Step 2 is no problem
Step 3 is my first issue - Depending on how I pull everything in to the temp table I will not actually have the "10 fields" just a certain number of characters. So I am not sure what to do here. One of the reasons for exploring the other method is that I would end up with tow files, each specifically for one of my two import specifications. Once it is in the temp table I can't use the specifications anymore (to the best of my knowledge because that would mean importing data into a database it is already in)
Step 4 will have some issues based on how I do step 3
Step 5 is no problem
Step 6 is no problem

So really, step 3 is the one that I really need to get a handle on. Sorry for the detailed explanations. I really want to not just learn how to do this task but learn the how's and why's so that in the future i have reference for what is the best way to do things.

Can you please help me think through step 3 and find the best solution? I can provide more detail if that would help. I am not entirely sure to to do what you mentioned in your post "Then, you could write queries that identify/split the records and fields as per your requirements, and then use Append queries to add them to your final table(s)." I only know how to do that with an import specification.
Thanks in advance

[tagteam]

I am trying to start by creating an import spec that has two fields.
Record Format - text - start 1 -field length 1
RemainingData - text - start 2 - field length - 500

It will not import and gives me the error: "The search key was not found in any record"

I have googled and checked forum and most people have problems with this with space in field name, or space before field name, I dont have either of these issues.
Am I missing something?

[Dal Jeanis]

You can leave them all in the same table if you want, and the non-wheel records will just have a NULL in the MyCode01 - MyCode10 fields.
You're going to slap your forehead when you see how easy it is to do Steps 3 & 4:

Step 3:
UPDATE MyTempTable
SET MyCode01 = Mid(MyDesc50,29,2),
    MyCode02 = Mid(MyDesc50,31,2),
    MyCode03 = Mid(MyDesc50,33,2),
    MyCode04 = Mid(MyDesc50,35,2),
    MyCode05 = Mid(MyDesc50,37,2),
    MyCode06 = Mid(MyDesc50,39,2),
    MyCode07 = Mid(MyDesc50,41,2),
    MyCode08 = Mid(MyDesc50,43,2),
    MyCode09 = Mid(MyDesc50,45,2),
    MyCode10 = Mid(MyDesc50,47,4)
WHERE MyCode = "Wheel";

Step 4:
UPDATE MyTempTable
SET MyDesc50 = Left(MyDesc50,28)
WHERE MyCode = "Wheel";

Obviously, you'll need to use your own field names and record layout.

[tagteam]

Hmm, very interesting. So I could
1. import all using one specification to a temp table
2. use del query to remove all records that dont start with 1
3. use append query to take all valid non wheel records to main table and leave null values for the fields relating to wheels
4. use update query above to create temp table 2 with valid wheel records
5. use append query to take all valid wheel records to main table
6. update text date fields to real date fields
7. remove temp tables

Thanks for the update query above, that is what I will need to try create this over the weekend. I will report back with any issues or problems I see.

One quick question i do have: the records that dont start with 1 have very different data so I always get that extra table after the import that shows the import errors. Is there anyway to keep this from creating or is it best to just have a macro to delete it late?
Thanks,

[Dal Jeanis]

Move your step 6 to be step 2.5, and you could do it that way. It's not good practice to move data over to the real table before the data has been fixed.

You could instead start off by populating your first temp table with NULLs in the fields relating to wheels, update it in place to move the data to those fields for the wheel records only, and then run your append query once for all the records. Both roads lead to the same Rome.

Regarding the import errors, I don't know. You might post that as a new thread, so you get more eyes on it.

[tagteam]

Still working on this issue, sorry for late replies
I am working out the best way to do this and realized I should probably ask this first:
I need to import records but the import specification is actually based on a field in the record. It is for repairs and one particular type of repair will need a different import specification. If it is not a wheel record all there is a particular field that is called Wheel Narrative. If it is a wheel record that field, Wheel Narrative, is actually broken down into 11 more fields. (most of this you can get from previous posts)
is it better to
1. Import all the data into a single table that has the Wheel Narrative AND all the other fields in the same table? Then after import i would run a job that would look at the table and if if finds a wheel coded run a query on that record and use string length formulas to move the data into the separate fields.

This seems like it would be easier for my reports later but will leave a lot of empty space in the database

OR

to have two different tables that have the data separate and either split it on import or after the import.

Thanks