Data import

16 May 2006 8:33 AM

SecAnalyst

I've inherited an interesting one :-

An application currently produces about 150 .csv formatted reports that
I need to import into a SQL database to run reports, etc on. Simple
enough but here's the kicker :-

The applications works as follows :-
All the data is stored in a single .dat file until midnight then the
..dat file is processed and the data seperated into the various .csv
files.
The application automatically creates a directory to save the data
into, in the format yyyymmdd, - so each day I have a new folder that
contains the data that I need to work with.

What I need to do is create a script that will automatically goto the
relevant folder & then process the data. I've done the revelvant
processing part of the script but I'm stumped with regard to howto
change the source directory automatically to reflect the date in the
format above.

Any ideas will be very welcome.

19 May 2006 1:07 AM

MeanOldDBA

Store your file names into a table. Create a string variable @filename.
When you set the variable, set it to be the table.column + yyyymmdd. You can
use SELECT CONVERT(VARCHAR,GETDATE(),112) to get the yyyymmdd format.

--
MeanOldDBA
derricklegg***@hotmail.com
http://weblogs.sqlteam.com/derrickl

When life gives you a lemon, fire the DBA.

Show quote

"SecAnalyst" wrote:

> I've inherited an interesting one :-
>
> An application currently produces about 150 .csv formatted reports that
> I need to import into a SQL database to run reports, etc on. Simple
> enough but here's the kicker :-
>
> The applications works as follows :-
> All the data is stored in a single .dat file until midnight then the
> ..dat file is processed and the data seperated into the various .csv
> files.
> The application automatically creates a directory to save the data
> into, in the format yyyymmdd, - so each day I have a new folder that
> contains the data that I need to work with.
>
> What I need to do is create a script that will automatically goto the
> relevant folder & then process the data. I've done the revelvant
> processing part of the script but I'm stumped with regard to howto
> change the source directory automatically to reflect the date in the
> format above.
>
> Any ideas will be very welcome.
>
>