| Author |
Topic |
|
RIKIL
Starting Member
20 Posts |
 Posted - 2010-11-10 : 14:42:50
|
| I have a simple statement to do a bulk insert that is not working:BULK INSERT NACHA_File FROM 'c:\wnacha' WITH ( MAXERRORS = 0, ROWTERMINATOR = '\n\r' )The problem is that this runs without errors (result I get back is: (0 row(s) affected)). I have no idea why the statement is not inserting the file into my ntext defined field. This file is a text file and the format of the file is called NACHA. I am not sure what I am doing wrong and also not sure what to correct since I am not getting any errors. Does anyone have experience where they can advise me? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TimSman
Posting Yak Master
127 Posts |
|
|
RIKIL
Starting Member
20 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
RIKIL
Starting Member
20 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-10 : 18:01:34
|
| try ROWTERMINATOR = \nand without the quotes. |
 |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-10 : 18:22:43
|
quote:
Originally posted by russell
try ROWTERMINATOR = \nand without the quotes.
Here is what I tried:BULK INSERT NACHA_File FROM 'c:\wnacha' WITH ( MAXERRORS = 0, ROWTERMINATOR = \n )Result:Msg 102, Level 15, State 1, Line 13Incorrect syntax near '\'.Msg 208, Level 16, State 1, Line 1Invalid object name 'nacha_file'.I have also tried the following, with quotes: \n, \n\r, \r\n, \r |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-10 : 20:39:21
|
| [code]BULK INSERT DatabaseName..NACHA_File FROM 'c:\wnacha' <-- no file extension?WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', <-- what are u using for a field terminator? this is a tab. ROWTERMINATOR = '\n');[/code]sorry, was a mistake about removing the quotes. |
|
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 10:13:53
|
quote:
Originally posted by russell
BULK INSERT DatabaseName..NACHA_File FROM 'c:\wnacha' <-- no file extension?WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', <-- what are u using for a field terminator? this is a tab. ROWTERMINATOR = '\n'); sorry, was a mistake about removing the quotes.
Correct...no file extension. I tried adding one but didn't make a difference.Why does SQL need to know where my row ends and what the field terminator is? I need to put this into the table as is, with no modifications. I was hoping SQL would take it and insert it into the table byte for byte.And no, the field terminator there doesn't work. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 11:05:54
|
quote:
Originally posted by X002548
are you asking why a load program needs to know what the delimiter is?Oh, what is the delimiter?Can you post some samples rows?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
Yeah, I do know what a delimieter is but I am not sure why SQL needs to know about it. I am not opposed to giving SQL what it needs but, again, I need to have this file stored in the DB and not be modified at all. I am concerned with providing SQL the delimeters because that makes me think that SQL would change my file in some way.Unfortunately I am not able to provide a sample of the file, it contains real financial data. I wouldn't even know what to change in the file to make it clean. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 12:44:27
|
| Well I got it working. I had 3 columns in this table, none were required. When I modified the table and removed the 2 columns I am not using now, the file imports.So now my question is how do I bulk insert a file and populate data in other columns of the same table? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 14:33:11
|
quote:
Originally posted by X002548
so I guess there's one column of data?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
Yes, now there is only 1 column in the table. I originally had 3 columns because I wanted to insert this file into one column, have a date timestamp in another, and a number (which I provide) to be put in the last column. Any idea if bulk insert can be used with some sort of INSERT statement to get what I need? I haven't found a way to get that yet. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 15:52:10
|
quote:
Originally posted by X002548
how many delimited field were in your file?I'm Guessing 1AND WHAT'S THE DELIMITER...or don't you have oneHereFile.txt1|2|45|6|7CREATE TABLE myTable99(Col1 int, Col2 int, Col3 int)GOexec master..xp_cmdhell 'bcp myDB.dbo.myTable99 IN File.txt -Sservername -T -t"|" -c'Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
There could be several fields in the file, depending on the row. As I mentioned above, this file has a couple of file formats embedded in the file. There is a section of data at the beginning of each row that indicates what format that row is in. There are 80 characters that data can be stored on each row, this 80 characters can be one field or many fields depending on the type of data in that row. Yes, I know, it's a retarded format.The only thing that is consistent is that each row terminates at 94 characters. I don't have a field delimiter that I am using with the BULK INSERT, I use only a row delimiter. At this point I can get the file to import but now I need to expand it so that at the same time I import the file to the table, I also need to populate some other columns. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RIKIL
Starting Member
20 Posts |
Posted - 2010-11-12 : 17:43:17
|
quote:
Originally posted by X002548
not retarded..sounds like a mainframe fileHere's what you do (and you still haven't told me the delimited)Create a 1 Column Staging table with a column varchar(max)Then load the file into that...THEN Based on the Row Type, you build a sproc to manage each "thing" (a thing being a row of data)If you can post any data with the various row types (just make up something) we can hook you upBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
Sounds like you are just barely missing what I am trying to do.I want to load the file into a field as you mentioned ("Create a 1 Column Staging table with a column varchar(max)...Then load the file into that...")If you stop there for a sec, you will see that I would have the file loaded into one cell of one table. This is what I am after. The other columns in the table will hold other data, like a timestamp and a CRC of the file. So the table should look like this:column 1 = entire file without modification (no field delimeter, row delimeter = '\n\r')column 2 = date and time of when the file was importedcolumn 3 = CRC or some sort of hash of the file (so I can prove that when the file is exported, it wasn't modified from when it was imported).When you say "THEN Based on the Row Type, you build a sproc to manage each "thing" (a thing being a row of data)" that makes me think that I need to take apart the file, row by row. I don't want to do that, I want to store the file in the table in its entirety and NOT take the file apart into smaller bits OR modify the file in any way.I hope that made sense. So I can get the file into the table as I want it IF I have only one column in the table using this statement:BULK INSERT NACHA_File FROM 'C:\wnacha' WITH ( MAXERRORS = 0, ROWTERMINATOR = '\n\r' )Now that I have this part working I need to know how to get this to work with a table that has more than one column AND where I can populate those other columns at the same time.I realize this SQL won't work but this is the idea of what I am trying to do:INSERT INTO NACHA_File (file_blob, time_inserted, CRC)('WNACHA', getdate(), 'B7C7DB5770F0D977E2C915B793212EFD1AE0B4B3') |
|
|
|
|
Bulk Insert