Community forum

Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
View
Go to last post Go to first unread
Offline chris.hill@adsigroup.co.uk  
#1 Posted : Friday, July 19, 2019 1:15:48 PM(UTC)
chris.hill@adsigroup.co.uk

Rank: Paid support

Joined: 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
Offline Support  
#2 Posted : Friday, July 19, 2019 2:38:32 PM(UTC)
Support

Rank: Official support

Joined: 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 Go to Quoted Post
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

Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!
Offline Gary_W  
#3 Posted : Friday, July 19, 2019 2:43:52 PM(UTC)
Gary_W

Rank: Paid support

Joined: 12/19/2016(UTC)
Posts: 212
United States
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.
thanks 1 user thanked Gary_W for this useful post.
Support on 7/19/2019(UTC)
Offline blm-vc  
#4 Posted : Monday, November 18, 2019 2:16:32 PM(UTC)
blm-vc

Rank: Paid support

Joined: 8/23/2018(UTC)
Posts: 4
Germany
Location: Berlin, Berlin

Originally Posted by: Support Go to Quoted Post

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

Offline thomas  
#5 Posted : Monday, November 18, 2019 2:41:50 PM(UTC)
thomas

Rank: Paid support

Joined: 12/10/2013(UTC)
Posts: 466
Norway

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.
thanks 1 user thanked thomas for this useful post.
Support on 11/19/2019(UTC)
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.

Scroll to Top