import of multiple CSV files in one MS SQL table

Hi all,

I have de following application to do :

I receive several .csv files from another application in a determined folder
of my PC.

Those files are named with the format log1.csv logs2.csv logs...
The number of file is variable but the internal format is always : time_sec;level
So the files content a field that may be used as unique key in the target database.

I'm trying to build a DTS package that should import periodically
all the CSV's present in the folder and then destroy them if done
successfully.

Apparently its not so simple than I supposed. I have always to give the name
of the table I want to import.

any idea?"Laurent" <autplc@.hotmail.com> wrote in message
news:e8bac87e.0411100552.5af177ca@.posting.google.c om...
> I receive several .csv files from another application in a determined
folder
> of my PC.
> Those files are named with the format log1.csv logs2.csv logs...
> The number of file is variable but the internal format is always :
time_sec;level
> So the files content a field that may be used as unique key in the target
database.
>
> I'm trying to build a DTS package that should import periodically
> all the CSV's present in the folder and then destroy them if done
> successfully.

Two Options:

Option 1:
Rename the file to a temporary name (in an ActiveX task) and use the
temporary name for the Data Pump

Option 2:
Use a Dynamic Properties Task to change the Data Source Name in the Data
Pump Task.

Regards,
Jim

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 Error

I receive unhelpful errors from attempting to import a ms access table into sql server 2005 using the import wizard. Here is the report.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)

Messages

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (73).
(SQL Server Import and Export Wizard)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [Company].[dbo].[EMPLOYEE] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

I received this type of error once before and got around it by removing all constraints from the table. I was able to import the table when the data types were nvarchar. I changed the data types to varchar and this occurred. How do I know what the message is specifically referring to or can I see more details of the import command. This is my first posting.

Charley,

Data conversion transformation requires SQL Server enterprise edition to be executed. see if this link provides more information

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=594086&SiteID=1

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