Related Records Inport from single CSV

Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.
Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:

>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>
|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
sql