Hi

I want to read through a fixed format text file a line at a time so I can then import the data into a SQL database.

I can't work out how to open the file and read each line in turn / within a loop? The read file obviously takes the whole content, what do I need to do please to loop through the file one line at a time?

Thanks

Hi

I want to read through a fixed format text file a line at a time so I can then import the data into a SQL database.

I can't work out how to open the file and read each line in turn / within a loop? The read file obviously takes the whole content, what do I need to do please to loop through the file one line at a time?

Thanks

Hi Chris,

What you need is a specific variable to type in to the SQL task, this one for example; {LOOP(CurrentValueXArray|0)}
You can find that when you go to Variables -> Jobs -> Active Jobs -> Loop.
We have a youtube tutorial that shows an example of almost exactly what you want to do. You can find it here: https://www.youtube.com/watch?v=hSPLLvBU2Ts 

---

Unless you have special reason for line-at-a-time reading, use the bulk loading tools for your RDBMS to load data into tables. Performance will be much faster. Use VC for detecting a file arrived, then call the bulk loader utility.

Hi Chris,

What you need is a specific variable to type in to the SQL task, this one for example; {LOOP(CurrentValueXArray|0)}
You can find that when you go to Variables -> Jobs -> Active Jobs -> Loop.
We have a youtube tutorial that shows an example of almost exactly what you want to do. You can find it here: look up

Greetings,
this works much faster compared to my first approach splitting input multi row files into single row files on external process.
Just one thing. How to mask input data while there are ctrl-like characters in "CurrentValueXLine"?
The insert stops with an error if for example a ' appears in the line.
We use Oracle and the masking seems not to work in SQL text query.

I'm not sure what you mean by masking the data, but if you have control characters and such in the file, you need to clean the data before importing it.
You could have a separate generic task/job that removes these type of characters from a file that you pass in as param. This can be done with eg regex.

Anyway, just to repeat what Gary said in his comment. Use built in db features. You are using Oracle. They have something called Extrernal tables. If you are not familiar with it, look it up. They are awesome for importing data from files into tables. you can clean the data, insert and log errors all in one go.