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 multiple XML files at once

Hi,

I have about 300-400 XML files I want to load in my SQL database (2005). The following code will load one (1) file. How do i do a mulitple collections?

INSERT INTO MEL (DATA)SELECT *FROM OPENROWSET (BULK
'C:\Temp\CHAPTER1.xml', SINGLE_BLOB)AS TEMP

Thanks,

http://www.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp

See this article.. i think ur schema should be same for each file...

|||

Thank you for the help. This script runs in VBS, how do I do this in VB.NET or from with SQL (stored procedure)? Also I am looking to import the XML as RAW XML. My XML files are large and complex and I want to store them in a table with TYPE of the field "XML".

Thanks Again,

Bones

|||

Example to use the below stored proc

-- Listing 2

CREATE TABLE #Files (MyFile varchar(200))

CREATE TABLE #Lines (MyLine varchar(8000))

DECLARE @.MyFile varchar(200), @.SQL varchar(2000), @.Path varchar(400)

SET @.Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\'

EXECUTE sp_ListFiles @.Path,'#Files','%.txt',NULL,0

SELECT @.MyFile = MyFile FROM #Files WHERE MyFile LIKE 'README%'

SET @.SQL = 'BULK INSERT #Lines FROM ' + CHAR(39) + @.Path + @.MyFile + CHAR(39)

EXECUTE (@.SQL)

SELECT * FROM #Lines

DROP TABLE #Files

DROP TABLE #Lines

1---------------------23StoredProcedure:sp_ListFiles45---------------------6789USE master10GO11CREATE PROCEDURE dbo.sp_ListFiles12 @.PCWritevarchar(2000),13 @.DBTablevarchar(100)=NULL,14 @.PCIntravarchar(100)=NULL,15 @.PCExtravarchar(100)=NULL,16 @.DBUltrabit = 017AS1819SET NOCOUNT ON2021DECLARE @.Return int22DECLARE @.Retainint23DECLARE @.Statusint2425SET @.Status = 02627DECLARE @.Taskvarchar(2000)2829DECLARE @.Work varchar(2000)3031DECLARE @.Wishvarchar(2000)3233SET @.Work ='DIR ' +'"' + @.PCWrite +'"'3435CREATE TABLE #DBAZ (Name varchar(400),Work int IDENTITY(1,1))3637INSERT #DBAZEXECUTE @.Return = master.dbo.xp_cmdshell @.Work3839SET @.Retain =@.@.ERROR4041IF @.Status = 0SET @.Status = @.Retain42IF @.Status = 0SET @.Status = @.Return4344IF (SELECTCOUNT(*)FROM #DBAZ) < 44546BEGIN4748 SELECT @.Wish =Name FROM #DBAZWHERE Work = 14950IF @.WishISNULL5152BEGIN5354 RAISERROR ('General error [%d]',16,1,@.Status)5556END5758 ELSE5960 BEGIN6162 RAISERROR (@.Wish,16,1)6364END6566 END6768ELSE6970 BEGIN7172 DELETE #DBAZWHEREISDATE(SUBSTRING(Name,1,10)) = 0ORSUBSTRING(Name,40,1) ='.'ORNameLIKE'%.lnk'7374IF @.DBTableISNULL7576BEGIN7778 SELECTSUBSTRING(Name,40,100)AS Files79FROM #DBAZ80WHERE 0 = 081AND (@.DBUltra = 0ORNameLIKE'%<DIR>%')82AND (@.DBUltra != 0ORNameNOT LIKE'%<DIR>%')83AND (@.PCIntraISNULL ORSUBSTRING(Name,40,100)LIKE @.PCIntra)84AND (@.PCExtraISNULL ORSUBSTRING(Name,40,100)NOT LIKE @.PCExtra)85ORDER BY 18687END8889 ELSE9091 BEGIN9293 SET @.Task =' INSERT ' +REPLACE(@.DBTable,CHAR(32),CHAR(95))94 +' SELECT SUBSTRING(Name,40,100) AS Files'95 +' FROM #DBAZ'96 +' WHERE 0 = 0'97 +CASEWHEN @.DBUltra = 0THEN''ELSE' AND Name LIKE ' +CHAR(39) +'%<DIR>%' +CHAR(39)END98 +CASEWHEN @.DBUltra != 0THEN''ELSE' AND Name NOT LIKE ' +CHAR(39) +'%<DIR>%' +CHAR(39)END99 +CASEWHEN @.PCIntraISNULLTHEN''ELSE' AND SUBSTRING(Name,40,100) LIKE ' +CHAR(39) + @.PCIntra +CHAR(39)END100 +CASEWHEN @.PCExtraISNULLTHEN''ELSE' AND SUBSTRING(Name,40,100) NOT LIKE ' +CHAR(39) + @.PCExtra +CHAR(39)END101 +' ORDER BY 1'102103IF @.Status = 0EXECUTE (@.Task)SET @.Return =@.@.ERROR104105IF @.Status = 0SET @.Status = @.Return106107 END108109 END110111DROP TABLE #DBAZ112113SET NOCOUNT OFF114115RETURN (@.Status)116117GO118119-- Listing 2120

The above proc will return the list of files in a folder. Use the resulSet of this proc and then run a a cursor or loop to execute your procedure

|||

Thanks Satya

You've been very helpful.

Bones

Import multiple txt files

I have 8GB of text files which are basically log files from the past few years.
There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder).
It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )

Surely there is a way to mass import without all the costly applications that google searches give me?
cheers :PUse a DTS package implementing looping constructs with ActiveX scripts to loop through each folder and import the data contained in each file. There are some basic examples of how to do this on SQLDTS.com

Regards
Lempster|||If you are using SQL Server 2005 then you can use Integration service.
SSIS provides number of features to perform variety of tasks. You can use for loop container to import multiple text files at a time.|||I would use bcp and a cmd shell script for this.|||I would use bcp and a cmd shell script for this.

I would probably wrap a DTSRun in a cmd shell script (with a for/do) and pass in whatever needed input variables (date, basefilename, etc.), I just wish cmd had good "native" equivalents of grep and sed that exist in UNIX. I've used type | find, type | findstr but it gets a bit clumsy.|||Lost of ideas for me to look at here hehe, thanks a lot

import multiple text files?

Hello,

I am kind of new to Sql Server 2005.

I figured out how to use the import data wizard to import a delimited text file.

But I need to find a way to import many delimited text files at once.

does anybody know if this can be done in Sql Server 2005? and how?
thanks in advance,Hi,

you should probably use integration services for that. SSIS has a special task for that which will loop though a directory and inmport all text files which fit into the filter specified before.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Import multiple text files into Single table

How to import multiple text files (residing in single folder) into SQL Server table? I know how to import single file but not sure how multiple files could be loaded? Pls. guide.

Thanks,

HShah

I am assuming that thefiles all have the same structure.

There are a number of options availableto you. See here:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

sql

Import multiple csv into multiple tables

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecietedChicagoboy27 (jeremy.bird@.gmail.com) writes:

Quote:

Originally Posted by

Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted

You could use BCP or BULK INSERT, but it appears that you would have to
add quite some control code on top that.

A better alternative could be to turn to SQL Server Integration Services,
which is what the Import Wizard uses. Unfortunately, though, I am
completely unexperienced myself with SSIS, so I cannot assist further.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

import multiple accessfiles in dts

Hi,

How can i import multiple accessfiles in sql server 2000 by using a dts
package?

The dts package should import files with the most current date in a
directory (not today's date). The date can be found in the name, e.g.:
<companyname>_20041214.mdb.

Also i would like to use the companyname from the filename to fill in a
empty column called companyname, so i can import all files into one
normalized table.

The files are placed in on a root drive.

If someone can help me on this i would be very happy.

Thnx.Hi

The following should get you started:
http://www.sqldts.com/default.aspx?292
http://www.sqldts.com/default.aspx?248
http://www.sqldts.com/default.aspx?201

You can store the company name in a global variable, then use an activeX
transform to populate a column from that global variable.
http://www.sqldts.com/default.aspx?279,4

John

"Ezekil" <ezekil@.lycos.com> wrote in message
news:41c281c0$0$45956$a344fe98@.news.wanadoo.nl...
> Hi,
> How can i import multiple accessfiles in sql server 2000 by using a dts
> package?
> The dts package should import files with the most current date in a
> directory (not today's date). The date can be found in the name, e.g.:
> <companyname>_20041214.mdb.
> Also i would like to use the companyname from the filename to fill in a
> empty column called companyname, so i can import all files into one
> normalized table.
> The files are placed in on a root drive.
> If someone can help me on this i would be very happy.
> Thnx.

Import Excel to SQL Server 2000

I need to import some excel files in the Database via DTS. The problem is, that I don't know the names of the Excel Sheets (there are multiple sheets in the excel file). Is it possible to get the names of those sheets? (via activeX or SQL Query or anything, but from a DTS package)
You might want to ask this in the DTS news group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Import and Export Wizard: transferring multiple tables from SQL Server 2005 to SQL Server 2000

Hi!

I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.

I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)

Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.

Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?

This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.

So now I'm trying to understand why the Wizard created this more-complex package.

Any help will be appreciated, including references to non-Microsoft books/websites/etc.

Thanks in advance.

Dan

Hi Dan,

you can also have a package with 50 parallel data flows built if you uncheck the "Optimize for Many Tables" checkbox. That might be easier for you to edit. This solution does not scale too well with "really" too many tables so we had to emloy the complex package you are referring to. The metadata is stored in the XML file you mentioned and the transfer task goes through that file and generates simple data flows on the fly and executes them one by one.

HTH.

|||

Bob,

Thanks.

I also now know how to get the "50 parallel data flows" that is easier to edit.

I don't understand, though, how the metadata in the XML would "tag along" if I were to save the SSIS package to the File System and copy it to a network drive, where others could use it.

Dan

|||

You would need to copy all the associated files (like the XML with metadata) and tweak the connections to point to the new location.

Thanks.

|||

Bob,

Thanks again.

So it seems the Wizard creates an SSIS package, and also whatever files are needed to support that package, e.g., the XML file with the table names and structures (if a CREATE is necessary). The user of the Wizard must be smart enough to realize that anything in the Connection Manager must accompany the SSIS package -- and that such "temporary folder" files are essential to the task (so they should not be deleted by any "housekeeping" effort on the PC). That's good to know.

I would have expected such files to end up in someplace like the BIN folder found in the same folder where the SSIS package is created. If it isn't a long explanation, maybe you might share why the development team did not use the BIN folder for such files. (I am not wanting to be "nasty" -- just curious about how such decisions are made by the development team.)

Dan

|||

Hi Dan,

the transfer tables task was not initially designed to be used by the wizard. It was built by SMO team to allow copying tables using their APIs. When used that way the internal package is invisible and it makes sense to put the additional files to the temp folders. Later, we realized it might be suitable for our purpose. We weren't sure how often our users would actually want to preserve this package.

We realized it is a problem now and found some additional issues with the table transfer provider task, so we are looking into simplifying the wizard generated packages for Katmai.

Thanks,

-Bob

|||

Bob,

Thanks for the response. I truly appreciate the help the Wizard has given me on many occasions.

I have learned a lot from the Wizard, using it to convert my SQL Server 2000 DTS Packages to SSIS, and learning from the packages it created.

Since you mentioned some new features, is there any current plan to enhance the Execute Package Utility so that it can accommodate the movement of data from MS Access 2003 SP2 to SQL Server 2005? I can do that when I use Visual Studio 2005, but when I try to execute the package with the Execute Package Utility I get many errors of the form

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).

The only data conversion I perform is double-byte characters to single-byte characters.

(Maybe I should ask this in a separate thread?)

Dan

|||

Are trying to run the package on the same machine it worked from the designer? What edition of the product you have installed?

It does not seem like new features are needed for this. You just need a proper edition of the product on the machine where you run the package.

Thanks.

|||

Hi Bob,

I'm having a similar issue as Dan, with two notable differences. First, I have over 300 tables to transfer and Second, most of the tables have identity columns.

When I went through the wizard, with "Optimize for Many Tables" checked, it ignored the adjustments to accept the identity values and created new values. Is there a way to adjust this script to accept the Identity values without setting up 300+ parallel data flows? The components are identical to what Dan described in the first posting.

If this can't be done, other suggestions are welcome.

Thanks - Gary

|||

Hi Gary,

unfortunately you have hit another issue with the transfer tables task I was referring to in the previous post. Currently, it is not possible to pass the identitty column settings when the "Optimize for many tables" option is selected.

The best workaround I can offer is to copy your tables in multiple batches (50 tables each should work, it might take up to 100 depending on your hardwear but you would need to test it) with unchecked "Optimize for many tables" option.

Thanks.

|||

Bob,

Thanks for your reply.

Yes, I am trying to run the package from the same machine it worked from the designer. But I am trying to run a "file system" copy of the package that I placed on the network drive. I am out of the office at the moment, so I cannot try running the exact copy on my PC hard drive -- but I will be back in the office in a few days, and can try doing so at that time.

We upgraded to SP2 a month or two ago, for SQL Server 2005. Did you need more "edition" information? If so, I will provide it on Friday, or so.

I just figured it (transfer from Access, and convert Access tables with single-byte characters to double-byte characters, as seem to be usual for SSIS input, then back to single-byte characters for placement in the SQL Server 2005 tables) was a capability that went beyond the intent of the "standalone" package runner (outside Visual Studio), Execute Package Utility.

I am pleased to learn that I may not need to use Visual Studio 2005 to perform this movement of data from Access to SQL Server 2005.

Dan

|||

Bob,

I am back at my desk, where I tried to run the SSIS package that moves approx. 50 tables from MS Access to SQL Server 2005.

The Access version is 2003 (11.6566.8132) SP2.

The SQL Server version is 9.0.3042

The only version information I see in the About box for "About DTExecUI" is "Version: 1.0". Is there some other place I should be seeking version information for this product?

Dan

|||

Dan,

I was asking about the edition of your SQL server instalation; is it Developer, Standard or Enterprise edition?

Thanks.

|||

Bob,

We have the Enterprise edition of SQL Server 2005 in the environment where I am trying to perform the task.

Dan

|||

Have you installed the entire SSIS module on all of those machines as well?

Thanks,

Bob

Implicit transaction for a page request?

If I execute multiple SqlCommands in a single .aspx, using a single SqlConnection, are all the commands part of the same transaction?

That is, if I say:

cmd1.CommandText = "UPDATE foo SET myVal = myVal - 1"
cmd2.CommandText = "SELECT myVal FROM foo"
cmd1.Connection = myConn
cmd2.Connection = myConn
myConn.Open()
cmd1.ExecuteNonQuery() ' decrement myVal
intResult = CInt(cmd2.ExecuteScalar()) ' select value of myVal
myConn.Close()

Do the UPDATE and the SELECT happen in the same transaction, or is it possible that someone else would have changed the value of myVal between these two calls?Nope. Each statement has an implicit transaction but to put them both under a single transaction you have to use an explicit transaction.

So yes, data could have changed between the statements.

Don|||Thanks for the clarification. So if I understand you right, this:

cmd.CommandText = "UPDATE ... ; SELECT @.myResult = ... "
cmd.ExecuteNonQuery()

would put them in the same transaction, which is what I want.|||Now I'm on shakier ground. I'm pretty sure that SQL Server still considers them to be separate statements, simply batched together. But not absolutely positive.

Any reason you don't want to just use an explicit transaction?

Don|||No -- I'd like to use an explicit transaction, but I'm not sure how. Can you give me an example of how to do it w/o moving the queries to a stored procedure?

Thanks!|||Well, you're opening yourself up to all kinds of security holes by using dynamic SQL like this. Stored procedures are much better way to go.

But you can either use transactions via dynamic SQL and batch statements, or using the SQLTransaction class in ADO.NET. The example in the .NET docs is pretty clear. For the latter, just new up a SQLTransaction object and use the connection object to begin the transaction. Then call either the Commit or Rollback methods of the transaction object.

But remember that your app won't be very secure unless you take all the steps to close the holes.

Don|||Thanks, Don. What kind of hacks am I risking? I'm using parameters to avoid sql-insertion attacks; are there other risks that come with using SqlCommands?|||Ah, you're using ADO.NET parameters? That wasn't clear from the snippets you've posted, although you had one @.Result showing. You're using parameters to provide the changeable values in the SQL, right?

If that's the case, you're probaly reasonably well protected from SQL injection, since ADO.NET passes the SQL and parameters separately to SQL Server, where they are inserted into the SQL.

Don