I have a requirement as below:
I will be receiving a flat file which can come with 10 fields at one time and for next instance it can come with 12 fields,later with 15 or 5 fields.... similar to this. the fields and their corresponding datatypes also can differ, i.e. each time the file can be received with different set of columns.
The script should create a table structure on it's own based on the file format and the data should be loaded. Is this possible?
Any idea in what scenarios such a requirement comes? Appreciate if any one can suggest the best possible solution.
imho there's no reliable solution for this scenario, loading totally unknown data.
What are you going to do with that table afterwards?
Teradata Studio supports loading files containing delimited data, trying to find the correct datatypes (similar to Excel), create a matching table and then load it.
Yes, I too asked business the same question about what they are going to do after loading.. :-) They are yet to come back.
In the meanwhile can you please let me know few points related to "Terdata Studio" which u mentioned in the above post? I have heard about this, but do not know any info as I have not worked on it.
Thanks in Advance!
for Studio check the articles by Francine Grimmer:
Another alternative is to store some of the incoming record as a varchar and leave it up to the users to parse out the varying data at query time using the regexp functions. For instance if the first 10 columns are well known and then there are zero to 10 more randomly varying columns you can model the 10 that you know and put the rest into a varchar for the users to parse out as needed. This becomes even more effective if the arriving data is in name value pair form and can be parsed with the NVP function. Or even better than that if it arrives in or can be made into JSON form.
Regarding the requirement which I posted earlier, I got some more additional clarification from business-
All the columns will be of VARCHAR (50) type. It is going to be a one-time load. However, number of columns & column names for each file will differ. we should be creating a separate table for each file and the data should be loaded to the respective columns.
Now can you suggest any solution?
Appreciate any help in this regard.
if it's a onetime load you can load the files one after the other using Studio.
Open the "Data Transfer" perspective and navigate to your target database, right click on Tables -> Teradata -> Data Transfer and choose "External File (Smart Load)"