Post reply

How to loop through text file row by row

  • November 5, 2019 at 3:38 pm

    #3695169

    Hi experts,

    I have one text file(no header - but i have the length for each column value) and need to get 3 values from 3 different columns from  each row(total will have 10 columns in each row and will have 20 rows in the file) and then need to pass those 3 values into a store proc to get a key for each row. after getting these keys for each row then need to insert into a SQL table.

    Any idea how to handle this? Cache? or just foreach loop? Appreciate !!

    thanks !

  • November 5, 2019 at 3:49 pm

    #3695174

    Aren't you just describing a headerless fixed width file? If so, why build a Script Component to iterate through the file, when a text file source would work much better.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 5, 2019 at 4:32 pm

    #3695192

    I'm with Thom.

    Sounds like a Flat File Source, to a staging table, followed by one or more ExecuteSQL tasks to fire off the stored procs you need to process the imported data.

  • November 7, 2019 at 8:59 am

    #3695814

    Hi Caojunhe24,

    If you want to respond to the topic, doing so is the best place, not the DMs. 🙂

    Caojunhe24 wrote:

    thanks Thom, i guess i might have confused you to describe with my poor English, so what i want is loop through each row in the text file to get 3 values from each file and then those 3 values will become input parameters for a sp. this sp will return one value which is key. i want to get this key for each row based on 3 values(combination) and insert this key only into SQL table.

    so i don't know how can flat file source can loop through each rows in the file.

    thanks

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 7, 2019 at 9:01 am

    #3695816

    You say that the SP returns a key, where is this key stored, in the RDBMS as well? It is just logic based on the values of the 3 parameters? If so you would be better off converting it into to a iTVF and then doing the whole lot in one go, not one agonising row at a time.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

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

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