Solved

Load DB table from Pipe delimited TStringList (or textfile)

Posted on 2008-10-07
2
577 Views
Last Modified: 2008-10-08
Need some assistance in determining the best method of loading a DB table.

Currently I have a table called tbExportDocument which has roughly 30 fields in it as it is shared by 3 different scanner platforms (each platform uses most fields, but have fields that are unique to them).
What I'm doing is looping through a list of images and grabbing all kinds of data about them (each image ends up being a record in the table). After processing each image I do an insert into the tbExportDocument table for that image and continue on to the next image.

I was thinking that maybe I could speed up the processing dramatically by storing the values that I would normally pass as parameters to the InsertRecord stored procedure into a TStringlist (each image being on a separate line with the data delimited with a Pipe). After the loop of images is complete, I want to do ONE insert call which inserts the whole thing at the end (instead of 300+ inserts). There would be a HeaderLine that would give me the fieldname mapping for the piped file.

I looked at doing a Bulk Insert, but like I mentioned the data captured and stored for one scanner platform is different than the other 2 scanner platforms that use the same table.

Is there a way to do a Bulk insert to only the fields required by whatever scanner platform I'm working with currently? The table I'm inserting into also has an Identity Seed (ExportDocumentID). I'm not familar with FormatFile that I've seen mentioned in other threads, so if we need to use that I'd need a brief overview of that.

Also, can this be done from a TStringList or would I have to save the contents to a text file (easy enough to do)?

I'll try and put a watered down version of the table here to show an example of what I'm needing.

tbExportDocument:
ExportDocumentID    ImgSize   IBMLBatch   Camera   TransSequence  DatFileName  ScannerType
---------------------     ---------    ------------    ---------    ------------------   --------------   ----------------
          1                       2000        12345             1                                                              IBML
          2                       2200                               1                 1                                           OPEX
          3                       2200                               1                                         1.DAT           KODAK
----------------------------------------------------------------------------------------------------------------------

As you can see each scanner platform uses some of the same fields, but also has fields unique to itself

Example of what my TStringList (or textfile) will look like for each (with one header line with fieldnames)

IBML Example:
ImgSize|IBMLBatch|Camera|ScannerType
2000|12345|1|IBML

OPEX Example:
ImgSize|Camera|TransSequence|ScannerType
2200|1|1|OPEX

KODAK Example:
ImgSize|Camera|DatFileName|ScannerType
2200|1|1.DAT|KODAK
0
Comment
Question by:jasonkrueger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22666614
you could do it with batch inserts if you would have 3 distinct files - one for each scanner
you can bulk insert records with only some of the columns of the tables, but all the records in the file must have the same columns (structure)
0
 

Author Comment

by:jasonkrueger
ID: 22671261
I actually solved this late last night saving the TStringList to  a text file and I created a view for each scanner platform. I do the bulk insert into the view which only contained the fields that I needed from the text file .

I'll give you the points since you offered your time to answer.

Thanks,
Jason
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question