Solved

SQL to input a flat file

Posted on 2006-06-16
5
539 Views
Last Modified: 2012-06-22
Can someone show me an example of how you would write SQL to input a flat file?  is this possible?  or do you have to schedule DTS job to do this?  For example, if I wanted to received a monthly csv file with and load to a table is it easier for DTS to do this or is it possible to write SQL procedure to do this?
0
Comment
Question by:Hojoformo
5 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 16921497
you can do this in three ways
1. dts
2. bulk insert
3. bcp
0
 
LVL 20

Assisted Solution

by:Sirees
Sirees earned 100 total points
ID: 16921821
This site has some good examples

http://www.sqldts.com/default.aspx?6
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 100 total points
ID: 16921907
I use bulk insert - it is the fastest but does not allow for any transformations of data.

0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 100 total points
ID: 16922574
I use DTS.  You can call it from within  query analyzer, you can schedule the dts package as a job, or you can run it external to SQL using the DTSRUN executable.

If you invoke the DTS wizard from Sql Enterprise Manager, you can save the first import as a package so you can have it to run every month.  That saves learning how to create the dts package from the UI.

Tom
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 16925109
Rfer to to the topic Using format files in Books Online for a complete
explanation of how to work with format files.


Three common formats for text data files are:


1. Tab-delimited
The records are variable length.
Data item are delimited with tabs.
Missing values are indicated by two consecutive tabs.
This is the default bcp format.


2. Fixed-width
The records are fixed length.
Data items are padded with spaces to fill out fields to the maximum width.
Missing items are indicated by all spaces.
This is a popular output format from COBOL mainframe systems.


3. Comma-separated values (csv)
The records are variable length.
Data item are delimited with commas.
Character data items are also enclosed in double quotes.
Missing values are indicated by two commas.
This is common output format from Excel.


Some examples:
(Change the path, databases, and server names as needed.)


create table maryam (a1 varchar(10), a2 varchar(10), a3 int)


/***
1. Tab-delimited data file.
Data file maryam1.dat:
(Those should be tabs between the data items.)


mary dep1 1
john dep2 1
jim dep1 2


Format file maryam1.bcp:


8.0
3
1    SQLCHAR    0    10    "\t"      1     a1     ""
2    SQLCHAR    0    10    "\t"      2     a2     ""
3    SQLCHAR    0    12    "\r\n"    3     a3     ""


***/
exec master..xp_cmdshell 'pushd C:\staging\zap & bcp zap..maryam in
maryam1.dat -fmaryam1.bcp -Slindaw\mbbf -T'


/***
2. Fixed-width data file.
Data file maryam2.dat:


mary      dep1      11
john      dep2      11
jim       dep1      22


Format file maryam2.bcp:


8.0
3
1    SQLCHAR    0    10    ""        1     a1     ""
2    SQLCHAR    0    10    ""        2     a2     ""
3    SQLCHAR    0    12    "\r\n"    3     a3     ""


***/
exec master..xp_cmdshell 'pushd C:\staging\zap & bcp zap..maryam in
maryam2.dat -fmaryam2.bcp -Slindaw\mbbf -T'


/***
3. Comm-separated values data file.
Data file maryam3.dat:


"mary","dep1",111
"john","dep2",111
"jim","dep1",222


Format file maryam3.bcp:


8.0
4
1    SQLCHAR    0    1     "\""      0     quote  ""
2    SQLCHAR    0    10    "\",\""   1     a1     ""
3    SQLCHAR    0    10    "\","     2     a2     ""
4    SQLCHAR    0    12    "\r\n"    3     a3     ""


***/
exec master..xp_cmdshell 'pushd C:\staging\zap & bcp zap..maryam in
maryam3.dat -fmaryam3.bcp -Slindaw\mbbf -T'


select * from maryam
drop table maryam


/***
a1         a2         a3
---------- ---------- -----------
mary       dep1                 1
john       dep2                 1
jim        dep1                 2
mary       dep1                11
john       dep2                11
jim        dep1                22
mary       dep1               111
john       dep2               111
jim        dep1               222
***/


0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
SQL Restore Script - Syntax Error 8 70
Group by and order by clause 28 36
while loop in html mail format 5 32
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now