Importing a Text File

Hi,
I'm trying to parse a text file in SQL Server but am having problems
importing the file.
When I try to import the text file using Enterprise Manager it doesn't
preserve the row order. Currently I'm importing the text file into Access
(which preserves the order) and creating a primary key there. Then I can
import it into SQL Server and order by the key. Is there a way to skip this
ACCESS step and directly import into SQL server with the order intact?
Thank you very much.Use ORDER BY when selecting the data.
--
TIA,
ChrisR
"Irving" wrote:
> Hi,
> I'm trying to parse a text file in SQL Server but am having problems
> importing the file.
> When I try to import the text file using Enterprise Manager it doesn't
> preserve the row order. Currently I'm importing the text file into Access
> (which preserves the order) and creating a primary key there. Then I can
> import it into SQL Server and order by the key. Is there a way to skip this
> ACCESS step and directly import into SQL server with the order intact?
> Thank you very much.|||Thank you for responding.
The problem is there's nothing to order by. That's why I import into Access
first to create a primary key.
"ChrisR" wrote:
> Use ORDER BY when selecting the data.
> --
> TIA,
> ChrisR
>
> "Irving" wrote:
> > Hi,
> >
> > I'm trying to parse a text file in SQL Server but am having problems
> > importing the file.
> > When I try to import the text file using Enterprise Manager it doesn't
> > preserve the row order. Currently I'm importing the text file into Access
> > (which preserves the order) and creating a primary key there. Then I can
> > import it into SQL Server and order by the key. Is there a way to skip this
> > ACCESS step and directly import into SQL server with the order intact?
> >
> > Thank you very much.|||Might try using DTS to import the file into a table that contains a PK with
the IDENITY property. The SELECT ...ORDER BY PK column.
HTH
Jerry
"Irving" <Irving@.discussions.microsoft.com> wrote in message
news:AFD2116A-3615-4680-966C-8DCC17588C09@.microsoft.com...
> Thank you for responding.
> The problem is there's nothing to order by. That's why I import into
> Access
> first to create a primary key.
> "ChrisR" wrote:
>> Use ORDER BY when selecting the data.
>> --
>> TIA,
>> ChrisR
>>
>> "Irving" wrote:
>> > Hi,
>> >
>> > I'm trying to parse a text file in SQL Server but am having problems
>> > importing the file.
>> > When I try to import the text file using Enterprise Manager it doesn't
>> > preserve the row order. Currently I'm importing the text file into
>> > Access
>> > (which preserves the order) and creating a primary key there. Then I
>> > can
>> > import it into SQL Server and order by the key. Is there a way to skip
>> > this
>> > ACCESS step and directly import into SQL server with the order intact?
>> >
>> > Thank you very much.|||Irving,
One possibility is to add line numbers with
C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
or write a short program in C or C++ or C# to do something similar.
(Or use a Unix shell.)
This DOS find command will add bracketed line numbers at the beginning
of each line, along with a blank line and a line with the file name at
the top, these first two without any line numbers. You could then
import this file into a table with one varchar(270) column and retrieve
both the line number and the contents with another query:
select
cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
linenumber,
substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
from importedTable
where substring(onlyColumn,1,1) = '['
-- Steve Kass
-- Drew University
-- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F
Irving wrote:
>Hi,
>I'm trying to parse a text file in SQL Server but am having problems
>importing the file.
>When I try to import the text file using Enterprise Manager it doesn't
>preserve the row order. Currently I'm importing the text file into Access
>(which preserves the order) and creating a primary key there. Then I can
>import it into SQL Server and order by the key. Is there a way to skip this
>ACCESS step and directly import into SQL server with the order intact?
>Thank you very much.
>|||Thank you very much Steve.
It looks like this will work.
"Steve Kass" wrote:
> Irving,
>
> One possibility is to add line numbers with
>
> C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
> or write a short program in C or C++ or C# to do something similar.
> (Or use a Unix shell.)
> This DOS find command will add bracketed line numbers at the beginning
> of each line, along with a blank line and a line with the file name at
> the top, these first two without any line numbers. You could then
> import this file into a table with one varchar(270) column and retrieve
> both the line number and the contents with another query:
> select
> cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
> linenumber,
> substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
> from importedTable
> where substring(onlyColumn,1,1) = '['
>
> -- Steve Kass
> -- Drew University
> -- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F