| Author |
Topic |
|
cshah1
Constraint Violating Yak Guru
347 Posts |
 Posted - 2005-12-07 : 17:19:52
|
| I need to import all the text files from a directory and import its content into SQL Server table. (need to loop through the entire directory). SQL Server Table StructureFileName FileContentFile1 <file1Content>File2 <file2content> |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-10 : 08:09:23
|
| how to include file name when you use BULK INSERT? I want to load the data from the text file in filename and <filecontent> fields? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-10 : 09:35:47
|
| This might help:http://weblogs.sqlteam.com/robv/archive/2003/09/28/181.aspx |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-10 : 14:42:56
|
| Rob, Thans a lot! One thing I ran this in the query analyzer exec xp_cmdshell 'for %a in (c:\temp\*.txt) do bcp mydb..mytable in %a -T -SServerName\SQL -c'it is able to copy all the files from the folder into the table but I also want to insert filename in a separate column..I am not able to understand %a part..Yesterday..I spent several hours figuring out while some of the files were not able to import when you use BULK INSERT (if file size in bytes is divisible by 4) BULK INSERT skip those files.. worse part it I looked into it files and try to figure it out is something inside the files that is preventing it from importing into the SQL..finallly I found Jasper Smith's (SQL Server MVP) remark about file size divisible by 4 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-10 : 15:46:31
|
| Try this one too for some ideas:http://weblogs.sqlteam.com/robv/articles/4107.aspxYou could do something like:CREATE TABLE #files(filename varchar(128) null)INSERT #files(filename) EXEC master..xp_cmdshell 'dir/b c:\temp\*.txt'DELETE #files WHERE filename IS NULL OR filename='File Not Found'This will import the filenames into a table. |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-10 : 16:44:40
|
| my last question is..what should i use as row and filed terminator, those text files has a chuck of data..I tried (\0) as row terminator but it is not working..instead of it is putting every line in a new row in the table***********************some text some textsome text some text************************* |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-10 : 16:53:07
|
| What's a "chuck" of data? Do you mean the file contains data with regular line breaks in it? |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-10 : 17:10:27
|
| Yes, chunk of data with regular line breaks..is there anyway to know eof (end of file) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-10 : 17:15:03
|
| If the line of data began with a specific character, perhaps. Can you post an example of some of the data in the file? |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-10 : 17:40:17
|
| Ignore dashes in the following file some flles may have spaces in the beginnig...I sincerely appreciate all your help..I imported those text files from word documents..reason for doing it is searching we wanted to search contents of the word documents searchable in our application...---------------------D16052 This policy is bidning to allapplicatble terms and conditions listed above. See amendment VI for further information----------------------------------------. |
|
|
|
|
Importing Text Files