Text File To SQL Server Using VB6

Hello,

I am developing an application in Vb6 and the datbase is SQL Server 2000.

The source of the data is a couple of comma seperated text files.

I am using the following statment to transfer the data from text file to SQL
Server:

Dim objConnMisc As ADODB.Connection, strSQL as String

Set objConnMisc = New ADODB.Connection

With objConnMisc
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & "\;Extended
Properties=""Text;HDR=No;FMT=Delimited="""
.Open
End With

strSQL = "Insert Into TABTEXTDATA([ONE],[TWO],[TH REE],[FO
UR],[FIVE],[SIX],[SEVEN],[EIGHT],[NINE],[TEN],[ELEVEN],[TWELVE],[THIRTEEN]) "
_
& "IN ''[ODBC;Driver={SQL Server};Server=TEMP;Database=TEMP" _
& ";uid=test;pwd=test] Select
F1,F2,F3,F4,F5,F6,F7,F8,F9,LTRIM(RTRIM(Str(Len(F10)))),F11,F12,F13 From
[Test1.txt]"

objConnMisc.Execute strSQL

Now all the data is getting imported but for some data in the column TEN.
In the text file it contains data in the following format:

001,00086,1111 , SSSSS , ABC , ABC ,20061227,20070630,
-4,7435677 , 55830,AAA ,
001,00087,Z1111 , SSSSS , ABC , ABC ,20061227,20070731,
-59, , 56530,AAA ,
001,00088,1111 , SSSSS , ABC , ABC ,20061227,20071031,
1,D222222 , 313.7,AAA ,
001,00089,1111 , SSSSS , ABC , ABC ,20061227,20070531,
52, , 44780,AAA ,
001,00090,1111 , SSSSS , ABC , ABC ,20061227,20071031,
3, , 2402,AAA ,
001,00091,1111 , SSSSS , ABC , ABC ,20061227,20070430,
1,D123456 , 1249,AAA ,


It inserts NULLS at places where the data contains characters and numbers.
If it is only numbers, then there isn't any problem at all.

I tried to use the following statement to see what happens and it still
inserts NULL Values where there is character and number data:

strSQL = "Select * Into TestData " _
& "IN ''[ODBC;Driver={SQL Server};Server=TEMP;Database=TEMP" _
& ";uid=test;pwd=test] From [Test1.txt]"

The datatype in the back end is Varchar(100) NULL. When I do a transfer
using DTS, everything comes in fine. I need to make this specific method
working.

I need to take the data into SQL Server as it is. Any help is highly
appreciated.

Thanks,

Pradeep
.

Relevant Pages

  • Re: Text File To SQL Server Using VB6
    ... ¤ I am developing an application in Vb6 and the datbase is SQL Server 2000. ... Dim objConnMisc As ADODB.Connection, ... It inserts NULLS at places where the data contains characters and numbers. ...
    (microsoft.public.vb.database.ado)
  • Re: Stored proc with version number ?
    ... in addition to letter characters from other languages. ... Certain symbols at the beginning of an identifier have special meaning ... in SQL Server. ... I choose> the existing stored proc uspTableSave and right click Properties. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can you use osql in DTS?
    ... each one of these queries has in it the same first 18 characters (or ... it from Query #3. ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.dts)
  • Re: Can you use osql in DTS?
    ... each one of these queries has in it the same first 18 characters (or ... it from Query #3. ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: sendStringParameterAsUnicode: How to insert unicode data corre
    ... We are using SAP XI and connecting it to SQL Server 2005 using JDBC. ... The destination columns are nvarchar and store unicode data correctly. ... is unable to display the characters you have. ...
    (microsoft.public.sqlserver.jdbcdriver)