Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005

    Unanswered: import .dat file to SQL 2000

    My company designs our own banking software written in Progress. Currently twice a year (during our releases) we require our bank customers to run a program on their system that pulls certain data and dumps it to a .dat file. This .dat file was then imported (using another progress program) into a Progress database that our development team created (called stats.db) so we could have access to certain information regarding the customer's software/hardware set-up and utilization of programs,etc. This stats.db is now going away as we have a centralized SQL database for all customer information. The new process will be for our customers to send in their .dat files, they would be forwarded onto me, then I will need to import them into our SQL database. I am struggling in finding a way to do this. Our .dat files contain one field that holds the information, therefore the data is not delimited in such a way that I can just pull it into Excel, Access, CSV, etc. How can I go about pulling data out of these .dat files into a view/table in SQL for importing?

    I am at a total loss and have spent hours researching this issue. Any help will be greatly appreciated. Thanks...

  2. #2
    Join Date
    Feb 2004
    One field that holds the information? What do you mean? Is it a fixed field? Or did the progress software have some logic to dump this and/or that if/when/why/not available?

  3. #3
    Join Date
    Jul 2005


    I saved the .dat file as a text file, then imported the text file into a table in a sql database. However when I run a query in SQL Query Analyzer to select * from <tablename>, I receive 1 column for each line in the table.

    Here is a sample of the first handful of lines in one of the files when I open with>Microsoft Word:

    State Bank of Danvers Danvers MN 56231-0019
    BRANCH 1
    CIF Check Digit A
    Teller Machine B
    Inquiry Charging Yes
    Deposit Doc
    Doc Transfer
    Dep Doc File
    Credit Line Statements S
    LNA Coupons D
    Credit Bureau Format 2
    TRW Program ID
    Trans Union Program ID
    Jrnl Sort Order A
    Prf Machine Mnu H
    PRF Batch Input H
    Bulk File Sort C
    Bulk File OD Accts A
    Cash Letter A
    ACH Input A
    ACH2 Input A
    Primary ATM A
    Secondary ATM A
    Imaging Process A
    Voice Response B
    Internet Banking
    Activity Jrnls D
    GL Auto Balance B
    GL Auto Update Yes
    ALM Third Party
    Image Viewing A
    Branch Bal Acct 1126
    Mult Accruals No
    Customer Info 3,475
    CIF Alien Stat 2
    CIF Demographics 0
    Demand Deposit 1,864
    Account Recon 133
    DDA/Sav IRA 0
    DDA Mstr Combnd 699
    DDA Mstr Analys 2

    So I have different sections within this file which are marked by BEGIN/END statements. Each line item (other than the BEGIN/END statements) correspond to a field name in my SQL database. The value after is the line itme is the value that I need to populate my database with. I need to some how step through each line item in the .dat file to pull the field name and corresponding value.

    Sorry for any confusion...I wasn't sure the best way to explain. Hopefully this example clarifies. Let me know if you have any other questions...Thanks!

  4. #4
    Join Date
    Feb 2004
    I'm sure there are non-sql ways to do this (which might work better), but since this is a sql forum...

    I think that it'd be easier if the table in which the .dat file is imported has a row for each line, instead of a column. How do you import the file?

    Traferse through the import-table by either using a cursor (see declare cursor) or add a column that has a unique id and use that instead (see create table, identity; there's a recent thread about this).

    Making the assumption you have a row for each line, a way of getting the key / value pair is to reverse the row, look for the first space and go from there, fe:
    declare @myrow as varchar(15)
    ,       @leftpart as varchar(15)
    ,       @rightpart as varchar(15)
    ,       @pos as int
    set @myrow = 'key key value'
    set @pos = (select charindex(' ', reverse(@myrow)))
    set @rightpart = (select right(@myrow, @pos - 1))
    set @pos = len(@myrow) - @pos
    set @leftpart = (select left(@myrow, @pos))
    select @myrow, @leftpart, @rightpart
    Assuming the leftpart exactly matches a column, and the table to insert the value into is known, insert it into the table using dynamic sql, fe:

    declare @mySql as varchar(100)
    set @mySql = 'insert into myTable ([' + @leftpart + ']) values (N''' + @rightpart + ''')'
    select @mysql
    exec (@mySql)
    Doing an update instead might make it easier to select the lot afterwards.

Posting Permissions

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