[thelist] ODBC Text File Driver and ASP

[Scott Dexter]

> -----Original Message-----
> From: Anthony Baratta [mailto:Anthony at Baratta.com]
> I'm looking to solve a current problem I have to auto load a 
> very large amount of
> data daily. (Basically drop and re-create the target tables, 
> import the data, and
> create the indexes.) Side Note: the data files might need 
> massaging prior to
> importation, e.g. a header row and cleanup of the file to 
> remove "bad data" lines.

<plug>
this just happens to be a process Ti3 does with almost every client
</plug>

can't find my VB-bcp source (the guy who wrote it isn't in until tomorrow,
I'll send it along then), but here's some text isam stuff:

* use a Schema.ini file. This allows you to define the file layout and
column names and delimiters and such. Here's a sample:

[weeklyupload.txt]
ColNameHeader=False
Format=TabDelimited
TextDelimiter=None
CharacterSet=OEM
Col1=StoreID Integer
Col2=CurrentLDS Integer
Col3=CurrentTickets Integer
Col4=CurrentHitRate Integer
Col5=AsofDate DateTime
Col6=PreviousMonthHitRate Integer
Col7=PreviousMonthAStatus Integer
Col8=Indicator char width 1

* then the code references a file named the same as the heading in the
Schema.ini:

<%
Sub UploadWeekly(byval wfile)
errorcount = 0
' the directory the file is located in is the 'database'
' the files in the directory are the 'tables'

' connect to the text db *.txt (tab delimited) --one
lineconnstr="DefaultDir="&server.MapPath("./Weekly")&";Driver={Microsoft
Text Driver (*.txt; *.csv)};FIL=text"

Set otxtConn = SetupSQL(connstr)

' our table is the *FULL* file name
tbweekly = Server.Mappath("./Weekly/"&wfile)

' get records
Set oRS = otxtConn.Execute("Select * from "& tbweekly)
...
' Operate like any other RS
End Sub

I'll get you the bcp COM code in the morning, it isn't too bad to work with,
just that if you have to massage the file you might want to do what we do
here (for files 3million+ rows in length): massage into a temp file, then
bcp the temp file....

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/