Loading data from multiple text files, with different number of columns to 1 big table

Question

I have 30k+ text files with different number of columns, space delimited, and task is to load data, columns wise, into 1 big oracle table. Details & possible approaches are below:

Text File 1-

Date Time SiteName CompName ipAdd
jkl mno pqr stu vwx

Text File 2-

Date CompName ipAdd
abc def ghi

Text File 3-

Date SiteName 
mno pqr

Expected Oracle Table-

Date Time SiteName CompName ipAdd
jkl mno pqr stu vwx
abc         def ghi
mno     pqr

Now, as you can see, there are different number of 'same' columns spread across multiple text files. And I have to load data into 1 big oracle table.

PLEASE NOTE:

In all text files, the first column is always 'Date', and the columns occur in an order, i.e., from Date to Time..or Date to CompName... (an increasing order from Date onwards)

The problem is that I could not find a way to use SQL Loader in a way that it can differentiate between 2 columns, and 15 columns in 2 different text files, even when I have their name in 1st line of text files, and then load them in a big table.

Approaches:

• Combine all text files in to 1 big text file and do something - FAILED
• Tried to put a delimiter, for max number of columns that can be out of all the text files (25 columns), and do something - COULD NOT
• Load 35k+ text files in 35k+ oracle tables, and then sit and combine these 35k+ oracle tables into 1 big table - RECOMMENDED BY PPL AROUND ME, BUT IT WAS TOO LONG, MAYBE SPACE & TIME/EFFORT INEFFICIENT SO I SEARCH FOR A REAL ALTERNATIVE

Please give your best approach. :-(

Answer 1

Assuming your posted examples are accurate and the first line of each file is the column headings here is the general approach I would take. (If the first line of each file is not the column headings I think you're pretty much stuffed).

This will all be a lot easier on *nix than in Windows, although I would hope Powershell can handle these tasks (I'm not a Powershell person).

1. Write a shell script to read the first line of each file and write it to a list file as well as the file name.
2. Sort the contents of that list file by all the columns. This will group all the file names by columns. You need to do this because knowing the number of columns isn't enough: I presume some files with three columns will have Date Time SiteName and others will have Date Time ipAdd
3. I suggest you label each group in the list file. This will be sufficiently tedious that it will be worth automating with a text processor, although right now I'm not sure how I would do that.
4. You will need to write a loader file for each permutation of columns identified in the previous stages.
5. Then you need to submit each text file to the loader with the appropriate control file. This is why labelling each filename with its group is a good idea: it will make it easier to automate this stage.

As an aside, it would be simpler to use external tables rather than SQL Loader. The DDL for an external table is easier to write than a Loader control file and you can use SQL to query them. Basically, external tables are staging tables, but with the data in OS files instead of in the dataabase.

Note that you don't need 35,000 staging tables. You just need one for each distinct combination of columns acosss all your text files. Of course, given that you have 25 columns in your target table the possible number of permutations is huuuuuge but let's assume it's actually a manageable number.

Answer 2

Load each one into a staging table that matches the file's structure. Then use sql code to clean up teh data and move it to the final production table.

Alternative, return the files to the people who are sending them to yhou and refuse to accept them inutl they conform to a standard file spec.