November 29, 2006 at 10:57 am
Hello,
We usually receive a text report everyday with only one line that has total data for transactions made by our clients. We we would like to have it imported into our SQL server 2000 database. The problem is that the report doesn't have the same columns every day.
Example:
On MONDAY the report might have these columns:
Date | TotalUsers | Tickets | ISM Tickets | GH Shares | YT Fees
1Nov06 450 2602 789 456 519.5
On TUESDAY the report might have these columns:
Date | TotalUsers | Tickets | YT Fees
2Nov06 580 3520 865.4
Both the ISM Tickets and GH Shares columns are missing because clients didn't place a trade for those shares on that day.
My question is: Would in be possible to import these files into the DB and have DTS check if all the columns exists in the text file and put a NULL or a 0 on the columns values in the DB table if the columns are not in the text file?
At the end I would like the DB table to look like this:
Date | TotalUsers | Tickets | ISM Tickets | GH Shares | YT Fees
1Nov06 450 2602 789 456 519.5
2Nov06 580 3520 NULL NULL 865.4
I have been trying for the last week and I have been unable to make it work.
If it is not possible with DTS, is there another way to do it?
Thanks for you help
November 29, 2006 at 12:38 pm
Do they provide the same heading name every time they send you the file?
What kind of format do they send you the file ? ',' delimited or fixed length?
November 29, 2006 at 2:41 pm
The Columns have the same name every time.
The file is a csv file delimited by ,
Thanks !
November 29, 2006 at 11:58 pm
If it's just a heaser line and 1 data line, I would just use an ActiveX script (in a DTS package) to read in the two lines, split each line into arrays and build an "Insert ... Values" query.
November 30, 2006 at 7:56 am
yes, as long as there is a header line and it is a comma separated csv file, Robert Davis's solution is the one and it could be very simple.
But if there is no header in the file, you got look for a pattern in the data, which still needs a spec from the company who sends you the data. If we can't humanly identify, DTS cannot do anything.
hope you solved the problem by now.
November 30, 2006 at 9:15 am
I notice that the 'Date' is a common field in the Header line and that there is a common seperator, the pipe.
Here is a very general example of an attack on the problem.
In this case I'd split the Header line on the pipe and load the value, position and lenth of the resultant text into an array, i.e.
"Date | TotalUsers | Tickets | YT Fees" <---- Split
"2Nov06 580 3520 865.4" <---Next Line in file
Darray=split("Date | TotalUsers | Tickets | YT Fees","|")
for cnt=0 to ubound(Darray)
A[cnt][Label]=Darray[cnt] --Label "Date,TotalUsers ect.."
A[cnt][Position]=instr(Darray[cnt],"Date | TotalUsers | Tickets | YT Fees ")
A[cnt][Length]=len(Darray[cnt]) ect..
next
--Afterwards, depending on how many lines follow the header, for each line
While not X -- The Stop Condition
DataLine= "NextLineInFile"
for cnt = 0 to ubound(Darray)
Data[x][A[cnt][Label]]=mid(DataLine,A[cnt][Position],A[cnt][Length])
next
wend
-- You can use a constant to adjust for leading spaces
December 1, 2006 at 11:10 pm
Would in be possible to import these files into the DB ?
It is possible. I hope you are using data pump task to load the data into the DB. What you may need to do is, open the flat file and read the first line/header line and parse it for columns name in active script task before data pump task.
Assign the source and destination column names dynamically
for DataPumpTask Object within the active script.
for more info on object, check this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtsptasks_76es.asp
Hope this helps.
Regards,
JG
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply