| Author |
Topic |
|
WalkerDA
Yak Posting Veteran
61 Posts |
 Posted - 2004-06-18 : 14:57:57
|
| Is it possible to automate DTS to load files and dynamically assign the file names? Or on the other hand can you somehow through BULK INSERT or BCP tell it to ignore the first line of a file on insert?Basically I have many csv files that I need to import though the first row of each file has the column names in it..I don't really need these so how can I ignore them or how could I tell SQL to accept them as the column names?Any help?Derrick |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-18 : 15:01:39
|
| Yes you can ignore the first row in BULK INSERT and BCP. You just need the -F switch for bcp and FIRSTROW option for BULK INSERT.Tara |
 |
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-18 : 15:05:53
|
| Thanks..life saver!Derrick |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-18 : 15:33:37
|
| Hey Tara, any idea how I would handle a column with no data? For instance I have 25 columns in a csv file though the last column there is no data for much of it, SO ON BULK INSERT I keep getting a TYPE MISMATCH.. For some reason it is trying to insert the comma into a numeric column..works fine when there is data, and doesn't when there is none.Derrick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-18 : 15:36:19
|
| Is your file comma separted? It should have , at the end and then a CHAR(13) and CHAR(10). If you have that, BULK INSERT should work fine.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-18 : 15:38:13
|
With a format file...just 0 out the missing columns7.0 18 1 SQLCHAR 0 14 "" 1 EmployeeID 2 SQLCHAR 0 20 "" 2 LastName 3 SQLCHAR 0 10 "" 3 FirstName 4 SQLCHAR 0 30 "" 4 Title 5 SQLCHAR 0 25 "" 5 TitleOfCourtesy 6 SQLCHAR 0 26 "" 6 BirthDate 7 SQLCHAR 0 26 "" 7 HireDate 8 SQLCHAR 0 60 "" 8 Address 9 SQLCHAR 0 15 "" 9 City 10 SQLCHAR 0 15 "" 10 Region 11 SQLCHAR 0 10 "" 11 PostalCode 12 SQLCHAR 0 15 "" 12 Country 13 SQLCHAR 0 24 "" 13 HomePhone 14 SQLCHAR 0 4 "" 14 Extension 15 SQLCHAR 0 0 "" 0 Photo 16 SQLCHAR 0 0 "" 0 Notes 17 SQLCHAR 0 14 "" 17 ReportsTo 18 SQLCHAR 0 255 "\r\n" 18 PhotoPath Brett8-) |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-18 : 16:05:07
|
| The problem was it that the number of columns in the file itself varied..something that will need to be fixed! So in some rows there were 26 columns in others there were 25. Weird!Derrick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-18 : 16:07:14
|
| If there were 25 and the missing one was in the middle, then this should have appeared ,,. If you don't have a column delimiter of some sort, then SQL doesn't know that the column is skipped. So make them give you a file in a specific format.Tara |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-18 : 17:25:28
|
| And that's how the file appeared. So the rows with values had 24 ,, the rows without values had 25 ,,,, which wasn't very easy to find. There should always be definate total from here on out.Derrick |
|
|
|
|
Automating DTS