How to include the date as part of the log file name...

Hello to all,
I'm using SQL Server agent to perform some schedule jobs.
I have it setup so that it will write to an result output
file (Jobs Steps -> Advanced -> Output file:
The problem is that I would like to name this output file
such as 'result%date%.log' where %date% is the current
date so that I can get a seperate log file each day.
Does anyone know how to do this ? %date% does not work.
Thanks in advance.
KinKin:
The way I have it up is that I do archiving of these output log files every
day as a separate job. In the archiving process I copy the files and add
date to the filename as follows:
declare @.cmd sysname
declare @.var sysname
set @.var = convert(varchar(11),getdate(),112)
--add date to file and archive
set @.cmd = 'copy e:\Logs\DBMaintenance\*.* e:\Logs\Archive_Logs\*' + @.var +
'.txt'
exec master..xp_cmdshell @.cmd
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||I'm not sure that there is a direct way to do that in SQL Agent. You could
always build you're own backup log command and execute that directly. Also,
you could simply let the job name it what it wants, and then add a step that
uses xp_cmdshell to rename the file.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Kin" <ninka_2000@.yahoo.com> wrote in message
news:017501c34fa8$212552a0$a301280a@.phx.gbl...
> Hello to all,
> I'm using SQL Server agent to perform some schedule jobs.
> I have it setup so that it will write to an result output
> file (Jobs Steps -> Advanced -> Output file:
> The problem is that I would like to name this output file
> such as 'result%date%.log' where %date% is the current
> date so that I can get a seperate log file each day.
> Does anyone know how to do this ? %date% does not work.
> Thanks in advance.
> Kin|||>--Original Message--
>Hello to all,
>I'm using SQL Server agent to perform some schedule
jobs.
>I have it setup so that it will write to an result output
>file (Jobs Steps -> Advanced -> Output file:
>The problem is that I would like to name this output file
>such as 'result%date%.log' where %date% is the current
>date so that I can get a seperate log file each day.
>Does anyone know how to do this ? %date% does not work.
>Thanks in advance.
>Kin
>.
>

how to import the database data from backup file ?

Hello,All:

I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.

Now I want to import the backup data(generated by production server),I don't know how to do it.

Is it possible to do it ? anyone can give me a soluation ?

thanks in advanced!

->Open Enterprise manager of SQL Server 2000

-> Select Your Server

-> right click on your database

-> All Tasks -> Restore Database

-> Select from Device

-> click the button Select Devices

-> in the new window select Disk option

-> click add button

-> select your backup file in file name option

-> ok

->ok

->ok

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.