How to import fixed-width text file in MS SQL 2000?

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:376852
Hello,
Is there a simple answer to this simple question?
How to import fixed-width text file?
I know the width of the columns but there are so many that I don't want
redefine them. I have already a table build for that data with the necessary
fields width. In dbase it was such a simple thing to do...
Note:
I went through the Import wizard in MS SQL 2000 and I could not find any
thing there about importing fixed-width text file.
Any help is greatly appreciated,
LesThis can be done through the DTS import wizard in Enterprise Manager under
Tools --> Data Transformation Services --> Import Data.
In the wizard select the source Data Source as 'Text File'. The wizard will
guide you through the steps.
Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
command.
--
Stuart Fish
www.technologic.co.nz
"Tom" <tom@.killspam.com> wrote in message
news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
> Hello,
> Is there a simple answer to this simple question?
> How to import fixed-width text file?
> I know the width of the columns but there are so many that I don't want
> redefine them. I have already a table build for that data with the
> necessary fields width. In dbase it was such a simple thing to do...
> Note:
> I went through the Import wizard in MS SQL 2000 and I could not find any
> thing there about importing fixed-width text file.
>
> Any help is greatly appreciated,
> Les
>|||I've tried that and it and I found that through the wizard you have to
define the length of the fields.
But I have already a table with the fields. I don't want to go through 50
fields every time I want to import data.
Is that all what we pay MS for?
Les
"Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
> This can be done through the DTS import wizard in Enterprise Manager under
> Tools --> Data Transformation Services --> Import Data.
> In the wizard select the source Data Source as 'Text File'. The wizard
> will guide you through the steps.
> Alternative methods to DTS are the bcp utility and the 'BULK INSERT'
> command.
>
> --
> --
> Stuart Fish
> www.technologic.co.nz
>
> "Tom" <tom@.killspam.com> wrote in message
> news:M0UGd.14491$W33.456289@.news20.bellglobal.com...
>|||At the end of the DTS Import/Export wizard there are options that allow you
to save the DTS package instead of running immediately. That way the DTS
package can then be executed or scheduled as you need it. You don't need to
redefine it each time, as long as your file and table structure remain the
same.
If the package is saved to the SQL Server it can be viewed in Enterprise
Manager under the Data Transformations Services --> Local Packages folder.
"Tom" <tom@.killspam.com> wrote in message
news:sOUGd.15394$W33.466935@.news20.bellglobal.com...
> I've tried that and it and I found that through the wizard you have to
> define the length of the fields.
> But I have already a table with the fields. I don't want to go through 50
> fields every time I want to import data.
> Is that all what we pay MS for?
> Les
>
> "Stuart Fish" <stuart@.fish._no__spam_.net.nz> wrote in message
> news:u7PwA4M$EHA.2316@.TK2MSFTNGP15.phx.gbl...
>|||Les,
Most of people choose to loading the data from import file in to a temporary
table then doing manipulation on that temp table, this way is useful when d
uring the import progress, you could also maintain your database integrity.
to solve your problem, you could you DTS or SQL stored procedure. i was sele
cted stored procedure to manage this process.
e.g:
you have list of invoice need to be imported to invoice table every day. thi
s file is a flat fixed length file storing in following desc
Invoice#ProductCategory
xxxx-xxxAAAAAAAACCCCCCCC
length of row was 24, since Invoice# was 8, Product 8, Category 8. This file
name is invoice.txt
you could define a table as follow
INVOICE_TEMP(
ROWTMP VARCHAR(24)
)
In you stored procedure, you would have this command
BULK INSERT INVOICE_TEMP FROM invoice.txt WITH (BATCHSIZE = 10000, CODEPAGE
='RAW', ROWTERMINATOR = '\n')
Next: You could be able to insert the rows from this temp file to destinatio
n table.
INSERT INVOICE (InvoiceNo, Product, Category)
SELECT SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1, 8), SUBSTRING(ROWTMP, 1
, 8) FROM INVOICE_TEMP
*done*.
Next: your stored procedure could be called any time you want, or it could b
e called from DTS and have it running in a defined schedule.
Good luck!
Doan
Message posted via http://www.droptable.com