DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 143

Points: 2340

Post Posted: Thu Jun 20, 2019 1:33 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi,
I have a potential issue I am hoping to circumvent before it happens (matter of time). We have a few external sources that we have to bring in from sequential files. The issue is that a few of these have to be manually generated by that department. It is only a matter of time before someone incorrectly generates one of these files in that the structure is different, therefore the file definition that we have in the datastage job doesn't match. In our setup, unfortunately, if that were to happen, our staging process would stop and we would need to manually review and fix the issue to continue nightly processing. Also unfortunately, datastage's Validate, doesn't check if the incoming source file itself will work as its defined. I was hoping someone had some suggestions on this.

Thanks,
Jackson

_________________
-Me
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Thu Jun 20, 2019 6:08 pm Reply with quote    Back to top    

So, you're looking for something to validate the file structure so you can skip loading any that could jam up your staging process? I had to build something several years ago where the sequential files we had issues with were csv files from a spreadsheet source that people would sometimes... "adjust". That or mess up the formatting or add a ton of blank lines at the end from improper deletions. From what I recall, we read each record in as a single string and then did some simple validations:

Validate the delimiter count
Validate the header column names are there in order
Validate data types / conversion to type
Validate any columns with "valid value" lists

Caught most of the issues from what I recall but not all. I could pull the code out of archives and see what (if anything else) we did if that would help.

I wonder if there is any kind of third party validation utility that could be leveraged? Did a quick search for "csv file format checker" and seems like that may be an option for you.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 143

Points: 2340

Post Posted: Thu Jun 27, 2019 5:12 pm Reply with quote    Back to top    

Looks like CSV Lint might work. I was considering writing my own, may still do that but I will research some other tools that already exist first.

https://csvlint.io/

_________________
-Me
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222463

Post Posted: Fri Jun 28, 2019 9:03 am Reply with quote    Back to top    

Cool

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
kumar_s

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 16 Jun 2005
Posts: 5245

Points: 26379

Post Posted: Sun Jun 30, 2019 3:59 pm Reply with quote    Back to top    

I have done the Craig's suggestion in my past. Especially the top 2. Compare the header of the incoming file against the predefined list. And.. thats it. Assumption is that, header would promptly ...

_________________
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54595
Location: Sydney, Australia
Points: 296053

Post Posted: Wed Jul 03, 2019 10:21 pm Reply with quote    Back to top    

Sending threatening messages to the providers works wonders.

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours