August 22, 2006 at 8:33 am
Hello!
I have several .dbf files. Each file is a table that has data for a day.
I need to join all these files into one. Anybody knows how to do it?
Thanks
CLaudia
Cláudia Rego
www.footballbesttips.com
August 22, 2006 at 9:04 am
Claudia
What is a .dbf file? Is this in Access, or SQL Server? If it's SQL Server, are all the files in the same filegroup, or is each one in its own filegroup? What do you mean by "has data for a day"? If you can post the DDL for your database, that would be helpful.
John
August 22, 2006 at 9:32 am
I have several files dbf -> dBase 5.
For today (22 aug 2006) i have a file called 20060822AI.dbf, for yesterday i have a file called 20060821AI.dbf, etc.
All files have the same structure.
With DTS i joined several files into only one table in a sql server database. But every time i have to indicate the source file.
I need some automatic way of doing this because i have a lot of files!!!!
Claudia
Cláudia Rego
www.footballbesttips.com
August 22, 2006 at 9:38 am
Claudia
Sorry, I don't know anything about dBase 5. You'll probably have to write something in VB or ActiveX for your DTS package that generates the file name according to the date.
John
August 22, 2006 at 9:46 am
I'm sure the solution is passing by there.. some VB code.
Claudia
Cláudia Rego
www.footballbesttips.com
August 22, 2006 at 1:24 pm
How is this to be used: Is this a one-time task or something you'll run regularly? Will it always be a fixed number of files, ie: the past five days? After the initial run, will you always need to grab multiple files, or will you just be appending the current day to the mssql table?
August 22, 2006 at 1:33 pm
I have done something similar. You can use DTS and append to the table.
The way I achieved this was to create a global variable. Use some VBscript to grab and format the name and save that to the global variable. You'll also need a textfile connection and it is this datasource that you need to assign the filename to in your VBScript.
This way it will pick up the correct file each day and append to your table in SQL Server.
August 23, 2006 at 7:11 am
Can you post your vb code?
Thanks Claudia
Cláudia Rego
www.footballbesttips.com
August 23, 2006 at 7:31 am
If you have a global variable set up (ours is called InputFile) for the input file name, then the code could be something like....
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim Dy
Dim Mth
Dim Yr
Dy = Right(Day(Date) + 100, 2)
Mth = Right(Month(Date) + 100, 2)
Yr = year(Date)
DTSGlobalVariables("InputFilename")="CreditFile"& yr & mth & dy & ".csv"
Main = DTSTaskExecResult_Success
End Function
This renames the filename, so when it is applied as the datasource of the text file connection, it will pick up the current day each day the DTS is fired.
August 28, 2006 at 3:24 pm
Hi Clive.
I don't know if i understood ok...
In your case, do you have to indicate the file name or is DTS it looks automatically?
Because i have a paste with a big number of files and i want to automatically get the file name.
I don't want to every time before executing the package indicate the file name.
Cláudia Rego
www.footballbesttips.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply