Creating a table for each of many text files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a table for each of many text files

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 12:02:41

For some reason, this says that it can't find the text files.
They are at the address supplied , however.

The temp table #FileList was created, but now I can't delete it.

Any ideas as to what I did wrong?

Also, how does one use DTS to create a template that can be
iterated over repeatedly?

Thanks

DECLARE @FileName varchar(255),
@TableCreateTemplate nvarchar(4000),
@SqlStatement nvarchar(4000)
CREATE TABLE #FileList
(FileName varchar(255))
INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX'
SET @TableCreateTemplate =
'CREATE TABLE [$(TableName)]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)'
DECLARE FileList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT FileName FROM #FileList
OPEN FileList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM FileList
INTO @FileName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
REPLACE(@TableCreateTemplate,
'$(TableName)',
@FileName)
EXEC (@SqlStatement) -- create table
SET @SqlStatement =
'BULK INSERT [' + @FileName + ']
FROM ''D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\' +
@FileName + ''''
EXEC (@SqlStatement) -- load data
END
CLOSE FileList
DEALLOCATE FileList

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-16 : 12:44:25

Instead of the EXEC (@SqlStatement), run PRINT @SqlStatement. That is how you troubleshoot dynamic sql. What does the PRINT statement show?

BTW, temp tables will be deleted if you close your session. So if you are doing this inside Query Analyzer, just close that connection and then reopen it.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 13:12:35

Tara:

It gives me the same message as before....can't find the files. Does the path in the procedure that I posted look correct or should it be altered, eg no ending slash? or the filenames are not correct, eg .txt is not being recognized? I'm poking in the dark here.

Thanks,

Paul

(2 row(s) affected)

CREATE TABLE [The system cannot find the path specified.]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)
BULK INSERT [The system cannot find the path specified.]
FROM 'D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\The system cannot find the path specified.'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-16 : 13:22:31

I don't understand your code. Why are you putting $(TableName) in the CREATE TABLE statement? When I run your code on my machine, I get device not ready error for that CREATE TABLE statement. What is $(TableName)?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-16 : 13:33:38

I am understanding your code more now. Run this inside Query Analyzer (it should only create the tables, not load data into them):

DECLARE @FileName varchar(255),
@TableCreateTemplate nvarchar(4000),
@SqlStatement nvarchar(4000)
CREATE TABLE #FileList
(FileName varchar(255))
INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b C:\temp\'
SET @TableCreateTemplate =
'CREATE TABLE [$(TableName)]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)'
DECLARE FileList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT FileName FROM #FileList
OPEN FileList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM FileList
INTO @FileName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
REPLACE(@TableCreateTemplate,
'$(TableName)',
@FileName)
EXEC (@SqlStatement) -- create table
SET @SqlStatement =
'BULK INSERT [' + @FileName + ']
FROM ''D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\' +
@FileName + ''''
--EXEC (@SqlStatement) -- load data
END
CLOSE FileList
DEALLOCATE FileList

DROP TABLE #FileList

Does it create the tables? It does for me. Just change C:\Temp\ to your path.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 14:22:04

This code came from Dan Guzman:

I think that what I don't understand here is where the files are supposed to be written to.

What does this mean?
EXEC master..xp_cmdshell
'dir /b C:\temp'

What is my path? to my MSSQL folder? to the database name?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-16 : 22:20:05

The code is actually this:

INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b C:\temp\'

You are inserting the result set of the dir command (DOS command) into #FileList temporary table. The path is to wherever your import files are located, which is D:\... in your original code. Is that where you have a set of files that you want to import the data from?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 23:33:27

Yes, the text files to be imported are at this location:

D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX
This is the error that I get...

(2 row(s) affected)

Server: Msg 4860, Level 16, State 1, Line 1
Could not bulk insert. File 'D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\The system cannot find the path specified.' does not exist.

when I use this code:

DECLARE @FileName varchar(255),
@TableCreateTemplate nvarchar(4000),
@SqlStatement nvarchar(4000)
CREATE TABLE #FileList
(FileName varchar(255))
INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\'
SET @TableCreateTemplate =
'CREATE TABLE [$(TableName)]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)'
DECLARE FileList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT FileName FROM #FileList
OPEN FileList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM FileList
INTO @FileName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
REPLACE(@TableCreateTemplate,
'$(TableName)',
@FileName)
EXEC (@SqlStatement) -- create table
SET @SqlStatement =
'BULK INSERT [' + @FileName + ']
FROM ''D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\' +
@FileName + ''''
EXEC (@SqlStatement) -- load data
END
CLOSE FileList
DEALLOCATE FileList

DROP TABLE #FileList

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-16 : 23:36:58

Is there a way to do this in DTS by creating an import for one file and then using it as a template to iterate over for the other text files?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 12:17:17

I do not believe that you can use a template in DTS through the DTS designer. I'm sure you can do it in VB though.

In order for BULK INSERT to work, the path must be valid on the database server, not on the client. Is this a valid path on the database server? To verify, run:

EXEC xp_cmdshell 'dir D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\'

What is the output of the above command?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 12:48:44

Tara, thanks for getting back to me on this one.

I'm assuming you me 'run' this in Query Analyzer...the output is
an error...

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 12:50:58

You need to be in the master database or specify master when you run it. Yes Query Analyzer.

EXEC master.dbo.xp_cmdshell...

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 13:17:07

Thank you for clarifying that...

This:
The system cannot find the path specified.
NULL

is what was returned by:

EXEC master.dbo.xp_cmdshell 'dir D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\'

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-17 : 13:20:51

Where does that path reside? On your machine? It needs to reside on the Server...

Brett

8-)

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 14:18:21

Well, it's to a partition on a local mirrored RAID disc.

I have Windows Server 2003 and SQL Server 2000 running off of the the C partition (small partition), but I did move the Data folder with the actual Database files onto the D partition (large partition).

I'm stumped as to what I'm doing wrong. It just seems like it should work, but I'm missing whatever is causing it to fail.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 14:25:11

So D:\... is on the database server? From the database server's console, go to Start..Run. Type in your path. Does the directory come up or do you get an error?

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 14:44:33

When go to:

Start
Run

and type in:
D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX
the correct folder with the files opens.

Go figure.

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 14:46:37

there is a slash at the end of this path, just as in the other example

D:\Investment Databases\Stock_Quotes\Quote_Data\AMEX\

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 14:49:50

And you pulled up the directory on the database server and not from a client machine? I keep asking because BULK INSERT requires that the files be on the database server. This is the most common mistake with it.

If it is on the database server, then create a temp directory on C on the database server. Put two text files in it, doesn't matter what is in the text files, just make the files small. Then run this:

DECLARE @FileName varchar(255),
@TableCreateTemplate nvarchar(4000),
@SqlStatement nvarchar(4000)
CREATE TABLE #FileList
(FileName varchar(255))
INSERT INTO #FileList
EXEC master..xp_cmdshell
'dir /b C:\temp\'
SET @TableCreateTemplate =
'CREATE TABLE [$(TableName)]
(
[Date] smalldatetime NOT NULL,
[Open] smallmoney NOT NULL,
[High] smallmoney NOT NULL,
[Low] smallmoney NOT NULL,
[Close] smallmoney NOT NULL,
[Volume] int NOT NULL
)'
DECLARE FileList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT FileName FROM #FileList
OPEN FileList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM FileList
INTO @FileName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement =
REPLACE(@TableCreateTemplate,
'$(TableName)',
@FileName)
EXEC (@SqlStatement) -- create table
END
CLOSE FileList
DEALLOCATE FileList

DROP TABLE #FileList

Now check on the SQL Server if 2 tables were created that have the same name as the text files you put in C:\temp.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2004-02-17 : 15:17:57

Tara:

Your code works fine.

It created new database tables with the names of the text files from the C:\temp\ directory

As for how this machine is set-up, I apologize for not being clear.

All the software is running on the same (1) machine. Client and server are on the same machine. I'm serving up ASP.NET web pages so I'm making http requests to the database. Does that help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 16:03:42

So when you run your code, what are you running it in? Query Analyzer?

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources