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 |
|
nomadsoul
Yak Posting Veteran
89 Posts |
 Posted - 2006-11-18 : 19:41:40
|
| Hello eveyone I want to do a bulk insert on a large text file. I've read Garth's article. His delimiters are commas or quotes. What the file has not commas or quotes? Can I use spaces?Here is what he wrote:BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')Thanks.It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-18 : 19:53:05
|
You can use any char you like for the delimiter.You could use a space I guess, but I wouldn't recommend it if there is text data in the bulk load file. what happens if one of the values has a space in it? bulk insert would fail. seems to me like that would be a pretty common case.I usually use a tab char as a delimiter, which is the default for both bcp.exe and BULK INSERT.
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
 |
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-18 : 20:03:32
|
| Jezemine:Heres a very small sample of the script, it is a class schedule that I dloaded from a University websiteand the name ALEXANDER, S is acutally the 7th column. Any suggestion is appreciated. My reply is not in table form here of course but imajine the fields them being in a proper table pleaseACCT 2003 01 ACCOUNTING PRINC I 10:00AM - 10:50AM MWF COR 103 ALEXANDER, S ACCT 2003 02 ACCOUNTING PRINC I 9:30AM - 10:50AM TR COR 114A CARR, P ACCT 2003 03 ACCOUNTING PRINC I 11:00AM - 11:50AM MWF COR 103 ALEXANDER, S ACCT 2003 04 ACCOUNTING PRINC I 11:00AM - 12:20PM TR COR 103 GOZA, N ACCT 2003 05 ACCOUNTING PRINC I 8:00AM - 9:20AM TR DN 105 GOZA, N ACCT 2013 01 ACCOUNTING PRINC II 9:00AM - 9:50AM MWF DN 201 GOZA, N ACCT 2013 02 ACCOUNTING PRINC II 10:00AM - 10:50AM MWF DN 104 BACHMAN, V ACCT 2013 03 ACCOUNTING PRINC II 9:30AM - 10:50AM TR DN 104 MCKNIGHT, C ACCT 2013 04 ACCOUNTING PRINC II 11:00AM - 12:20PM TR DN 103 MCKNIGHT, C ACCT 2013 05 ACCOUNTING PRINC II 5:30PM - 6:50PM MW COR 114A BACHMAN, VACCT 3013 01 INTERMEDIATE ACCT II 11:00AM - 11:50AM MWF DN 104 MCKNIGHT, C ACCT 3023 01 ACCOUNTING INFO SYSTEMS 12:00PM - 12:50PM MWF COR 117 BACHMAN, V ACCT 3053 01 FEDERAL TAXES II 11:00AM - 12:20PM TR COR 117 CARR, P ACCT 3063 01 MANAGERIAL ACCOUNTING 1:00PM - 1:50PM MWF COR 103 GOZA, N ACCT 3063 02 MANAGERIAL ACCOUNTING 9:00AM - 9:50AM MWF DN 104 BACHMAN, V ACCT 4013 01 ADVANCED ACCT II 1:00PM - 1:50PM MWF DN 105 ALEXANDER, S It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-18 : 20:41:02
|
so here you certainly could NOT use ' ' as a delimiter, because 'ALEXANDER, S' has a space in it.I think you'll have to format the file yourself, using a delimiter such as '|'for example:ACCT 2003|01|ACCOUNTING PRINC I|10:00AM - 10:50AM|MWF|COR 103|ALEXANDER, Shere I am just guessing where the delimiters go. you said there were a total of 7 cols, so I put 6 delims in there :)
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-18 : 23:08:33
|
| Yea I was wondering how to deal with that. ThanksIt is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-18 : 23:36:12
|
however you deal with it, you'll have to deal with it *before* you use bcp or BULK INSERT, because your file is not delimited currently. Maybe the easiest way would be to load it into excel and then merge cells, then save it as a tab-delimited file from excel. this you could bcp no problem. don't ask me how to merge cells though, i am an excel idiot. I think it's possible though 
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-18 : 23:42:04
|
| Ok I'll try that. It may take me awile cause I also am not an excel whiz, thanksIt is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
|
|
|
|
|
Bulk Insert Question