SQL server Question

[vkallelil]

Hi all,

How do I transfer a .txt file to SQL server. I want to use VB front end for my SQL server database.

I used import specifications in Access before, but how do I transfer to a Sql server, or what is the best way of transfering data to Sql server?????


Reggie

[Chris Byrom]

Check out Data Transformation Services. You can do pretty much anything with this, as far as importing goes. It allows you to do stuff like schedule jobs, write scripts to transform the data as you import it, and some other neat stuff. I have been using it for a couple of months and it seems to work well.

[stm]

Hi,

Beside what Chris suggest, I use the ODBC text file driver to merge it:

oTxtConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=\some_path\;" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"

once u've made the connection, just treat it like other ADO connection, and I use a recordset to loop through it:

oTxtRs.Open "Select * From " & datfile(i), _
oTxtConn, adOpenStatic, adLockReadOnly, adCmdText

Make sure that u have the schema.ini file in ur working directory, it should be something like:

[control.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=YEAR Integer Width 4
Col2=MONTH Integer Width 2
Col3=CONAME Char Width 15
Col4=RECCNT Integer Width 6

where u can create it from the control panel -> ODBC -> Microsoft Text Driver config.

Regards,
stm