Solved

Load DB table from Pipe delimited TStringList (or textfile)

Posted on 2008-10-07
2
550 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
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now