How to increment ID sequentially?

I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?

Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.

|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||

Hi mychucky,

You would have to use 'identity seed' property in your table design window in your SQL Server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||

ZLA:

Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Thanks so much! I got it working now. I would never have found the solution if without your link.

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.

How to import flat files into Microsoft SQL 2005 Express Edition automatically?

Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?

hi,

you can perhaps have a look at the features provided by linked servers using JET data provider as reported in http://msdn2.microsoft.com/en-us/library/ms190479.aspx

say you have a situation like this, a flat.txt saved in d:\txt\flat.txt

<flat.txt>
FirstName|LastName
andrea|montanari
jr2006|jr2006
</flat.txt>

you can define a schema file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp) like

<schema.ini>
[FLAT.TXT]
ColNameHeader=True
Format=Delimited(|)
</schema.ini>

and proceed, on SQL Server, registering a linked server based on JET 4.0 OLE DB provider, querying it and eventually importing data from it in a regular SQLX database table like

SET NOCOUNT ON;

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\txt\',
NULL,
'Text';

SELECT * FROM txtsrv...flat#txt;

EXEC sp_dropserver 'txtsrv', 'droplogins';

resulting in

--<--

FirstName LastName
-- --
andrea montanari
jr2006 jr2006

regards

|||

Andrea,

Thanks a bunch if your solution is as pretty as your name, then I believe we shall be OK.

regards

JR2006

|||

Andrea, Great solution and I am wondering if it will help me in what I am trying to accomplish. I have a CSV file that comes from another company. The file is a CSV only in the extension, since there are no commas or seperation in the data. here's what I am trying to do.

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this? Currently we are importing it into Access2003 and seperating it into the columns. Then merging it with the tables that it needs to be in. I would like to make this a simple broswe for the file, click the upload button and it sort and upload the information. Am I asking too much?

Let me know what you think.

Charles

|||

hi,

Kraven3d wrote:

Here is a sample of the data and what I need to do with it.

1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295

This data needs to be brought into the data base as:

1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

The seperation will ALWAYS occur after the 1, 5, 11, 12, 15,19, 23, 25 & 29th characters. Do you have any idea how I can accomplish this?

you can define fixed width columns in the schema.ini as following

<flat.txt>
1ETTX800401V411TTX BNSF851293
1ETTX800913V411TTX BNSF840295
</flat.txt>

<schema.ini>
[Flat.TXT]
ColNameHeader=False
Format=FixedLength
Col1=Col1 Text Width 1
Col2=Col2 Text Width 4
Col3=Col3 Text Width 6
Col4=Col4 Text Width 1
Col5=Col5 Text Width 3
Col6=Col6 Text Width 4
Col7=Col7 Text Width 4
Col8=Col8 Text Width 2
Col9=Col9 Text Width 4
</schema.ini>

SET NOCOUNT ON;
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Import\',
NULL,
'Text';
GO
CREATE TABLE #tText (
Col1 int,
Col2 char(4),
Col3 int,
Col4 char(1),
Col5 int,
Col6 varchar(4),
Col7 varchar(4),
Col8 int,
Col9 varchar(4)
);
SELECT * FROM txtsrv...flat#txt;

INSERT INTO #tText
SELECT CONVERT(int, Col1), Col2, CONVERT(int, Col3), Col4, CONVERT(int, Col5), Col6, Col7, CONVERT(int, Col8), Col9
FROM txtsrv...flat#txt;

SELECT * FROM #tText;
DROP TABLE #tText;
GO
EXEC sp_dropserver 'txtsrv', 'droplogins';

--<-
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
- - - - - - - -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
-- - -- - -- - - -- -
1 ETTX 800401 V 411 TTX BNSF 85 1293
1 ETTX 800913 V 411 TTX BNSF 84 0295

then you can use the SELECT to perform whatever action you require... even populate a temp table, casting the data types as required ..

regards

|||Hi Andrea,

I'm trying to get it to work but I keep running into this error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas?

Thanks,

Bradley|||These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B|||

B. Sandvik wrote:

These approaches never occurred to me. I have found my heroine. Thanks for the great tips!

-B

... I'm male

regards

|||

I guess they didn't go to your web site to see you picture like I did.

Mike

|||

[OT]

was looking for you in Redmond at the MVP summit...

I was able to meet Jens, but not you..

regards

|||

Will I need to define everything in the Flat.txt file? Here's what I mean.

I have entered only 2 lines in my original post, where my official txt file has over 1 million rows. The one I just got is comma delimited and I can use it as a qualifier if it will make it easier for me to import into my DB.

This is what the new file looks like:

1,AAMX,050167 ,C112,AAMX, , ., ,
1,AAMX,061468 ,C112,ACFX, , ., ,
1,AAMX,062068 ,C112,ACFX, , ., ,

I have 2 documents that I will need to do this on. 1 is the official EVERY entry and the other is an update file where I will need to see if a character has changed; if so update it, if not then it will leave it alone.

I know that that will be a simple update statement. But I will be getting a TXT file like this 2 times a month.

Any ideas would be helpful.