SCHEDULE IMPORT - SQL Server Forums

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)

SCHEDULE IMPORT

Author

Topic

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 11:32:38

Hello,

Can I please get some advice on how to go about importing a .csv file into a SQL table on a schedule? The file will be in a directory by itself or with others exactly like it. It will have a date in its name. I tried using the DTS but you have to have the exact file name and my file name changes because of the date. Also if there are more than one file in the directory, I need them both to import into the same table. I don't want the table overwritten either. Any advice would be great. Thank you all for the help that I have got in the past and thanks in advance for this too!

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 11:52:46

I just wanted to add a bit.

Lets say that I have directory F:Location with files Import102204.csv and Import102104.csv as the files to be imported. They need scheduled to be imported into in the tableX. I hope this helped. Thanks in advance again. :)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-22 : 12:07:34

quote:
Originally posted by ELLIEMAY

Hello,

Can I please get some advice on how to go about importing a .csv file into a SQL table on a schedule? The file will be in a directory by itself or with others exactly like it. It will have a date in its name. I tried using the DTS but you have to have the exact file name and my file name changes because of the date. Also if there are more than one file in the directory, I need them both to import into the same table. I don't want the table overwritten either. Any advice would be great. Thank you all for the help that I have got in the past and thanks in advance for this too!

I use VB to do this kind of task
>search for pending files to be uploaded
>construct SQL statement

myConn.excecute "BULK INSERT myDB..tblfromtxt " _ 
     & "FROM '" & myFilePath & myFileName & "' " _
     & "WITH (FORMATFILE = '" & myFormatFile & "')"

Where myFilePath is the path where new files to be processed should be, myFileName is the name of
the new file found and myFormatFile is the path and name of a file with the format that should be
use to upload the file

But you can also use the BCP utility (search on BOL) with shell from VB
or from Windows Schedule Tasks. And within the DTS maybe using
Dynamic Properties Task will help you.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 15:21:27

Thanks tuenty. I can easily do this in VB, but I was hoping that this could be done on my SQL server w/o adding another entity. I am really unfamilliar with BCP, I have seen it on here several times but I don't know much about it. I will do a search on BOL like you said. Do I have to install something to use the utility? Where is the option for the Dynamic Properites Task in DTS? Thanks again for you help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 15:30:37

If your file names are as simple as what you've specified, then I'd use BULK INSERT as tuenty showed an example of. You don't need VB though. You could do all of this in a stored procedure using xp_cmdshell.

Or you could add a VBScript task to your DTS package. The VBScript would check a directory for while files need to be processed, then move it to another directory and rename it to what the Source object is expecting.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 15:36:39

Thanks Tara. I actuall seen an example of a BULK INSERT but I didn't understand the third line. Can you explain?

BULK INSERT TableX
From 'Import*.csv' Tara, will the wildcard work like this?
With (Fieldterminator = '|')

Also, will this work on multiple files? What would I need the xp_cmdshell for? I hope I don't sound completly crazy. Thanks again! :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 16:10:10

No you can not use a wild card. You have to import each file in separately. So you'll need a loop. The xp_cmdshell part would be to find out what the file names are. xp_cmdshell can run DOS commands like dir. The third line in his example has to do with the fieldterminator. You have to tell BULK INSERT what format your files are in.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 16:20:03

Okay. I understand that. Can you give me an example loop for the xp_cmdshell? Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 16:22:24

Take a look at my backup stored procedure that uses a loop and xp_cmdshell:

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 16:31:05

Okay, I will. Thanks. I have been playing with the BULK INSERT code and I am getting this message. I have full control to the directory and DBA rights.

Server: Msg 4861, Level 16, State 1, Line 6
Could not bulk insert because file 'F:\FTP Clinical Research\CR_HBOC20041014.csv' could not be opened. Operating system error code 5(Access is denied.).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 16:34:10

Does this exist on the database server: F:\FTP Clinical Research\CR_HBOC20041014.csv?

BULK INSERT runs from the database server and not your machine.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 16:36:48

Yes. F is a drive on the server.

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 16:38:28

At first I was getting a "file not found" error until I got the file name correct. I kept leaving out the underscore, but when I got the file name correct I get this error. I can even paste the exact path into <START><RUN> and it will open the file.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-22 : 16:49:32

The problem is with the spaces in your directory. You might need to put double quotes around it. Try removing the spaces in your directory name.

Tara

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 17:25:35

Well, I put double quotes around the entire path and it still didn't work. Is there a way around re-naming the directory. It is an FTP directory that coorperate set up and it will be a major issue. I also did place the fill in a different director to see if it would run if the name was different and I got the following errors:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-22 : 17:34:53

Take a look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24371

You should be able to modify that to import all the files in the folder. I don't think putting double quotes around would matter. As far as the STREAM error, it's most likely due to line terminator character. Most FTP sites are run on Unix and Unix uses a single CHAR(10) as a line terminator, while Windows and DOS use CHAR(13) + CHAR(10). Add a ROWTERMINATOR='\r' to your BULK INSERT statement and see if that fixes it.

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-22 : 17:55:06

That code works great, but it doesn't find the file with the spaces in my directory. Even when I just use this:
CREATE TABLE files (filename varchar(128))
INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b f:\FTP C R\*.csv'

I can see the table in Enterprise mgr and the only record says: The system cannot find the file specified.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-22 : 18:03:08

quote:
Originally posted by ELLIEMAY

Thanks tuenty. I can easily do this in VB, but I was hoping that this could be done on my SQL server w/o adding another entity. I am really unfamilliar with BCP, I have seen it on here several times but I don't know much about it. I will do a search on BOL like you said. Do I have to install something to use the utility? Where is the option for the Dynamic Properites Task in DTS? Thanks again for you help.

the Dynamic Properties Task is one of the Task options within a DTS but I have never use it so there
is not much I can tell you about it

From BOL

Dynamic Properties Task: Package Properties
Use this dialog box to view and select a property to be set dynamically.

The left pane displays a tree view of Data Transformation Services (DTS) package properties. Expand the appropriate nodes to navigate to the group containing the property whose value you want assigned dynamically.

The right pane lists the names and default values of the properties belonging to the property group selected in the left pane.

Options
Add

After selecting a property from the right pane, click to display the Add/Edit Assignment dialog box, which you use to make the property assignment.

Close

Return to the Dynamic Properties Task Properties dialog box.

Leave this dialog box open after adding a setting

Make multiple property assignments at a time. If this box is cleared, the Add/Edit Assignment dialog box is closed after a setting is added, and the Dynamic Properties Task Properties dialog box is displayed.
=================================
But I think Taras sugestion of using the xp_cmdshell is better

but I think you will have to use
dynamic SQL to join the bulk insert instruction with the new files to be updated. I have try to
use variables for the file to be imported and the format file and I allways get an error
maybe I'm doing it wrong or maybe a dynamic SQL is required for that.

In my case I receive many files from different clients and VB is the front end application so an
operator that can decide wich files are going to be run and see the progress for every process being
run on the file(s) just to make the process Moron Proof. What I like of the Bulk Insert is
that even when not all the files have the same format I can upload all the files to the same table
by changing the format file (I have a table where I store the client code, the path where the client
files reside and the format file to be used to upload the data)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-22 : 18:20:05

Change this:

INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b f:\FTP C R\*.csv'

To:

INSERT INTO files (filename) EXEC master..xp_cmdshell 'dir/b "f:\FTP C R\*.csv"'

DOS does require double quotes around a pathname that contains spaces.

And tell your corporate IT group, or whoever created that folder, that they're a bunch of morons for using spaces in the name.

ELLIEMAY
Starting Member

43 Posts

Posted - 2004-10-25 : 11:27:10

Thanks again! I did get it to work, but it was a bit flaky until I used some fake data in a fake directory w/o spaces. I am requesting that the people who created it and setup the FTP eliminate the spaces in the directory for me to move further on the project. Thanks again SO much for you help. I will be back if I have other issues. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources