?
Solved

creating a qurey to import mutiple flat file content into specific existing tables

Posted on 2011-09-02
2
Medium Priority
?
293 Views
Last Modified: 2012-05-12
hi,
I am using sql server 2k5 to import 264 flat file contents into each matching table.  I have a long way to go.  I perform the following steps below.  Please share an examples of a query that uses the criteria below to import data from multiple flat files from a folder on  a pc into its related database tables.  Thanks

Import Steps I perform
Source: Flat File
Locale: English (United States)
Code page: 1252 (Ansi - Latin 1)
Format: Delimited
Text Qualifier: "
Header row delimiter: {CR}{LF}
Header rows to skip: 0
Column names in the first data row: yes
Edit Mappings-->Column Mappings-->Delete Rows in destination table

Choose a Destination
Destination: SQL Native Client
Server Name: dbsrv01
Use Windows Authentication
Database: dbTrains
0
Comment
Question by:cesemj
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 36475395
been there done that....think a folder is receiving "flat" files and you need to import them into SQL tables (for simplicity) with same name.

I used:

EXEC xp_cmdshell N'dir G:\FTP_DOWNLOADS\CSP_file.txt';

cmd shell to get their names in a temp table than I used that table to run a SP like below wich will import your flat file into a SQL table. With little bit of work you can adjust it and creat a ne table for each new impot file or put it into an existing table:


--Usage : exec sp_readTextFile 'G:\FTP_DOWNLOADS\CSP_file.txt'
Create proc sp_readTextFile @filename sysname
as

    begin
    set nocount on
    Create table #tempfile (line varchar(8000))
    exec ('bulk insert #tempfile from "' + @filename + '"')
    select * from #tempfile
    drop table #tempfile
End
go




0
 

Author Closing Comment

by:cesemj
ID: 36498028
Thanks
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A procedure for exporting installed hotfix details of remote computers using powershell
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question