Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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?
0
rayluvs
Asked:
rayluvs
  • 6
  • 2
  • 2
  • +3
3 Solutions
 
RamanhpCommented:
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
 
rayluvsAuthor Commented:
Can you give an example?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
MarblemanCommented:
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
 
rayluvsAuthor Commented:
Marbleman, its MS SQL we are working in.  Thanx anyways.
0
 
rayluvsAuthor Commented:
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
 
rajvjaCommented:
Hi,

    Syntax is correct. Try again
0
 
Sachid_SinghCommented:
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
 
rayluvsAuthor Commented:
Your example works, but in my data it doesn't.  Here's is an example of the data attached.
OutlookEmailsTest.txt
0
 
Anthony PerkinsCommented:
That would be because you are using a tab delimiter and not a comma delimiter.
0
 
rayluvsAuthor Commented:
Ok, so I change the script?
0
 
Anthony PerkinsCommented:
At the very least you would have to change FIELDTERMINATOR to \t or skip it entirely as it is the default field terminator.
0
 
rayluvsAuthor Commented:
Thanx!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now