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.
| Author |
Topic |
|
Genisy
Starting Member
2 Posts |
 Posted - 2006-07-25 : 15:57:17
|
| Hi,Am trying to import about 70 text files (all are in similar format) where there is no tab deliminator/comma seperator. Rather, the files can only be seperated into columns by character size. For instance, column 1 is 5 characters, column 2 is the next 4 characters etc. How can i do this in DTS? PS. This will be a regular monthly import.Please help! |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-25 : 17:53:52
|
| You can specify whether the file is fixed width or delimited (by commas, etc)Try running the Import Data wizard from Enterprise ManagerHTH,Tim |
 |
|
|
Genisy
Starting Member
2 Posts |
Posted - 2006-07-26 : 05:34:16
|
| Afraid the text file is fixed width for the first half and then deliminated with "|" for the second half.... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-07-26 : 06:02:49
|
| I just did this exact thing for testing purposes and as long as you have the column lengths you are fine. DTS will slow things down considerably so I recomend using BULK INSERT instead. What I did was this:1. Create a staging table with one single column wide enough to fit the entire row of data2. Insert the file into the staging table using BULK INSERT (fast as he**)3. Create a SELECT-statement with one SUBSTRING for each column in the target table like this:INSERT INTO targettableSELECT SUBSTRING(stagingcolumnname, 1, 5) AS Column1,SUBSTRING(stagingcolumnname, 6, 4) AS Column2,...FROM stagingtable--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Crazy Text file to SQL Server