"Le" <l...@iconus.com> wrote in message news:3c500756$1_2@dnews...
> Good morning and Thank you, Euan Garden
> Well it sounds simple, isn't it?
> It was the way I used to do the job, but just like you suggested, I have
to
> digging more into it.
> Here is the fact, The job need to complete fast and I have no time to
learn
> a new trick, that is why I use the old code.
> Anyway, I am going alone with your suggestion, but to start I have to
learn.
> So please teach the old dog a new trick. Would you?
> What I mean, if you have some sample, post it here if you are not mind.
> Thanks you.
> Tin Le
> "Euan Garden" <euan_gar...@spicedham.hotmail.com> wrote in message
> news:3c4fb889$1_1@dnews...
> > Why not just use DTS? This is what it is designed to do:
> > 1/ You can create DTS Packages on the fly using Delphi.
> > 2/ You can execute DTS Packages by:
> > a/ Writing com code that calls the execute method
> > b/ Calling out to createprocess and calling DTSRun
> > c/ Calling into SQL Server and using xp_cmdshell
> > d/ Calling into SQL Server and using sp_Oa
> > For the latter 2 see http://www.sqldts.com
> > -Euan
> > "Le" <l...@iconus.com> wrote in message news:3c4ed1c0$1_1@dnews...
> > > Good morning everyone. I have questions:
> > > How to import a text file and Excel work sheet to SQL Server 2000 in
> > delphi
> > > application?
> > > Please help and let me know what I did wrong.
> > > Thanks you all.
> > > Tin Le
> > > I am using the following codes and it works all the time with Delphi 5
> and
> > > SQL 7,0. But not work with Delphi 6 and SQL Server 2000?
> > > To Import a text file to a SQL Server 7.0 :
> > > In Query analyzer:
> > > BULK INSERT pubs.dbo.publishers2 FROM
> > > P:\BMS\CV137-137\DataTransfer\CV137137_Master'
> > > WITH
> > > DATAFILETYPE = 'char',
> > > FIELDTERMINATOR = '|',
> > > ROWTERMINATOR = '\n' )
> > > Translate to be equal to the following codes:
> > > Please ignore the typo errors here if you have found one, because I
just
> > > copy and past it from Delphi codes.
> > > 'BULK INSERT '+sDatabaseOwnerTableName+ ' FROM '+
> > > #39+ P:\BMS\CV137-137\DataTransfer\CV137137_Master'#39 +
> > > ' WITH ( ' +
> > > ' DATAFILETYPE = '+#39 + cbxDatafile.Text + #39+','+
> > > ' FIELDTERMINATOR = '+#39+cbxFieldTerminator.Text + #39+','+
> > > ' ROWTERMINATOR = '+#39+cbxRowTerminator.Text + #39+
> > > ' ) ';
> > > With ADOCommand Do Try
> > > Prepared := False;
> > > ConnectionString := DB_CONNECTION('ClientData');
> > > ParamCheck := True;
> > > ExecuteOptions := [eoExecuteNoRecords];
> > > CommandType := cmdText;
> > > CommandText := sSQL;
> > > Prepared := True;
> > > Execute;
> > > blSucceed := True;
> > > Except ON E: Exception Do Begin
> > > Screen.Cursor := crDefault;
> > > slMesg.Add(DateTimeToStr(Now) +' '+E.Message);
> > > End;// Except ON E: Exception Do Begin
> > > End;
> > > )
> > > Here is the errors that generated when I try to import a text file to
> SQL
> > > 2000
> > > Invalid Object name 'pubs.dbo.publishers2'. Process stopped...
> > > To Import a Excel file to a SQL Server 7.0 :
> > > In Query analyzer:
> > > select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
> > > 8.0;HDR=NO;IMEX=2;DATABASE=
> > > 'P:\BMS\CV137-137\DataTransfer\CV137137_PeriodC.XLS','SELECT * FROM
> > > [Sheet1$]')
> > > Translate to be equal to the following codes:
> > > Please ignore the typo errors here if you have found one, because I
just
> > > copy and past it from Delphi codes.
> > > sSQLH := 'SELECT a.* into dbo.CV137137_Test FROM OpenRowSet(' +
> > > #39+'Microsoft.Jet.OLEDB.4.0'+#39+','+#39+'Excel 8.0;'+
> > > 'HDR=YES;IMEX=2;DATABASE='+
> > > 'P:\BMS\CV137-137\DataTransfer\CV137137_PeriodC.XLS'#39+','+#39+
> > > 'SELECT * FROM [Sheet1$]'+#39+')';
> > > With ADOCommand Do Try
> > > Prepared := False;
> > > ConnectionString := DB_CONNECTION('ClientData');
> > > ParamCheck := True;
> > > ExecuteOptions := [eoExecuteNoRecords];
> > > CommandType := cmdText;
> > > CommandText := sSQL;
> > > Prepared := True;
> > > Execute;
> > > blSucceed := True;
> > > Except ON E: Exception Do Begin
> > > Screen.Cursor := crDefault;
> > > blSucceed := False;
> > > Result := E.Message;
> > > slMesg.Add(DateTimeToStr(Now) +' '+E.Message);
> > > End;// Except ON E: Exception Do Begin
> > > End;// With dsAction Do Try
> > > Here is the errors that generated when I try to import a excelt file
to
> > SQL
> > > 2000
> > > OLE DB Procider 'Miscrosoft.Jet.OLEDB.4.0' report an error. The
provider
> > did
> > > not give any info....
> > > I did tested and tested at home with Delphi 5 and SQL 7 and it is
> working
> > > just fine.
