Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Post Unanswered: Importing text files into Database

    I inherited an Access database in which is used to import text files (.txt) that contain test score data. The data in the text files is in the “Fixed Width” format; all the fields are equally separated by spaces and aligned into even columns.

    The users of the database wish to add more fields for import and which is causing the import process not to work properly. I created a new table to accommodate the new data fields, 56 for each corresponding column in the data file. The properties and format match for the new columns match the properties and format from the old columns.

    This is where I'm getting stuck: the old importer was meant to import only seven fields of data whereas now we need it to import 56. For some reason the code in Visual Basic is not recognizing the new data fields.

    I would like to post a copy of the code for everyone to see but I don’t know if I am allowed to. I am somewhat new to Access 2010 and any help anyone can provide would be appreciated.
    Last edited by mando013; 03-05-13 at 13:43. Reason: Clarify Access version

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    eek going from 7 columns to 56..... thsats one heck of a change, Id' dbe suspicious that the data model/ table design was right.


    how are you importing....
    if you are using am input format specification you will need to change that to the new format (or better yet make a copy and modify that copy.

    again depending on how the import is done you may also need to modify your table design to accomodate the new number of columns.

    is this a visual basic problem or a an Access problem? we don't know how you do your import
    if you post your code (just the bit thats not working as opposed to he whole slug of code for the project)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    4

    More info

    Hi healdem, thanks for the quick reply. Here is the code referencing the import commands for the database. The first section is the one of interest; however I will include the second section just in case it has something to do with it.

    Two files are being imported, one containing scores and the other containing names. The first section, for the score import is the faulty one. The second section for the names is working fine.

    Here is the code:

    If Len(FileLocationScored) > 0 Then
    DoCmd.SetWarnings False
    DoCmd.TransferText acImportFixed, "InTrainingScoredFile", "T_Score", [FileLocationScored], False
    DoCmd.SetWarnings True
    End If

    If Len(FileLocationNames) > 0 Then
    DoCmd.SetWarnings False
    DoCmd.TransferText acImportDelim, "InTrainingNameFile", "T_Name", [FileLocationNames], False
    DoCmd****nSQL "UPDATE T_Name SET T_Name.CandidateID = Trim([CandidateID]), T_Name.ProgramID = Trim([ProgramID]);"
    DoCmd.SetWarnings True
    End If

    MsgBox "Import Complete."

    This is an MS Access 2010 Database and I am looking at the code through Microsoft Visual Basic for Applications.

    I am looking at the data files to make sure they are formatted correctly to make sure they match the new columns on the new table.

    If you need any more info let me know and thanks again for the reply.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so there are various elements here
    1) you have a text file containing the data to be imported [filelocationscored]
    2) you have a destination table [t_score]
    3) you have a format specification (effectively think of it as a set of translation rules that take data from the file and push the data into the table) [intrainingscoredfile]
    all 3 must be in step

    see:- DoCmd.TransferText Method (Access) for details of the transfertext macro.

    the number of columns in the table must match the number of items per row in the input file AND the translation rules must match

    so assuming you have defined your table to match the new input file you need to look at the transfer rules specification InTrainingScoredFile.

    ..thats where you should look first
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    4

    intrainingscoredfile

    So I have been looking for something that contains the set of rules for the format specification in [intrainingscoredfile] but I have not been able to find anything that references it (like a Macro or something) in the database.

    Do you have an idea as to where I can look?

    I found these 2 articles online:

    Save the details of an import or export operation as a specification.

    Run a saved import or export operation.

    I looked in the Saved Import Tabs to see if there were any Managed Data Tasks but there is nothing there. Could the rules for intrainingscoredfile be imbedded somehow? If I could find them I could edit them and fix the problem as you suggest.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    It is possible to manually amend the import specification, but you need to know what you're doing, and even then it's a little bit dodgy.

    The easiest and most reliable resolution would be to try manually importing the file into the table, so that you can line up the columns in the file with the fields in the table. Save the changes with a new specification name (you never know when you'll need an old spec!), and use the new name in the place of "InTrainingScoredFile" in the VBA script.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Mar 2013
    Posts
    4

    New Rules

    That sounds good weejas, thank you for the suggestion. I was thinking of doing a new set of rules based on what I read on those articles. Amending the old one to accommodate 49 more fields did seem like a daunting task.

    What was odd was that I can’t find the old rules anywhere (so I can edit them). That’s why I was thinking they may have been embedded somehow into the DB.

    Either way that will be irrelevant if I can create a new set of rules. I will try to do that and see if it works, if it does I will post my results.

    Thanks for the suggestion.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    The rules are found in system tables. I had a problem recently, whereby I copied an Access 2000 .mdb file using a PC with Access 2010 installed, and the import specs all vanished. I tried importing the system tables over manually, but that failed, so I resorted to recreating the new copy on an older PC.

    Anyway, manually creating new specs to go with the new files is the way to go!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •