Can I automate the import of a text file?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a text file that I need to import on a regular basis. It has upto 200
lines of text that look like this
BW53TUVWDB2093612F103025 0119200920E2MERCEDES CLK 01
ML06GKGZFA19900000085911 0120200906G4FIAT PUNT01
N124DFCJF1GF8KD3SG011553 0120200920E3SUBARU IMPR01

I can use the wizard but I wonder whether there is some way to import the
text file with some VBA code behind a command button?

Each line of text needs to be split like this

ML06GKG | ZFA19900000085911 | | 01202009 | 06G4 | FIAT |PUNT|01

7 characters| 17 characters | 4 characters | 8Characters | 12 Characters |
Field1 Field2 Spaces Field3
Field4
4 characters | 2 Characters
Field5 Field6
The groups of characters represent the fields in the table I want to import
into apart from the 3rd set of 4 characters which are always spaces.

Is this possible? I'm not a VBA expert so some example code would be
gratefully received, I realise that's asking a lot .

Thanks
Tony
 
I have a text file that I need to import on a regular basis. It has upto 200
lines of text that look like this
BW53TUVWDB2093612F103025   0119200920E2MERCEDES    CLK 01
ML06GKGZFA19900000085911     0120200906G4FIAT            PUNT01  
N124DFCJF1GF8KD3SG011553     0120200920E3SUBARU        IMPR01

I can use the wizard but I wonder whether there is some way to import the
text file with some VBA code behind a command button?

Each line of text needs to be split like this

ML06GKG | ZFA19900000085911 |     | 01202009 | 06G4 | FIAT            |PUNT|01

7 characters| 17 characters | 4 characters | 8Characters | 12 Characters |
    Field1           Field2               Spaces           Field3          
Field4    
4 characters | 2 Characters
   Field5             Field6
The groups of characters represent the fields in the table I want to import
into apart from the 3rd set of 4 characters which are always spaces.

Is this possible? I'm not a VBA expert so some example code would be
gratefully received, I realise that's asking a lot .

Thanks
Tony
Click to expand...

so this is just a fixed length record? C'mon! You can do this with
the Import wizard and save the import specification. You might need
to click the advanced tab or whatever on the import specification, but
the rest is like taking a candy from a baby...
 
I'm using Access 2003, can you save Import Specs in that, I've not seen
anywhere to do this, although I have seen it in 2007
Thnaks
Tony
 
Back
Top