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 MDF and LDF backup from SQL2000 to SQL2005

hi,

i have six files ( 3 MDF and 3 LDF) from a backup of a MS SQL 2000 server.

I installed MS SQL 2005 and wanted to Restore these Dataset. but they cant be accessed. doesnt SQL 2005 support these files ?

How can i use these files to access them with SQL 2005 ?

Thx,

Hello -

The files you're talking about are the "raw" data files that SQL Server uses, not a backup. When you take a backup from within SQL Server, files are acutally packaged a little differently, into a .bak file. When you use the BACKUP DATABASE command in SQL Server, it will create these files for you, which inlcude all of the "raw" files. You can then use the RESTORE DATABASE command to restore them to another (or the same) SQL Server.

All is not lost, however. If you have the MDF and LDF files, you can "adopt" these files directly into a running SQL Server system. That is called "attaching" a database. You can read more about that here:

http://msdn2.microsoft.com/en-us/library/ms190794.aspx

Buck Woody
http://www.buckwoody.com

Import HTML and Tab delimited into SQL

Hi,
I need to import files from either HTML or Tab delimited format into SQL
Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
the details. I'm able to standardize the filename and directory using code,
so that's not a problem. Any ideas?
HTML? I don't think so. Tab delimited, you bet.
Yes, you would set up a DTS packages using the
Import / Export wizard.
If you want to script this and run it, check out this
VBScript code sample:
http://www.eggheadcafe.com/articles/20030923.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C603F79A-3654-4785-B144-84909C69473E@.microsoft.com...
> Hi,
> I need to import files from either HTML or Tab delimited format into SQL
> Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
> the details. I'm able to standardize the filename and directory using
> code,
> so that's not a problem. Any ideas?

import hierarchical data

hi folks,

I have to import hierarchical text files like:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname
21;name;firstname;salutation;title;age;nickname
...

additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?

Iam looking forward for any helping idea.

rgrds,
matze

1. Use the Sort transform to remove dupes.

2. This is just a string manipulation problem right? There is a library of string manipulation functions available in the Derived Column transform.

-Jamie

|||

Thanks for reply Jamie,

First I cannot find any functionality to split the input column into multiple output columns in Derived Colmn Transform. Second how to get this in a pair of related tables ?

please help out

|||

Have you looked at the FINDSTRING and SUBSTRING functions?

When you say the tables are related do you mean there is a foreign key between them? if so you can use 1 f the methods defined here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

-Jamie

|||Hi Jamie,

Iam back on this and its driving me nuts.
You wont believe it, but Iam a absolute novice on SSIS ;)
So please let me explain more in detail and exercise paitience with me.

thats the schema:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname

32 identifies a city line, 21 identifies a person line

example:
32;england;london;2445;poolstreet 45
21;smith;joe;mr;;23;jo
21;henderson;paul;mr;doctor;54;paul
32;sweden;stockholm;4356;javanstreet 2
21;smith;joe;mr;;23;jo
32;england;london;2445;poolstreet 45
21;jhonson;pieter;mr;;33;pieter

let there be a table 'city' and a table 'person' with an m:n relatation table cityperson
I want to have a result as follows:
table city
1 england london 2445 poolstreet 45
2 sweden stockholm 4356 javanstreet 2

tabel person
1 smith joe mr 23 jo
2 henderson paul mr doctor 54 paul
3 jhonson pieter mr 33 pieter

table cityperson
1 1 1
2 1 2
3 1 3
4 2 1

whereas the first column idicates an identity value

What i did so far is reading my source file with flat file source into two columns (id, line) make a conditional split on id and make two derivied column transform. One for city lines and one for person lines. In these derived column transforms I split up the line column (eg. england;london;2445;poolstreet 45) with Findstring amd Substring into separate columns for each field. Now I have the two outputs a) all columns for a city and b) all columns for a person. What to do now ? How to get this into the tables three ?
By the way isnt it quite slow to extract the columns with Findstring and Substring form a line string ? I have to deal with files of nearly 30 megabytes. Is there a better way to accomplish this ?

|||

FINDSTRING() and SUBSTRING() in the Derived Column component are the best options and fastest available to you. I don't think you'll find a bottleneck there.

I understand that you now need a way of populating CityPerson. What I don't understand is how a relationship is defined in the source file between a city and a person.

In other words....how do you know by looking at the source file which city a person is in? Is it the City line that is immediately above it?

-Jamie

|||

Jamie,

>FINDSTRING() and SUBSTRING() in the Derived Column component are the best options and fastest available to you. I don't think you'll find a bottleneck there.

ok, that pacified me

>In other words....how do you know by looking at the source file which city a person is in? Is it the City line that is immediately above it?

Exactly. You got it, its defined by the city line above.
Not realy nice, I know. Therefore my problems keeping the relation.

again thanks for your support,
Matze

|||

OK, so there are probably a million and one ways to do this - but here's one method.

First, before the split, give each row an ID using the technique here: http://www.sqlis.com/default.aspx?37

Later in the pipeline, join the Person path back with the City path using a MERGE JOIN (you'll need a MULTICAST on the Person path). But only join where the City ID is less than the Person ID field. The output fields from the MERGE JOIN should be everything from the Person input and the ID from the City input.

The output from the MERGE JOIN should then become the input to a AGGREGATE transform. Inside there group by all the Person fields and select the maximum City ID. You will then have a record for each person along with the ID of the City field. It should then be pretty easy to constuct the CityPerson table.

Another way to describe the above is that you're building the following SQL statement:

SELECT p.*, MAX(c.ID) as CityID FROM Person p, INNER JOIN City c ON p.ID > c.ID GROUP BY p.*

where p & c are the inputs to the MERGE JOIN.

Hope that helps!!

-Jamie

|||

Jamie,

it's me the greenhorn again ;)
I have set up the ID for each row, but now become desperate on Merge Join.
>But only join where the City ID is less than the Person ID field.
I do not see a option to set up this in the Merge Join component. Seems to join only on ID equals ID.
Another issue is the requirement for piplines to be sorted. Do to the fact that the merge column is the ID and the ID is sorted, could I define this circumstance anywhere ?

thanks for your help again,
Matze

|||

Easy one first. On the output from the component where you add the IDs set IsSorted=TRUE and set SortKey=1 on the ID column. The data paths will then be sorted.

You're right about the MERGE JOIN tho. I admit I thought that you could do this and I didn't actually check it. My apologies for that. That's really bothered me actually - that's a real limitation of the component.

So, we need another solution. You can probably achieve this all in a single script component actually thinking about it. It will need to be an asynchronous component positioned before your conditional split but after your script component that adds the ID.

Loop over the rows. Each time you get a new City row store its ID in a local variable and add it to each Person row until you encounter the next City record.

It will need 2 outputs - 1 for City and 1 for Person

Here's a link that might help:

Difference between synch and asynch components: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx

Sorry I led you the wrong way on this. Hopefully this method should work.

-Jamie

|||

Hi Matze,

I've re-read this thread and realised I've led you on a bit of a merry dance. Sorry about that.

To try and make up for it I had a go at building this myself and have come up with something pretty simple that works fine. If you want to drop me a mail via the contact link on my blog homepage (address below) then I'll be able to send it to you.

-Jamie

|||

Hi,

I replied to the address from which your mail came (mail@.mhess.com) but got a mail delivery failure.

Do you wanna email me direct at jamie.thomson@.removethisbit.conchango.com

-Jamie

sql

Import from file

I am looking for a method to pull the data from several
files in the same directory into a SQL db. I can get DTS
to work for a single file but not multiple files. If
someone has an example, great, if not, where do I start?
Am I in the right place with DTS?
Thanks!
JamesSee if this helps: http://www.sqldts.com/default.aspx?6,103,246,0,1
--
Andrew J. Kelly
SQL Server MVP
"James Wood" <jgwood@.dot.state.az.us> wrote in message
news:09eb01c347f2$02d0c540$a001280a@.phx.gbl...
> I am looking for a method to pull the data from several
> files in the same directory into a SQL db. I can get DTS
> to work for a single file but not multiple files. If
> someone has an example, great, if not, where do I start?
> Am I in the right place with DTS?
> Thanks!
> James|||That is what I was looking for. I will give it a shot!
Thanks!
James
>--Original Message--
>See if this helps: http://www.sqldts.com/default.aspx?
6,103,246,0,1
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"James Wood" <jgwood@.dot.state.az.us> wrote in message
>news:09eb01c347f2$02d0c540$a001280a@.phx.gbl...
>> I am looking for a method to pull the data from several
>> files in the same directory into a SQL db. I can get
DTS
>> to work for a single file but not multiple files. If
>> someone has an example, great, if not, where do I
start?
>> Am I in the right place with DTS?
>> Thanks!
>> James
>
>.
>

import from binary file

I am importing data from binary data files into SQL Server. This is the code I am using:

Do While Not EOF(1)
Get #1, , NonStdCurrRecord
adoRS.AddNew
adoRS!Field1 = CDate(NonStdCurrRecord.Field1)
adoRS!Field2 = CSng(NonStdCurrRecord.Field2)
adoRS!Field3 = CSng(NonStdCurrRecord.Field3)
adoRS!Field4 = CSng(NonStdCurrRecord.Field4)
adoRS!Field5 = CSng(NonStdCurrRecord.Field5)
adoRS!Field6 = CSng(NonStdCurrRecord.Field6)
adoRS!Field7 = CSng(NonStdCurrRecord.Field7)
adoRS.Update
Loop

Unfortunately, it takes about 8 mins to import a file with 180k records. Is there a faster way to do this?

maybe bcp?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a4-0ce39c30ec38.htm
The bcp utility bulk copies data between an instance of Microsoft SQL Server 2005 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

Hope, it helps.

|||Thank you for the tip but I don't like requiring users to fiddle with command line utilities. This really has to be done programatically.|||

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/00d0311f-8b71-4ad6-824d-0e89119347a3.htm

maybe this link would be helpful?

Thanks

Import from Access - Datatype conversions

I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?

Thanks.

Kato

If you are using the import wizard then you can affect this with the mapping files at:

%PROGRAMFILES%\Microsoft SQL Server\90\DTS\MappingFiles

I'm guessing that the one you want is JetToSSIS.xml

If you are building packages manually then you can change this in the source adapter.

-Jamie

|||

Outstanding. Exactly what I was looking for.

Thank you.

sql

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.sql

Import Flat Files

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

Hosam Abd EL-Wahab wrote:

hi all

i using SSIS to import flat files and i need support

how can i import flat file from folder inculed many files and when finish start to next and next .....

if can i select from flat files to add condition

like Select * from.....where ......

thanks

You can run a foreach loop in your control flow to loop over every file...

No, you can't do a select * from flatfile where.... However, you can chose which columns to import and then you can use conditional splits to enforce your logic.|||

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

|||

Hosam Abd EL-Wahab wrote:

but if i want to some rows from files , the file include all territorys but i need to import only some .

how can i condition befor import to import only territory i want

thanks

You can't. Not with a flat file source. Sorry.|||

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

|||

Hosam Abd EL-Wahab wrote:

You can Reading Values from a Text File Using FileSystemObject

you can use that so usefull

http://msdn2.microsoft.com/zh-cn/library/aa933459(SQL.80).aspx

Right, if you want to code your own script. And FYI, that link is for DTS, not SSIS.

I would argue that simply loading a flat file and using a conditional split in the data flow will be extremely fast, almost such that trying to code something to exclude records up front would not be warranted.

import fils log

Hi,
Is there a simple way to get files "date modified" into
table, any xp_sp?.
the only solution I found is to use parsing on "dir"
command whith the cmdshell.
The motivation is to check data files downlowded every
day are to be changed.
Thanks,
HadarHi
If you are loading through DTS then look at
http://www.sqldts.com/default.aspx?292
You can also use xp_getfiledetails but it is an undocumented procedure.
John
"Hadar" wrote:

> Hi,
> Is there a simple way to get files "date modified" into
> table, any xp_sp?.
> the only solution I found is to use parsing on "dir"
> command whith the cmdshell.
> The motivation is to check data files downlowded every
> day are to be changed.
> Thanks,
> Hadar
>
>sql

Import Files Do Not Exist

I have a job that calls about 5 DTS's. Each DTS imports a text file. The problem is that some of the files may not be there every day. How do I exit the DTS without an error if the file does not exist, but I want my job to keep running...how about using an activex task to check for file and if file not found then Main = DTSTaskExecResult_Success, each of this task will then trigger the respective DTS package.

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 excel files

how can I import excel files into msde 2000
Hi ,
MSDE does not support DTS and other advanced features supported by the
regular versions of sql server.
But a work around is to get a evaluation CD of sql server and then install
the client components of sql server . Once it is installed sucessfully you
can use the import export wizard to export data into the msde instance.
Hopefully this answers your question
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qa413mkh5j0c5@.corp.supernews.com
> how can I import excel files into msde 2000
you can set up a linked server to the xls file and get relevant data into
your MSDE table...
consider having a d:\Students.xls file with 2 columns, FirstName and
LastName ...
SET NOCOUNT ON
CREATE TABLE dbo.Students (
FirstName VARCHAR(10) ,
LastName VARCHAR(10)
)
GO
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Students.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT * FROM ExcelSource...[Foglio1$] -- that's [Sheet1$] for English
version :D
-- import into your base table...
INSERT INTO dbo.Students SELECT * FROM ExcelSource...[Foglio1$] -- that's
[Sheet1$] for English version :D
SELECT * FROM dbo.Students
GO
-- final clean up, dropping the linked server and the base table
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
DROP TABLE dbo.Students
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||How do you create the 'ExcelSource'
|||hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qhkoi9ittcl93@.corp.supernews.com
> How do you create the 'ExcelSource'
the 'ExcelSource' just defines a linked server, pointing to the file system
specified Excel file, using the JET OLE DB provider...
but perhaps I do not understand your question
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply