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

Posted on 2011-09-02
Medium Priority
Last Modified: 2012-05-12
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
Question by:cesemj
LVL 40

Accepted Solution

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

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


Author Closing Comment

ID: 36498028

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
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 tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

587 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