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
............Mor e 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
1  7552 
I 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" <ss*******@webu niverse.net> wrote in message
news:67******** *************** ***@posting.goo gle.com... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: John Roth |
last post by:
I've found a case where it seems that Python is importing two copies of a
module without any reason or indication. It took me a while to verify that
this is what is occuring: I had to write a __import__ hook to trace the
actual activity. The source code for the hook is below.
There are several examples of the problem in the trace; this is one
particularly juicy one.
-----------Part 1 ----------------
'0059' Import 'TypeAdapter'...
|
by: mthlv12 |
last post by:
If you are a minority at IBM check your credit cards carefully, IBM human
resources may be stealing from you. I found $370 of charges on my card, it
was done by human resources and it took me 5 months to get it back
|
by: HUMAN SOFTWARE DEVELOPMENT NETWORK |
last post by:
Hi,
Human Software Websphere Development Network, is a community of
teleworkers connected to our company.
Main goal is to develop software solutions (websites, .Net, VB, Java,
Portal, WSE, and so on) to our customer or other Business Partners.
HSDN Workflow is summarized as follow :
|
by: Jason Reichenbach |
last post by:
I've GOT to be missing something painfully obvious, here...
I need to programmatically get the full human name of the current user on a
local system, the same system upon which the app is running.
Using C#.NET MSDEV2003. OS will be Win2K or WinXP.
I've found numerous ways to get the user name, but have yet to find the way
to get the human name.
|
by: Shane Hathaway |
last post by:
Let's talk about the problem I really want help with. I brought up a
proposal earlier, but it was only half serious. I realize Python is too
sacred to accept such a heretical change. ;-)
Here's the real problem: maintaining import statements when moving
sizable blocks of code between modules is hairy and error prone.
I move major code sections almost every day. I'm constantly
restructuring the code to make it clearer and simpler, to...
| | |
by: Lauren Wilson |
last post by:
Owning your ideas: An essential tool for freedom
By Daniel Son
Thinking about going into business? Have an idea that you think will
change the world? What if you were told that there was no way you
could prevent someone from stealing your idea and exploiting it to make a profit? What
incentive would there be for you to be innovative, creative and
ambitious if you couldn’t be sure that your ideas would be protected?
Enter intellectual...
|
by: Ron Adam |
last post by:
from __future__ import absolute_import
Is there a way to check if this is working? I get the same results with or
without it.
Python 2.5 (r25:51908, Sep 19 2006, 09:52:17)
on win 32
_Ron
|
by: Cacsus |
last post by:
All,
I have a need at work to pull form history from a SharePoint form library on a nightly basis without the worry of human reliance, in case someone calls out sick or forgets. How can I setup Access to pull this external SharePoint data nightly, say around 9pm?
I have only a slight knowledge of databases, that is to say I know what they are and have become a novice user of Access, so I ask for forgiveness of my ignorance. I am starting...
|
by: W. Watson |
last post by:
Is there a single source that explains these statements?
------------------------------
from Tkinter import *
from Numeric import *
import Image
import ImageChops
import ImageTk
import time
import binascii
import tkMessageBox
|
by: victor robbin |
last post by:
Hi... is it possible to move a picture around the form with
human fingers?
I have a form with one picture on it........when my finger touch one picture,
I would like the picture to follow it and then
place the picture there.........
input = webcam
Can anyone tell me how with a code example ??
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
