Solved

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

Posted on 2011-09-02
2
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

734 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