  Rank: Paid supportJoined: 3/28/2018(UTC)
Posts: 21
Thanks: 6 times
Was thanked: 1 time(s) in 1 post(s)
|
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
|
|
|
|
|
|
  Rank: Official supportJoined: 12/1/2015(UTC)
Posts: 233
Thanks: 27 times
Was thanked: 13 time(s) in 12 post(s)
|
Originally Posted by: chris.hill@adsigroup.co.uk  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: Edited by user Friday, July 19, 2019 3:57:15 PM(UTC)
| Reason: Not specified |
|
|
|
|
|
|
 Rank: Paid supportJoined: 12/19/2016(UTC) Posts: 212  Location: Michigan, Dearborn Thanks: 49 times
Was thanked: 73 time(s) in 64 post(s)
|
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.
|
 1 user thanked Gary_W for this useful post.
|
|
|
|
Rank: Paid supportJoined: 8/23/2018(UTC) Posts: 4  Location: Berlin, Berlin
|
Originally Posted by: Support
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. Edited by user Monday, November 18, 2019 2:17:07 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Paid supportJoined: 12/10/2013(UTC) Posts: 466  Thanks: 22 times
Was thanked: 179 time(s) in 136 post(s)
|
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.
|
1 user thanked thomas for this useful post.
|
|
|
|
| Users browsing this topic |
|
Guest
|
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.