Solved

How to import a text file via script to SQL 2005

Posted on 2010-09-09
13
468 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Viewers will learn how the fundamental information of how to create a table.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now