| Author |
Topic |
|
Cedricsan
Starting Member
16 Posts |
 Posted - 2001-12-18 : 07:24:48
|
| HI,I am trying to load a fixed width text file, without using a DTS Package. I can't find a way to do it with BULK insert .Our aim is an automated process for loading and transforming this file. Everything else but the load/import is sorted!Am I missing something?I have a table which contains the file layout and the raw file : What can I do?RegardsCedric |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-18 : 09:28:27
|
| I haven't tried fixed-width files with bcp or BULK INSERT, but I'm positive it can be done using a format file. Books Online has excellent documentation on bcp format files, and I believe had an example using a fixed-width data file. Read all of the bcp, BULK INSERT, and format file documentation that's there; it's scattered around a bit but it's in there. Play with bcp a little, let it generate the format file for you, then tweak it as needed (this is an easy and time-tested method that gives excellent results). Meanwhile I'll see if I can get an example together and post it here.If not, why not DTS? |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-18 : 09:55:29
|
quote:
If not, why not DTS?
Remind me how you write a program that creates a DTS package again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-18 : 10:15:48
|
| Have you looked at the DTS COM object model? It's what the DTS designer is based on. Coupled with VB, VBScript or C code, it could easily create an automated data loading & transforming program.Books Online has an entire section on DTS under the Contents tab. There are code examples and a full object model map if you're interested. I agree that this is probably overkill for what you need, but it's available.BTW, if the text file format doesn't change, and the transformations don't change, once you set up the package you're pretty much done. Is there more to it than that? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-18 : 11:53:33
|
| And if the data source isn't on a platform that talks COM? What, build another database for the data source's metadata? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-18 : 13:38:07
|
| Arnold, are you just trying to pick a fight? Or show that "yours is the superior intellect"? Or are you looking for a solution to Cedricsan's problem?-------------------It's a SQL thing... |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-18 : 13:49:50
|
quote:
And if the data source isn't on a platform that talks COM? What, build another database for the data source's metadata?
Actually I believe it does not matter whether or not the data source platform can talk COM... As long as the machine that is going to run the package (typically SQL box itself) can talk COM and has OLEDB or ODBC drivers for the said datasource, a DTS package should be able to connect just fine.Edited by - izaltsman on 12/18/2001 14:23:32 |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-18 : 14:09:30
|
quote:
Arnold, are you just trying to pick a fight? Or show that "yours is the superior intellect"? Or are you looking for a solution to Cedricsan's problem?
Certainly wasn't intended to start a fight. But no, I didn't have anything to add to Rob's comments about bcp format files and BULK INSERTs, so it wasn't helping Cedric's problem. I just don't see that COM is an entirely appropriate interface for programmatically building things designed to import data. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-18 : 15:35:28
|
| I don't see what the problem is with using a COM solution, especially if no other option is available, or practical. I'm never one to make a situation more complicated than it needs to be, I prefer to use BULK INSERT and bcp when importing text files myself. My point is that there are some limitations to these utilities, especially in the area of data transformations. This is EXACTLY what DTS is designed to do, why not use it?Secondly, if you are able to use bcp or BULK INSERT, you must have the text file available on the server's hard drive, or through a UNC path. This is no different from the requirements needed for DTS; in fact, DTS has an advantage with the FTP transfer task. By meeting the drive requirement, the question of a COM compliant platform goes away, as Ilya mentioned (in an indirect way).I don't want to get a debate/war going on DTS, but I don't think it helps to summarily dismiss or trash it. It's an incredibly powerful tool, and could very well be the best answer to the problem. If it works, then it's the right way to do it. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-18 : 16:31:38
|
Sorry, I'm really not expressing myself well today. For now, suffice it to say that I wasn't trying to trash DTS on the basis of its functionality, and that, yes, it may well be the best tool available in SQL Server for the job.Thinking about it, what I can't get my head around is that, in contrast to SQL DDL, say, there is no native format that expresses the contents of a package in a way that is transparent. You can write a program that constructs a Visual Basic file that itself builds the package, but that's another layer of indirection.Is any of this making any sense?  |
|
|
|
Cedricsan
Starting Member
16 Posts |
Posted - 2001-12-19 : 04:06:48
|
| Hi all,Thanks all for the comments.Just wanted to point out that I am a starter.Sorry but I have a very very limited knowledge of C, VB, COM...We are currently using a DTS package to load the data and I'd prefer not to as the file layout change every month. And I have to point out manually where the limits are for the 240 odd fields in the text file.So I will investigate both the DTS and Bulk Insert. Thanks allCedric |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 13:33:23
|
| Cedric,First, we're glad you stopped by, and hope you come back. There is a lot of good information on this site for people of ALL knolwedge levels. When you get some time, browse the articles here. There is a wealth of info in there.Second, do I understand you to say that within the file you are trying to upload, the fields are a constant fixed-length, but each month the lengths change? Wow! What a pain that must be. Is that because this is a new process still being developed and ironed out? Do you foresee it levelling off, or is there something inherent in the process that will keep it fluctuating. If it's changing that often, I hope you can find a repeatable process to build your format file for BCP.An alternate approach, which I don't think anyone has mentioned yet, is to BCP or Bulk Insert the table into a holding table in SQL where each row is inserted into one large field. Then, you can use string manipulation to pull it out of the holding table and put it into the final table. String manipulation in SQL is not necessarily fun, but once you have it built, save your command as a sproc or script file and then the next time, it should be relatively easy to change the numbers and run it again.Good luck!-------------------It's a SQL thing... |
|
|
|
|
Importing a fixed width text file