import file question

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

thanks a lot, i appreciate your help!

Here you go...

Code Snippet

Create Table #Files

(

CSVFilevarchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

|||

it returns the result like this:

Output

The system cannoot find the filespecified

null

can someone lese help...

|||

You have to give the proper path.

The example shows the sample path...

You can make select query against the temp table to verify all the files are listed ...

import data question (reask)

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

some one replyed me with the following solution

Create Table #Files

(

CSVFile varchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

but it doesn't work. can someone elaborate more on it and tell me how's it gonna work?

Having seen the error message in your other message, it sounds like the account under which your SQL Server instance's service is running doesn't have the appropriate permissions on the folder and/or CSV files.

Chris