Link to home
Create AccountLog in
Avatar of jana
janaFlag for United States of America

asked on

How to import a text file via script to SQL 2005

We were given 2 text files for importing to MS SQL 2005 database.  One is TAB delimiter and the other is COMMA.

We know we import them via the wizard, but we are trying to create a script instead.

Both text file are the same columns:

SentTime (time format is "4/27/2010 3:49:30 PM")
ReceiveTime  (time format is "4/27/2010 3:49:30 PM")
Sender (format is text)
Subject  (format is text)
SendTo  (format is text)
Body  (format is text)

Whats the best way of importing each file?
Avatar of Ramanhp
Ramanhp
Flag of India image

create a single script

just make a check

find the presence of tab or comma for its occurance in the first line of the input file

what ever it finds first, set it as delimiter
Avatar of jana

ASKER

Can you give an example?
ASKER CERTIFIED SOLUTION
Avatar of rajvja
rajvja
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Marbleman
Marbleman

the easiest way is to create either a php or an ASP script.

You can run ASP Scripts from a DOS-Box using: cscript mySript.asp

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8


strfile = "D:\testimport.txt"

c_strConnect = "DSN=MyDBSRV;DATABASE=myDatabase;UID=myUser;PWD=myPassword;OLE DB Services = -2;"

    Set fso = CreateObject("Scripting.FilesystemObject") 
    
    Set db = CreateObject("ADODB.Connection")
    db.open c_strConnect
    
    Set ts = File.OpenAsTextStream(OpenFileForReading)

    Do While Not ts.AtEndOfStream 
		strLine = ts.ReadLine
		
		'split your line into an Array here by using the seperator
		
		aFields = Split(strLine, ";")
		
		'Build an SQLString here
		strSQL = "INSERT INTO myTable (myFieldName1, myFieldName2,....) VALUES ('" & aFields(0) & "', '" & aFields(0) & "', ....)"
		
		'For debugging purposes only:
		WScript.Echo strSQL

		
		db.Execute strSQL

    Loop

ts.close
db.close

Open in new window

Avatar of jana

ASKER

Marbleman, its MS SQL we are working in.  Thanx anyways.
Avatar of jana

ASKER

rajvja, its not working, it giving "(0 row(s) affected)".

I used the script from the link provided:

           BULK INSERT OrdersBulk
               FROM 'c:\file.csv'
               WITH
               (
                   FIRSTROW = 2,
                   FIELDTERMINATOR = '\t',
                   ROWTERMINATOR = '\t\n'
               )


The text data is separated by Tabs
Hi,

    Syntax is correct. Try again
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jana

ASKER

Your example works, but in my data it doesn't.  Here's is an example of the data attached.
OutlookEmailsTest.txt
That would be because you are using a tab delimiter and not a comma delimiter.
Avatar of jana

ASKER

Ok, so I change the script?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jana

ASKER

Thanx!