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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 4 (of 4 total)

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