I have a report that's created each day as a flat textfile.
Because I came from the Access world, I created a macro that imports
it with a schema that gives meaningful names to the various columns,
and then uses a query to massage some of the data for me (deletes the
first blank row and does a couple of calculations)
Then I use DTS to import the Access query as a table.
the textfile has a column called "File_num", and among several others,
a column called "Serial_num ". (the file numbers represent shipments,
and sometimes there are more than one serial number in the shipment,
etc., so there is a separate line for every serial number)
Naturally, I would like to split this info into two tables..one that
does not contain the serial numbers and has a primary key on the
"File_num" column, and another table that would contain just the
"File_num" and "Serial_num " columns. That way I could relate them
later...but most importantly, it will give me a table where I can use
the "File_num" as my primary key.
What would be the best way to import these two tables from one source
textfile? The other thing that gives me problems is that the text
file has no column names, and the first row is always blank.
I'm very new to SQL and DTS and would appreciate any direction.
Thanks,
Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown." 1 2546
"Larry Rekow" <larry@netgeexd otcom> wrote in message
news:3h******** *************** *********@4ax.c om... I have a report that's created each day as a flat textfile.
Because I came from the Access world, I created a macro that imports it with a schema that gives meaningful names to the various columns, and then uses a query to massage some of the data for me (deletes the first blank row and does a couple of calculations)
Then I use DTS to import the Access query as a table.
the textfile has a column called "File_num", and among several others, a column called "Serial_num ". (the file numbers represent shipments, and sometimes there are more than one serial number in the shipment, etc., so there is a separate line for every serial number)
Naturally, I would like to split this info into two tables..one that does not contain the serial numbers and has a primary key on the "File_num" column, and another table that would contain just the "File_num" and "Serial_num " columns. That way I could relate them later...but most importantly, it will give me a table where I can use the "File_num" as my primary key.
What would be the best way to import these two tables from one source textfile? The other thing that gives me problems is that the text file has no column names, and the first row is always blank.
I'm very new to SQL and DTS and would appreciate any direction.
Thanks,
Larry
- - - - - - - - - - - - - - - - - - "Forget it, Jake. It's Chinatown."
A common technique is to import the source file directly into a staging
table, then transform the data using SQL. This is often easier than trying
to implement complex transformations in DTS itself. In your case, you could
do something like this:
insert into dbo.FileTable
(file_num, col1, col2, ...)
select file_num, col1, col2, ...
from dbo.StagingTabl e
insert into dbo.FileSerialT able
(file_num, serial_num)
select file_num, serial_num
from dbo.StagingTabl e
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Yellowbird |
last post by:
I have an HTML form with a pre-built table containing "standard"
column headings (for example, First Name, Last Name, SSN, Hours,
etc.). I want a user to be able to enter values into the form and then
submit it. The data will be emailed to an administrator who will then
print the month's report. I also plan to extract the data and store it
in an external flat text file so that there is an electronic backup
version.
The user would...
|
by: Eric Caron |
last post by:
Hi
I try to import file in a dataset. How I can do this ?
|
by: deko |
last post by:
I've been trying to use the Access Import Wizard to expedite importing data
into my mdb. The nice thing about the wizard is that I can import from
different file formats - txt, xls, even Outlook - and dump everything into a
table.
The problem is once I have the data imported into a new table, I can't do
much with it. If I try to run an Append query and insert data from the new
table into an existing table, the query fails - "Error...
|
by: Shibu |
last post by:
Hi,
I have a situation where I need to convert business objects to a flat table.
The reverse is also required. I am using c# and
Oracle ODP. I am looking for an easier method to do the below steps.
Steps I follows for populating Business Objects is as follows
(1) Get a list of records containing various tables joined together from DB
using a single PL/SQL
(This is a specific requirement, So cannot change the design)
|
by: Randy |
last post by:
Folks:
We have a web-based app that's _really_ slowing down because multiple
clients are writing their own private data into a single, central
database. I guess the previous programmer did things this way because it
made things easy. Well, I'm the person that has to put up with
the long-term headache.
Anywho, someone at work wants things sped up, so what I'm looking
at doing is this for each client:
| |
by: hkhella |
last post by:
I receive several flat files daily that need to be improted into my Access Database.
Each file record starts with a specific letter or group of letters, the following lines or rows in the flat file starts with an identifier followed by several data fields each seperated by ^ and each line end with ^. The record ends with a specific letter (T) or group of letters. Each file received may contain one or more records. The end of file is marked by...
|
by: WP |
last post by:
Hello, I've just started to work with DB2 (using the express edition
version 9.5 under windows vista) and I'm accessing it through a java
program (my java's very rusty). The java program loads the driver,
connects and tries to create two tables. That works just fine. Then it
tries to import data into one of the tables but it seems I can't use
an import command with Statement.execute() because an exception is
thrown if I attempt to do this....
|
by: lxyone |
last post by:
Using a flat file containing table names, fields, values whats the
best way of creating html pages?
I want control over the html pages ie
1. layout
2. what data to show
3. what controls to show - text boxes, input boxes, buttons,
hyperlinks ie the usual.
The data is not obtained directly from a database.
|
by: kashif73 |
last post by:
I have a text file with hundreds of records. each line contain 1250 values seperated by a semicolon. I have created 2 tables in SQL server 2000, one with 1000 columns & the second with 250 columns. my question is how can i import this textfile in to these 2 tables.and how i ensure that first 1000 values go to first table & the rest 250 in the next from each line of the text file. can be this be done via VB.NET or ASP? I would be grateful for...
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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.
| |