Ask a questionAsk a question
 

Questionschema.ini problem~~

  • Friday, November 06, 2009 12:44 AMdragonhunter Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Hi, experts~~~ I have a question about how to import several TXT files into ONLY ONE Access table via "schema.ini"~~~

    I read some materials but still cannot find a way to do this.
    http://support.microsoft.com/kb/230265
    http://www.dbforums.com/microsoft-access/1644528-import-fixedlength-text-file-access-table-using-schema-ini.html
    http://support.microsoft.com/default.aspx/kb/199068
    http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
    http://support.microsoft.com/kb/210001

    My task is:
    1, I have several TXT files named P1.txt, P2.txt, P3.txt...... Pn.txt (n is changing for every month, and some files will exceed 65536 rows so I cannot use Excel to sum them)~~~
    2, I want to import all these n files into ONE Access table named Ptotal~~~
    3, All these n files have the same structure, just from different Company Sub-groups~~~
    4, Sometimes we will change the import specification~~ We change this specification almost every 2 months~~~ But most of the changing will be add or delete one or two fields~~~
    5, My problem is I have to change the inbuilt import specification manually every time~~ I have totally 130+ fields in my specification and some fields have values like:
    P1.txt: Access will treat this field as Number
    Contract Number:
    91
    121

    P2.txt: Access will treat this field as Text
    Contract Number:
    CB3
    16
    A9Q

    Actually I want this field to be Text. So everytime I need to manually change the format of this import specification and then saveas~~ I prefer to use "Schema.ini"~~ Is there some way to build Access inbuilt specification via "Schema.ini"?~~~ Or just have to copy "Schema.ini" and use VBA to modify the first row of this ini file everytime?

    I have some fake examples:
    Schema.ini:
    [P1.TXT]
    ColNameHeader = False
    CharacterSet = ANSI
    Format = CSVDelimited
    Col1 = Rate Double
    Col2 = ContractNumber Char Width 255
    Col3 = CustomerName Char Width 255

    P1.txt:
    14.090,"91","Killer"
    4.01,"121","Dragonhunter"

    P2.txt:
    0.001,"CB3","Demon"
    4.112,"16","Evil"
    5.2231,"A9Q","Orc"


    Thank you for your time. I really appreciate~~ Have a nice day~~~

    Li Xiang - UNL Actuarial Science Student
    Li Xiang || UNL Actuarial Science Student || Good at Access/ Excel/ SAS /SQL || SAS is torturing me~~~ || Still looking for a summer intern~~~ || Anyone wants to hire me????~~~~