Import Human-Readable text file into SQL Server 2000

Hello,

I am receiving a text file that is produced from a mainframe that is
out of my control. I am attempting to find a (hopefully clean) way to
import it into a SQL Server database in an automated fashion. I am
not really concerned about how many tables it requires or what the
schema looks like as long as the data remains related and ends up in
its respective fields (I will probably use scratch tables for this).

The data is given to me in a format that is meant to be printed out
and read by human eyes (in a text file). The format looks something
like this:

Begin File:
------------------------
1234 1234 1234 1234 XYZ Company 01/01/2003
......More stuff related to XYZ company for a couple of lines ......
......(this stuff can easily be parsed by position)......

MCARD VISA AMEX DISC
------------------------
TOTAL 11111.11 4444.44 5555.55 30.01
TRANS FEE .20 .20 .15 .15
TRANS AMOUNT 2222.22 888.89 833.33 4.50
DISC .0165 .0165 .0365 .0355
------------------------

ANOTHER HEADER

.........More stuff related to XYZ Company............

End File:

Well, this isn't the exact format, but just an example. The point is
that all of the data in each column is related and should end up in
the same record which is related to the parent record of XYZ Company
(or all in a single record in a single table if that is the closest I
can get).

Also, the rows are not always present. For example, if TRANS FEE
doesn't apply to anything in the row, then the entire row will
collapse and TRANS AMOUNT would be the next line after TOTAL.

I was looking at the bcp utility and dts, but dts doesn't seem to have
the performance capabilities (or reliability for that matter) I am
looking for. Bcp seems like it might work if there is some advanced
formatting commands that I can't find in the documentation - Anyone?

The best I can come up with is to use a high level language such as C#
or VB.NET to parse the text file into another text file that is comma
delimited, and then use the bcp utility (or bulk insert) to import it
into SQL Server where I can then use TSQL to manipulate it how I want.
I am trying to eliminate the high level language parse and just go
straight from file to database. Does anybody know an easier route?

TIAI would probably still go with the high level language. You have multiple
pieces of information in different formats relating to the same bigger
object. It looks as though you have a fixed length part at the top and then
break into a pivot table in the middle. I don't know any format file that
is going to decipher that for you.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Sven" <sstorhaug@.webuniverse.net> wrote in message
news:67ca584a.0309302103.1eb4b10e@.posting.google.c om...
> Hello,
> I am receiving a text file that is produced from a mainframe that is
> out of my control. I am attempting to find a (hopefully clean) way to
> import it into a SQL Server database in an automated fashion. I am
> not really concerned about how many tables it requires or what the
> schema looks like as long as the data remains related and ends up in
> its respective fields (I will probably use scratch tables for this).
> The data is given to me in a format that is meant to be printed out
> and read by human eyes (in a text file). The format looks something
> like this:
>
> Begin File:
> -----------------------
--
> 1234 1234 1234 1234 XYZ Company 01/01/2003
> .....More stuff related to XYZ company for a couple of lines ......
> .....(this stuff can easily be parsed by position)......
> MCARD VISA AMEX DISC
> -----------------------
--
> TOTAL 11111.11 4444.44 5555.55 30.01
> TRANS FEE .20 .20 .15 .15
> TRANS AMOUNT 2222.22 888.89 833.33 4.50
> DISC .0165 .0165 .0365 .0355
> -----------------------
--
> ANOTHER HEADER
> .........More stuff related to XYZ Company............
> End File:
>
> Well, this isn't the exact format, but just an example. The point is
> that all of the data in each column is related and should end up in
> the same record which is related to the parent record of XYZ Company
> (or all in a single record in a single table if that is the closest I
> can get).
> Also, the rows are not always present. For example, if TRANS FEE
> doesn't apply to anything in the row, then the entire row will
> collapse and TRANS AMOUNT would be the next line after TOTAL.
> I was looking at the bcp utility and dts, but dts doesn't seem to have
> the performance capabilities (or reliability for that matter) I am
> looking for. Bcp seems like it might work if there is some advanced
> formatting commands that I can't find in the documentation - Anyone?
> The best I can come up with is to use a high level language such as C#
> or VB.NET to parse the text file into another text file that is comma
> delimited, and then use the bcp utility (or bulk insert) to import it
> into SQL Server where I can then use TSQL to manipulate it how I want.
> I am trying to eliminate the high level language parse and just go
> straight from file to database. Does anybody know an easier route?
> TIA

import from Excel error

While attempting to import data from Excel using SSMS and the import/export wizard, I received the following error:

TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{11CD789E-0DCD-48C8-81F9-1065D87B5ADF}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({BB3EBEA7-7F0E-4346-A8A5-60E176732365})

Any clues as what causes this and how to resolve the problem. I am using Office XP.

Do you have Microsoft Jet OLE DB provider installed? It should be there by default. Check whether you are missing msjetoledb40.dll, or whether it is correctly registered may help.

HTH

wenyang

|||The dll is installed and I ran regsvr32; it registered successfully. However, the problem still exists.

Import Error

I receive unhelpful errors from attempting to import a ms access table into sql server 2005 using the import wizard. Here is the report.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)

Messages

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (73).
(SQL Server Import and Export Wizard)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [Company].[dbo].[EMPLOYEE] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

I received this type of error once before and got around it by removing all constraints from the table. I was able to import the table when the data types were nvarchar. I changed the data types to varchar and this occurred. How do I know what the message is specifically referring to or can I see more details of the import command. This is my first posting.

Charley,

Data conversion transformation requires SQL Server enterprise edition to be executed. see if this link provides more information

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=594086&SiteID=1

Import error

I am attempting to import an access mdb file from a remote server.
When I run the import wizard I get the follwing error:
"Cannot start your application. The Workgroup information file is missing or
opened exclusively by another user."
I have checked to see if the lock file is there but its not.
Any ideas ?Not sure if this will resolve the problem but you can check the Advanced
properties of the the Access connection and edit the System Database value
to point to the location of the mdw file.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql