Solved

How to import a text file via script to SQL 2005

Posted on 2010-09-09
13
472 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:rayluvs
  • 6
  • 2
  • 2
  • +3
13 Comments
 
LVL 9

Expert Comment

by:Ramanhp
ID: 33634384
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
0
 

Author Comment

by:rayluvs
ID: 33634421
Can you give an example?
0
 
LVL 11

Accepted Solution

by:
rajvja earned 167 total points
ID: 33634435
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 3

Expert Comment

by:Marbleman
ID: 33634542
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

0
 

Author Comment

by:rayluvs
ID: 33634569
Marbleman, its MS SQL we are working in.  Thanx anyways.
0
 

Author Comment

by:rayluvs
ID: 33634659
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
0
 
LVL 11

Expert Comment

by:rajvja
ID: 33635071
Hi,

    Syntax is correct. Try again
0
 

Assisted Solution

by:Sachid_Singh
Sachid_Singh earned 167 total points
ID: 33635267
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
csv1.gif
0
 

Author Comment

by:rayluvs
ID: 33635344
Your example works, but in my data it doesn't.  Here's is an example of the data attached.
OutlookEmailsTest.txt
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33653646
That would be because you are using a tab delimiter and not a comma delimiter.
0
 

Author Comment

by:rayluvs
ID: 33653682
Ok, so I change the script?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 33653824
At the very least you would have to change FIELDTERMINATOR to \t or skip it entirely as it is the default field terminator.
0
 

Author Comment

by:rayluvs
ID: 33689425
Thanx!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question