Post reply

Read in file layout in DTS?

  • January 29, 2009 at 12:13 pm

    #94464

    I've been search the site for a way to do something like this in SQL2K but haven't found anything. Can someone point me in a direction? I get files that are fixed length and come with a file layout in excel. The challenge is that the layout varies from file to file but I always have an excel file layout to tell me where the fields start and end. How can I get SQL2K to "read" the file layout and then apply it to an import using a basic DTS package or the like?

    I found this thread for 2005 but can't find anything similar for 2K.

    http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=j1ggjl55t1wash55uvlzwcqo

    Thanks

    Ron

  • January 29, 2009 at 12:45 pm

    #934997

    Hi Ron,

    If you know how to use bcp or bulk insert, you may want to create an 'Execute Sql Task' to do the same job.

  • January 29, 2009 at 12:58 pm

    #935007

    Richard

    Thanks for the suggestion, would you have an idea for how the TSQL would be structured that would be able to "read" the Excel file layout? Here's an example of what the file layout looks like in Excel:

    FieldField Name FormatLength Start

    1Field1 A 12 1

    2Field2 A 10 13

    3Field3 A 13 23

    4Field4 A 4 36

    5Field5 A 7 40

    The challenge here is that the file layout changes from one file to the next.

  • January 29, 2009 at 1:12 pm

    #935017

    Ron,

    Hope this link can help you.

    http://msdn.microsoft.com/en-us/library/aa173859(SQL.80).aspx

  • January 29, 2009 at 1:26 pm

    #935030

    It does thanks, we use bcp for importing files that are consistent in nature but this is a different animal where the file layout is dynamic?

  • January 29, 2009 at 1:36 pm

    #935038

    What does format A mean and do you have a property showing the field type in your layout file?

    How do you match fields for importing?

    I have a thought.

    task 1: use bulk insert to import your layout file to a table,

    task 2: use bcp to create a bcp fomat file.

    task 3: bulk insert to import you data.

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

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