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 |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2002-08-06 : 03:09:59
|
| Hello ,I want to know how can i can use the bcp coomand to import a text file into a SQL database table . The text file contains info. like servername , date , time , logininfo and description . The problem is i cannot import that file into the table through data import wizard . The wizard is not able to separate the fields properly and the import takes place with mixed up data in different columns . The separator in the text file is colon . I even tried fixed length with manual column separation . The text in the text file are not aligned properly as these are log files generated by the system which i am trying to import in a table .So how can use the bcp command to specify the column length properly and seperate the fields uniformly so that the data gets imported neatly . Any thoughts how could the command look like ? Thank you very much . |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 07:32:27
|
| If you can post about a dozen rows from the file as an example it would make it a lot easier. If all of the columns are delimited and there is a row delimiter, the length of each row won't matter to bcp. |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2002-08-06 : 09:06:20
|
| Thanks for your reply . The text looks like this :SQL_SERV1: 08/05/2002 00:10:13 Iscan-maild[279]: Message from: <sqlmail@servercentral.com> The text is seperated by a delimiter and when i try to import through the import wizard , the time gets split into separate columns as the time field too caontains colon as separator . I want to put SQL_SERV1 , date , time , Iscan_mailID and message from the mail address ,in seperate Columns . I need help to write a bcp utility to import a text like this in my table. Thanks once again . |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 09:10:19
|
| Why not use a space as your column delimiter? Or ": " (colon-space)? Don't use a colon by itself.The other option is to import from the text file into a staging table with one column, and have bcp import the entire row as a single column. You can these use SubString() to parse out the data into the final destination table. This also gives you the opportunity to check for invalid data without throwing an error during the import process. |
|
|
|
Ash
Starting Member
1 Post |
Posted - 2002-08-06 : 12:58:05
|
| Hi ,Extremely thanks for your response . Your solution worked but it merged the three fields into one column . i.e the date , time and scan-maild . It looks like this Col00208/05/2002 00:10:13 Iscan-maild[279]: In such case how can i query the table to get the details of Iscan-maild[279] for e.g Select * from table where col002 = 'Iscan-maild[279]:'Basically to seperate these three fields . Is it possible . If i give only space as the delimiter then i have couple of messages like :SDE00121: 08/05/2002 00:32:04 Iscan-maild[279]: Forwarding mail to sql_serv1@sqlcentral.com to cmpq@support.com at port 25 This separates out the entire text and puts every word into sepearate columns .Any further suggestions ?Thanks . |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-06 : 13:34:09
|
| Import it into a staging table without column delimiters, then use SubString to parse out the data. Since the first half is pretty much fixed-length this is probably the easiest way to go. |
|
|
|
|
|
|
|
|
BCP utility