Importing Data Question

[JVZ]

I have a text file that I need to import, however know they changed the format of the file and I having a some problems thinking up a way to import this in.

Here is the layout of the file:

FieldOne|FieldTwo|FieldThree|FieldTwo|...and so on to FieldNine. (All fields are delimited with "|", except for fieldNine, which is delimited by Fixed Width)

What I need to do is take the first eight fields and import them into TableA, then take fieldNine (which is a string with the lenght of 210 characters) break the string up and import that into TableB.

Our current process just imports that whole line into TableA (using TransferText, however the new requirement is to create a one-to-one relationship with TableB.

The only way so far that I know how to do this is to write code that opens the txt file reads a line and insert that values into the TableA, and then take fieldNine break the string using MID(), then insert and commit...

I was wondering if there is another way...each file contain 300,000+ line...so the above process may (I'm thinking) will take way to much time...

Thanks in advance for you time and ideas.

 

[RickSpr]

You're not gonna get away without writing code to break out Field9, that's for sure. But I think you can improve on the all-code solution.

First, use a Delimited import spec to load TableA. Just set Field9 to be skipped in the spec. (To build an import spec, start the manual import process, walk through the wizard until you get to the Finish page, and click the Advanced button. You'll see a grid where you can set Field9 to be skipped. Be sure to save this spec before you exit. When you return to the Finish page, you can either continue or cancel--your spec will still be saved.) You can name the spec in a TransferText call from then on.

Second, I'd write a procedure that reads the file and copies JUST Field9 to a new text file. Use InStr() in Access 97 or InStrRev() in Access 2000 to find the last "|" character. This file copy operation will run very fast. (Warning: If the possibility exists that Field9 will contain a "|" character, don't use InStrRev! Use InStr() and count your way past the first 8 "|"s.)

Third, manually import the new file containing just Field9 and create a Fixed Width import spec, again saving the spec before you exit.

From then on, you just run your file copy procedure, then run the two TransferText operations with the saved import specs. (You still need to create the one-to-one relationship, if you want RI enforced, but I didn't take your question to be asking how to do that.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein