Import Of Multiple CSV Files In One MS SQL Table
Jul 20, 2005
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?
View 1 Replies
ADVERTISEMENT
Jan 21, 2005
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 = "")'
--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-02C$MyImport', '*.csv', 'Account'
I am getting the following error:
Could not bulk insert because file '\kb-02C$MyImportAccess 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.
K006B
View 2 Replies
View Related
Sep 23, 2014
I have over 600+ Excel .xlsx file that I have been trying to import to Sql database table. I've been trying to complete this task with SSIS but no luck yet. I have seen several videos and read articles but when I run the package the source is validated but I always get an error in the destination. I am using Excel 2010 and SQL Server 2012.
View 3 Replies
View Related
Jan 29, 2007
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
View 1 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Jul 17, 2007
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:TempCHAPTER1.xml', SINGLE_BLOB) AS TEMP Thanks,
View 4 Replies
View Related
Sep 3, 2007
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 :P
View 5 Replies
View Related
Nov 4, 2014
I have a script which imports the contents of a csv file from our CRM system and updates a table in my database. This works OK but the problems I have are that a) sometimes there is more than one file in the folder, and b) that I wish to move any csv files that have been imported into an archive folder. The csv files arrive with a time/datestamp and I currently rename them manually to FREXPORT before importing (the name is in the format FREXPORT_20141101_1217.csv).How do I:
1) get it to process the file without me having to manually rename the file(s) each time,
2) if there is more than 1 file in the folder process all the files and 3)move the correctly processed files to an archive folder which is: importarchive?
Ultimately, I would like the script to be run as a scheduled job, so it also has to deal with the fact that sometimes there will be no files to import too.
[code]create table #import
(worknumber nvarchar(12), date_done smalldatetime)
BULK
Insert #import
from 'fork04-hq-dc01dataimportFREXPORT.csv'
[code]....
View 2 Replies
View Related
Dec 7, 2006
Hi all,New to SQL Server - trying to create an SSIS package that will look forand import a series of Visual Foxpro tables (.DBFs) when they appear ina folder.The tables are/can be all different fields, field widths, etc. Withquite a bit of overlap though.The end result should be table "ABC.DBF" is pulled into SQL Server astable "ABC"Using: SQL Server 2005 Enterprise, SSIS, *latest* version of VFPOLEDBdownloaded from MSI have set up a package and tested it with several different tables andit works great - but I have to redo the data source and destinationeach time...I need to get this to be a somewhat automated process, pulling in all..DBFs no matter what they contain.Can I do this with SSIS alone (and variable substitution) or do I needto write a bunch of code...Thanks very much for your time and thoughts...
View 1 Replies
View Related
Jan 28, 2008
I am building a ssis package that imports multiple xml files containing data into the tables using one xsd file. I am using xml source task for this. I can only import one file as the primary key constraint gets violated.
I have four tables with four primary keys. The xml file does not have the primary key column data. So every time these columns get populated as 1, 2, 3, 4.
I am preety new to xml, so was wondering if anyone can help?
Why doesn't the xml file have primary key column data?
View 1 Replies
View Related
Feb 25, 2008
Hi,
I need to import around 200 excel file data into one table. Is there a way of doing this using SSIS or DTS? I know how to import single excel file into table but i need to automate this process for many files. All help appreciated
Thanks,
HEMAL
View 4 Replies
View Related
Jul 20, 2006
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,
View 1 Replies
View Related
Apr 23, 2015
Using sql server management studio I want to do 2 things.
- Import all txt files from a directory as tables into my DB.
- Add an additional field to each new table which contains the table name.
View 10 Replies
View Related
Feb 12, 2008
I am learning SQLServer Integration Services.
I created a file People.txt containing firstName, LastName seperated by a pipe.
------------------content-----------
John | Doe
Mike | James
Adam | Smith
-----------------------------------------
and another one called gender.txt
------------------content-----------
M
---------------------------------------
I will would like to create integration services package that compines each record of the first file with the record of the second file and inserts the result into table.
--------------Result table content------------------
John
Doe
M
Mike
James
M
Adam
Smith
M
-----------------------------------------------------------
Thanks
View 5 Replies
View Related
Nov 12, 2015
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
View 21 Replies
View Related
Feb 10, 2014
I need to import data to a MSSql table from massive (read: a million and a half rows, every single day) logs that come in .txt format separated in tabs with a ";" symbol and then have some stored procedures analyze that data to generate some reports in an excel file with that info. The text files include the column headers in the first row and the data starts on the second one.
The challenge is that the text files differ in column order and count every single day.
The analysis that I need to do only needs about 15 columns from the nearly 90-120 that those files include, and those columns sadly happen to be in a different order in those files.
View 8 Replies
View Related
Feb 18, 2014
I am having with trying to import XLSX files into SQL 2012 64 Bit.
I have installed the Access driver (AccessDatabaseEngine_x64.exe)
I have configured the script to run the following SP
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
[Code] ....
So I first create my Temp Table
The run the SP above then I run the insert into the Temp table defined
INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')
[Code] ....
The error message I get back is
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What I have set wrong on the import? Using SSIS at this point is not a real option.
View 0 Replies
View Related
Sep 6, 2007
Hi,
i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...
Here is my problem...
The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.
File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...
The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.
What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I
Regards
Mike
View 4 Replies
View Related
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
View 20 Replies
View Related
Oct 28, 2015
I have a requirement to load multiple flat files in target table .
I have created the package which used to load files into target table using For each loop container.
But now requirement has been changed now I have to take only those files from table where status="Success" and max JobId. By the query I am to get those records which need to load into table.
Below query I am using to get the files which need to load.
select [JobLogKey],[SrcNm],[DestNm]
FROM [ConfigRep].[dbo].[JobLog]
Where [JobId]=
(Select Max(cast([JobId] as Int)) Jobid
FROM [ConfigRep].[dbo].[JobLog]
Where [JobStat]='Success')
Output:-
JobLogKey SrcNm  DestNm
268 H:Data PlatformSource FileClient2LocHGSSpecLocation.txt Location.txt
269 H:Data PlatformSource FileClient1LocHGSSpecLocation.txt Location.txt
I have to load using above 2 files which are under SrcNm. I have created one variable called FileToLoad as Object and mapping to result set of above query. I have create JobId,SrcNm and DestNm variable to catch the record at every loop. I have  created 2 For each Loop containerÂ
Below screen shot of outer Foreach loop. Till here Its working fine. Inner for each loop container not executing any task under that. How to get it done.
View 3 Replies
View Related
Oct 24, 2007
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
View 8 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.Â
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Aug 14, 2012
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
Nov 29, 2007
I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.
Creating additional sources is fairly easy copy of the first source and change to the table name.
I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.
How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?
This seems like a simple / common task, but I've been unable to find a solution.
Thanks, Richard
View 1 Replies
View Related
Jun 1, 2007
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this??
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
View 3 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Dec 18, 2001
Hi i have a question.
How can i import dump from a text file , to an exsiting database, i´m will not useing DTS, i what to use BULK cmd in sql queryanalyser etc.
Need help
/Fredrik
View 1 Replies
View Related
Mar 28, 2003
I have SQL 2000 running under windows 2000 server.
Question:
I'm receving CSV files from the field.
I would like to built SQL database from daily CSV files. I've been doing this manually :( however, is there anyway I can do this task automactically so that once new daily CSV file ~200KB appear in the folder, progarm automatically insert recent file to SQL database...:) Do I need to write VB progarm for this? Please help...
View 4 Replies
View Related
Jul 4, 2007
I have created a stored procedure to handle xml data.
One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file.
Now I would like to create a SSIS package that takes the xml files from the network and uses this SP.
Should I have a file system task inside a for loop container?
How do I get started on this please?
Thanks
View 2 Replies
View Related
Jul 4, 2007
I have created a stored procedure to handle xml data.
One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file.
Now I would like to create a SSIS package that takes the xml files from the network and uses this SP.
Should I have a file system task inside a for loop container?
How do I get started on this please?
Thanks
View 2 Replies
View Related
