Solved

How to import a text file via script to SQL 2005

Posted on 2010-09-09
13
471 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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