IMPORTING AN ASCII TEXT FILE DAILY
-
Hi,
I receive an ASCII text file daily that contains the current stock levels from our parent company, (CSV type delimited by ~). Is it possible to write a SQL script that reads this file daily (Scheduled Job) and populates an existing SQL table, deleting the previous days contents ?
How you can help...
Cheers
Mike
-
Hello Mike,
quote:
I receive an ASCII text file daily that contains the current stock levels from our parent company, (CSV type delimited by ~). Is it possible to write a SQL script that reads this file daily (Scheduled Job) and populates an existing SQL table, deleting the previous days contents ?it might be a good idea to use the DTS Import/Export Wizard at first. I think on its last page you can save the process as DTS package. If it works fine, you only need to schedule your package on a regular basis.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My first post - how exciting.
I'm not a DBA - just a superuser.
We had exactly this same problem. Our client provides a work order system that we HAVE to use. We can extract a CSV from it.
We use some VB to parse the records on the way. The client system has some field rules that don't fit our field rules ALL of the time. We dump whatever we can into a table in the database specifically for this purpose. There's a trigger on this table that puts the data where it really belongs.
It could be a DTS in our case, but the transformation would include the same VB code as we're using now.
Dunno whether this helps as such...
-
We do this sort of thing reasonably often and I agree with Frank that using DTS is the way to go.
-
Hello sbuckby,
quote:
We had exactly this same problem. Our client provides a work order system that we HAVE to use. We can extract a CSV from it.
We use some VB to parse the records on the way. The client system has some field rules that don't fit our field rules ALL of the time. We dump whatever we can into a table in the database specifically for this purpose. There's a trigger on this table that puts the data where it really belongs.
It could be a DTS in our case, but the transformation would include the same VB code as we're using now.
as with all things in life there is more than one way you can go. In most cases it might be the right choice to pull data into SQL Server using DTS, and then there are cases, where it is more appropriate to push data into SQL Server from outside. For either way, if it is efficient it's ok.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here is my two cents:
Whenever a system receives data from External sources, it is prudent to do a validation (sanity check) on the data before loading it to your production database.
I have not used the DTS service. Does it have this capability? If not, I think, you will be better off using a program as sbuckby did.
What do others say??
Ram
-
Hello r_achar,
quote:
Whenever a system receives data from External sources, it is prudent to do a validation (sanity check) on the data before loading it to your production database.validation always makes sense. You can write more or less huge DTS packages that can validate everything you want.
However, it depends on how you define 'external'. If you mean from outside your company, validation becomes more important as from inside your company.
Another aspect is how the external data is created. If it is done via some automated interface, less validation can be applied as when some user manually hack in some Excel spreadsheets.
Another classical case of: 'It depends on your situation'
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you all for your replies.
Greatly appreciated.
Using the DTS packages is it possible to delete (or drop) the contents of a table before importing the data or is this best to do in a SQL script, scheduled to run prior to running the DTS job ?
Cheers.
Mike
-
Thank you all for your replies.
Greatly appreciated.
Using the DTS packages is it possible to delete (or drop) the contents of a table before importing the data or is this best to do in a SQL script, scheduled to run prior to running the DTS job ?
Cheers.
Mike
-
Hello Mike,
quote:
Using the DTS packages is it possible to delete (or drop) the contents of a table before importing the data or is this best to do in a SQL script, scheduled to run prior to running the DTS job ?Sir, yes it is, sir
. This option is one of the pages in the wizard
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi
Your problem is quite common. I've found using DTS you can do most things you'll want to. These include validating the data, clearing down tables,appending data or updating existing records. DTS has the capability of using VB script, SQL statements, dynamic properties, FTP jobs and you can always access other objects such as the file system objects for moving the imported files once imported.
Books on-line is a good starter!
N.B. how complex the result is depends on your systems and the imported file!
Hope it helps
Regards
Keith Davies
IT Consultant
Regards
Keith Davies
IT Consultant
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply