Post reply

Creating HUGE TEXT FILE HELP!!!!

  • January 11, 2006 at 1:08 pm

    #103035

    I wonder if any one can help!!! URGENT MATTER!!!!!!!!!!!

    I have to create a huge flat file using one table as my source. The script has to modify the justification of the data (some fields to the right some to the left) and the fields have to have a length defined by the company we are sending this files to (ADP, in case you are wondering)

    Well the scripts can create the file and the justification work but I run into a new problem. The text file when is created truncate the last secion of the table.

    I have to import data for 502 positions, but when the script gets to position 256 no more data is coming in. Am I making sense here? This is driving me NUTS!!!

    I run some other programs to create files for ADP, but never got to this situation. The script truncates the data regardless of the tool I use, Query Analyser or DTS. Any suggestions??????

    Because ADP has very strict settings for their files what I used was concatenation to get the files in the right format, but this last one did not work.

    My scripts looks something like this:

    Select RecordID+RecordCode+TotalTaxAmounts+QuarterTaxAmounts+YearTaxAmounts from taxtable

    To justify the amount fields I created a case statement so the query looks like:

    Select RecordID+RecordCode+

    CASE LEN(TotalTaxAmounts)

    WHEN 6 then '          '+replace(TotalTaxAmounts,'.','') END +

    CASE LEN(QuarterTaxAmounts)

    WHEN 6 THEN '          '+QuarterTaxAmounts END +

    CASE LEN(YearTaxAmounts)

    WHEN 6 then '          +YearTaxAmounts from taxtable

    Of course the file has a lot of fields to be brought in and that is where the problem showed up. The case statement works fine, but for some reason the text file creation truncates anything after position 256. PLEASE PLEASE PLEASE PLEASE!!!! HELP

     

    THANK YOU IN ADVANCE TO ANY SUGGESTION!!!!

  • January 11, 2006 at 1:14 pm

    #614181

    >>Select RecordID+RecordCode+TotalTaxAmounts+QuarterTaxAmounts+YearTaxAmounts

    So you're concatenating all the columns together in SQL before exporting it ?

    You're probably running into max size of a varchar, hence the truncation. Use each part of the tool to do what it does best. Use SQL to select discreet columns, use DTS to build the fixed-width file output.

  • January 11, 2006 at 2:53 pm

    #614199

    Are you doing this in Query Analyzer? If so, in options under results the max number of characters returned for a single column is 256. Change it to what you need.

  • January 12, 2006 at 9:09 am

    #614402

    Fernando,

    I have run across this problem as well.  Indeed the Query Analyzer limits the column width by default to 256 chars.  This can be changed in the options as indicated above.

    Interestingly, DTS does the same thing by default.  The max per column is set to 255 characters.  Since you are formatting the entire row as a single column, you'll have to convince DTS to output the entire column.  I maintain EDI procedures that do the same thing, so I've run into this. 

    The easy way in DTS is to open the package, and select the "File>>Disconnected Edit" menu item.  You'll get a GUI pop.  Under the Connections container, locate the text output object, open it up and pop open the OLE DB Properties for the connection.  In the list, you'll find "Max characters per delimited column".  Change the value to meet your needs.

    This is a tricky one, because Query analyzer and DTS both give (almost) the same results.  So it is easy to blame the SQL, and not the output. For a while, I thought that I had found a bug in SQL server.

     

    hth

    jg

     

  • January 12, 2006 at 9:14 am

    #614405

    Anytime you come across the number 255 or 256 you should be suspicious of configs and settings, since this is the 'old DOS' max-limit for many things.

    /Kenneth

  • January 12, 2006 at 2:41 pm

    #614493

    CREATE TABLE TAXTEMP (TXT VARCHAR(3000))

    ** Using CONVERT AND REPLICATE **

    ** Here is a sample to build EDI 810 file **

    TXT = CONVERT(CHAR(2000),'DTL' + '|' +

    REPLICATE(' ', 20 - DATALENGTH(RTRIM(A.FITEM))) + RTRIM(A.FITEM) + '|' +

    REPLICATE(' ', 10 - DATALENGTH(RTRIM(A.FSHIPQTY))) + RTRIM(A.FSHIPQTY) + '|' +

    REPLICATE(' ', 2 - DATALENGTH(RTRIM(A.IT103))) + RTRIM(A.IT103) + '|' +

    REPLICATE(' ', 17 - DATALENGTH(RTRIM(A.FTOTPRICE))) + RTRIM(A.FTOTPRICE) + '|' +

    REPLICATE(' ', 2 - DATALENGTH(RTRIM(A.IT105))) + RTRIM(A.IT105) + '|' +

    REPLICATE(' ', 2 - DATALENGTH(RTRIM(A.IT106))) + RTRIM(A.IT106) + '|' +

    REPLICATE(' ', 48 - DATALENGTH(RTRIM(A.AGCOPART))) + RTRIM(A.AGCOPART) + '|' +

    REPLICATE(' ', 2 - DATALENGTH(RTRIM(A.IT108))) + RTRIM(A.IT108) + '|' +

    REPLICATE(' ', 48 - DATALENGTH(RTRIM(A.FPARTNO))) + RTRIM(A.FPARTNO) + '|' )

    Binh

  • January 13, 2006 at 11:35 am

    #614710

    Thanks to all. I did change the maximun characters in QA and it went fine, I will follow the suggestion for DTS now that I am going to run into the same issue.

    In any event, I went back in time and used bcp to create my text file and it works fine. Thank you again for all your assistance.

    The bcp command was very simple due to the fact that I have the table with the same length values requested. With this said I just created the bcp command to do as follows:

    bcp "ADPTransporter.dbo.ADP_Wages_TypeW" out "C:\TextFileLocation\MainDirectory\Subdirectory\FileName.dat" -c -q -t -U"User ID" -P"password"

    With that the file was created as quickly as DTS did and it worked either way. Thank you again.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply