Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: design advice...writing a text file

    I need to create a text file using information from SQL tables/views in the following format...Can anyone recommend a direction or procedure to look into, i.e, sql script, custom dts, etc. The items in parentheses identify specific portions of the text file.

    (01)
    101081,84423,customer ,072304,customer ,11310 Via Playa De Cortes , ,San Diego ,CA,92124,
    (02) 6 ,1 , , , , ,22 ,1 ,0.00 ,160.46 ,160.46 ,0.00 , , , , , , , , ,1,1
    (03)B130907540,5.41 ,1
    (03)B130907550,5.41 ,1
    (03)B130907560,5.41 ,1
    (03)B130907570,6.04 ,1
    (03)B065007550,1.72 ,2
    (03)B065007560,1.72 ,6
    (03)B519926530,4.66 ,13
    (03)B519926550,4.66 ,12
    (03)B560911200,2.14 ,1
    (03)B560912500,2.14 ,1
    (03)B095305750,3.65 ,1
    Thanks,
    Bill

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This looks a lot like EDI format to me. Maybe it is just because of all the bad memories of it. Given a choice, I would go with a scripting language outside of SQL Server. Either PERL or VB Script. I believe PERL was designed with such file formats in mind, and it is not that hard to learn.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Perl would make the solution easier to code. VBA could be incorporated into a DTS package, which would be a bunch more portable (and easier to write if you already know VB and don't know Perl).

    Pick your poison. Either Perl or VBA would work nicely, and each has its own benefits.

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually EDI (X12) looks more like this:

    CAS*PR*1*24**2*12~CAS*CO*45*40~...etc., all one line.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Could well be I have the wrong name for it, then. Like I say, it has been a while. The format I had to deal with was:

    header row
    first item header
    first item detail
    first item detail
    first item footer
    second item header
    ...
    ...
    footer row.

    A very nested and finicky format. In my first job, I spent a number of weeks trying to get an output that would work, but kept getting blank lines in my output. Nowadays, I look back on that and laugh. Probably take an hour with different tools. Back then, I was truly "a man with a hammer".

  6. #6
    Join Date
    Aug 2002
    Posts
    2

    Smile Fastest way!!

    The fastest way to get data out of SQL to text is using a BCP (bulk copy paste). I've written a few EDI formats for medicare/medicaid billing and such using BCP and it's works like a charm. Get all of the data together in a temp table first and then use something like this in a stored procedure to export the data:

    SET @EXPORTSQL=
    'BCP "SELECT * FROM ##TEMPTABLE" QUERYOUT C:\FILE.TXT -c -t,'

    EXEC MASTER..XP_CMDSHELL @EXPORTSQL

    This will export a comma separated values version of the temp table to a file. I have to upload mine to an FTP site, so I have a mapped drive on the server attached to that FTP site and then change the path to that mapped drive letter. The proc is then effectively creating the file and uploading in less than a second or two.

    In my experience DTS is great, but there's not need to over complicate the product when a couple lines of SQL can get you there!

Posting Permissions

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