Solved

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

Posted on 2011-09-02
2
283 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to install and use the NTBackup utility that comes with Windows Server.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

688 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