The following produces no errors, but does not place any rows in the table:
USE MoneyManager
GO
BULK INSERT DailyPrices
FROM 'C:DownloadsSecuritiess20070105.dat'
WITH
(
firstrow = 2,
formatfile = 'C:DownloadsDailyPrices.fmt',
errorfile = 'c:downloadsprice_error_file.txt'
)
select *
from DailyPrices
Hi,
I have a data file in the folloing format
SubjectId1|class1
SubjectId2|class2
SubjectId3|class3
I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes
The row delimeter is "
" and the column delimeter is ' | '
Table Subjects
{
ID (Autoincrement)
SubjectId varchar(20)
}
Can any one provide the format file for doing this or suggest anyway to do this?
Please do note that the file may contain millions of records
Thank u
~mohan
Hello Everyone,
I am currently struggling with a problem bulk inserting data into sql server.
The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).
What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.
1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.
2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).
3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).
Does anyone have any idea's on a cpu friendly approach to this problem ??
Thanks in advance..
Gary.
Hi,
hope this is the right forum for this...
I'm using asp.net and the SqlBulkCopy command to insert a few lines of data into a table. I have the SqlBulkCopyOption.FireTriggers set, and a simple trigger on the table to take the inserted data and update a couple of other tables. Works fine if I only insert one record, but as soon as there are multiple records it fails.
So I presume that the trigger isn't firing on every line, but only once when the bulk insert is done?
Am I missing a trick? I can post some code if it will help - I'll have to get a screengrab of the error too.
cheers
Hi at all,I'm trying to bulk insert a uniqueidentifier column from unicode file.In my file I have guid generated from c# application and they areformatted in this way (separated by "|") :guid | field1 | field2fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2In my destination table I have three column:id (uniqueidentifier)field1 (nvarchar)field2 (nvarchar)I use in bulk insert a format file like this :9.031SQLNCHAR00"| "1IDLatin1_General_CI_AS2SQLNCHAR00"| "2Field1Latin1_General_CI_AS3SQLNCHAR00"| "3Field2Latin1_General_CI_ASand I use this scriptBULK INSERT [dbo].[KWTA2] FROM 'd:WTA2.txt'WITH (FORMATFILE = 'd:wta2Format.FMT')It doesn't work, it prints outMsg 8152, Level 16, State 13, Line 2String or binary data would be truncated.I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHARand it works perfectly but it imports another data. For example theguid fc0c0c42-438e-4897-96db-8b0489e873ef became00350031-0039-0033-3100-300030003000Please can you help me?Why sql converts alphanumerical GUID into only numbers ID?How can I bulk insert GUID? (I didn't find anything googling around : )Thanks!Bob
View 2 Replies View RelatedHi Andrea,
I have made a table which contain data inserted manually and also data that was inserted by using bulk insertion. I have no problems using the table with Grid View. But when I try to use a query like the following:
SELECT *
FROM dbo.last
WHERE VMake = 'Honda'
AND VType = 'sedan'
AND VColor = 'Red';
I would only get the data that were inserted manually.
When I use the same query to filter data from a table that the data was inserted by using bulk insert, I get column names but no data.
Any help please.
Juvan
Hi,
I am using SQL 2k5
I have a table with the follwing schema
Table BulkTest
(
ID nvarchar(100),
SubDate Datetime
)
And i have data in a file as follows ( column delimeter is | and row deleimeter is new line character ( windows ) )
4000000|10/25/2007 6:07:32 AM
4000001|10/25/2007 6:07:32 AM
4000002|10/25/2007 6:07:32 AM
4000003|10/25/2007 6:07:32 AM
4000004|10/25/2007 6:07:32 AM
BULK INSERT dbo.BulkTest FROM 'c:\insert.dat' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR='
')
When i execute this statement its failing with the follwing message.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (SubDate ).
Can any one please let me know whats wrong in this and what should be done to make it work
Quick response will be appreciated
Thanks much
~Mohan
Using SS2K, I'm getting the following error while bulk inserting:Column 'warranty_expiration_date' cannot be modified because it is acomputed column.Here is my bulk insert statement:BULK INSERT dbo.TestDataFROM 'TestData.dat'WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='|',MAXERRORS = 1,FORMATFILE='TestData.fmt')The computed column is not referenced in the format file and the data filedoes not contain the computed data.Thanks
View 2 Replies View RelatedI am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''
''
, codepage = ''1252''
, keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
Hi,
I have a data file which consists of data as below,
4
PPU_FFA7485E0D||
T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH
WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
Can any one help on this.
Thanks,
-Badri
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
Hi All,
I need to read a csv file, which is in remote server using SQl Bulk Insert Command.
Can I read a file Which is in remote server using BULK INSERT.
Thank you.......
Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
Hi folks,
I have a small problem - I am unable to load data from a .csv file into a table in SQL Server. Here is the command I am running:
BULK INSERT CCSProgramParticipation FROM 'c: est.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
Data in test.csv is the following format: (date fields can be blank)
NY580232,0,6/30/2006,3567,396,7/1/2005,9/9/2005
NY580232,0,6/30/2006,14850,462,12/12/2005,
....
....
What I see is the data does get loaded; however, data from the following row is getting inserted in the last field of a particular row (previous row) - it seems like the rowterminator is being ignored.
Has anyone encountered this issue? Please let me know your thoughts on this.
Thanks so much!
-Parul
Hi, don't know how to do this,
i've got 100+ .CSV text files (50mb in total, not each) i need to import into a SQL server database but don't know how.
i've tried hunting the web for some file concatenation program but just came against pay-for-me software, which didn't help.
any ideas?, would the BCP command do it?
I am bulk inserting from a csv file using
BULK INSERT testTable
FROM 'C:UsersRobsDocumentsSoccer2011-2012SC1.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
',
)
Works fine. Most of my csv files have the same number of columns, but some have 4 less. The files contain the same column headings as the full size ones(only 4 less). Is there a way when bulk inserting for sql to either skip these, or ignore the error.
Well hi here is my text file
"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","sara@parker.com"
and a table with Id , name, surname, email
the ID is a autoincrement thats why it gives me error any way to to skip this ID so let the sql create it automaticaly for every row?
Hi there,I have some text files saved using a UTF-8 encoding. The "BULK INSERT"statment in Sql Server 2000 is failing with a column length error. Savingthe file as ASCII removes the problem. However, I would like to import filesin UTF-8 format. I understand that the BCP tool, when used from the commandline, can take an "-F UTF8" argument, which allows it to work. Can this bedone from the SQL> prompt with the BULK INSERT statement?Cheers,Tobin
View 2 Replies View Relatedhi friends
i am using bulk insert command for txt files
but now i want to use bulk insert command for dbf files
so plz any one can tell me how to use this command for dbf files
thanx in advance.........
Hi everyone,I am trying to bulk insert some data from a csv file to a table. I can do it as part of a button on click event, but don't know how to do it using a stored procedure. This is what I have,ALTER PROCEDURE dbo.TestImportData (
@filename varchar(50) )
AS
BULK INSERT dbo.[TestTable]
FROM @filename
WITH
(
FIELDTERMINATOR = ','
)
/* SET NOCOUNT ON */
RETURNI get the error message "Incorrect syntax near '@filename', Incorrect syntax near 'with'). What am I doing wrong? What should I do? Please help!
Hi ,
I have imported an event log file from an NT server in a test database . The table has been created automatically with some 10 columns as Col001...Col002 ...and so on till Col0010 .
Now i want to copy the data of the event logs to specific columns . So i created a table with name such as Evnt , date , time , server and evntdescription so that whenever i can execute a simple query like
Select * from tablename where type = 'app' , Server = 'test1 ' .
so that i get all the results for that server 'test1 with type 'application' .
The problem is how do i insert that event log file into the table which i have created with different columns names . So that the data with 'App' should go to column 'type , data with server name should go to the column name server and so on ..
I tried all the was but could not succeed . Can i get some help in this regard please through some stored procedures or through DTS , if its possible .
The server is SQL Server 2000 with SP 1.
Many thanks
Anita .
I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0.
I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done.
Any suggestions ?
Regards,
Stuart.
Is it possible to Export a SQL passthrough query to a text file
Like
@A1 int
bcp '"select * from bank where id = @a1" query out ....... "
Or I have to use sth. like SQLDMO ???? But I dont know how to do it ..
Please Helpppppp
Edwin
Hi All,
I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is.
In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in.
I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths?
Thanks,
Shab
SQL Server 2K
OK, I'm probably being a bone-head here and am clearly in over my head but how do you (or can you?) set up a Bulk Insert to take a dynamic path/file name?
What I want to do is pass in the path and file name from an external process to a stored procedure that bulk inserts the content of the file and then does some other routines on it. I haven't had any luck getting Bulk Insert to run if the path/file name is not hard-coded in the sproc as a string.
The point is to have a master routine that can exercise the process for several different customers and use meta data in a table to inform what file to bulk insert.
Any suggestions?
Thanks!
i have the stored procedure...
ALTER procedure [dbo].[xmlpaths]
as
Declare @xml VARCHAR(MAX)
Declare @i as int
select @xml=BulkColumn from openrowset(bulk 'C:Documents and SettingsKasiDesktopote.xml', single_clob)as cse
EXEC sp_xml_preparedocument @i OUTPUT,@xml
Select * From OpenXML(@i,'/college/cse',2) With (name varchar(50), rollno int, year int)
i got the output...
but i want to give path as parameter during execution of procedure name. can anyone help me..
thanks in advance
I have a file which has * as the field delimiter and ~ as the record delimiter, but I don't know how much columns each row will have. Only known is the maximum which can be 15.
The file looks something like: A1*A2*A3*~B1*B2~C1*C2*C3*C4*C5~
SO I have created a table with 15 columns(since 15 can be the max) but now when I try to insert it to that table, I inserts only the entire file into 1 single column.
The command which I am using is: BULK INSERT tablename FROM filename WITH (FIELDTERMINATOR = '*' , ROWTERMINATOR = '~')
but this is not giving the correct output.
The output expected is
A1 A2 A3
B1 B2
C1 C2 C3 C4 C5
I import a group of sentences INSERT from a text file .... test
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
Insert Into XXXXX Values('UUUUUU','3')
The file contains 1000 insert (Aprox); I read lines for lines the file I make the insert
In VS.NET 2003 it works correctly and the process consumes little memory but In VS.NET 2005 the pocket is without space.
How I can specify the factor of growth of the database SQL Mobile?
How another thing can be happening?
I sorry for my inlges ... i speak spanish
Thanks.
i have a log file, i am trying to import data from it to SQL in order to analyze the data (able to query on the data), however that task seems impossible.
In fact the log file contains a varying number of column fields (error logged, various types of data logged demand varying number of columns). More than that the fields themself are hard to extract.
An example of data in my log is:xxxxxxxx is some alphanumeric chars2008-01-09 20:16:05,4784E36F.req,10.1.1.26,xxxxxxxxx,OK -- SMPP - xxxxxxx:xxxxx,Sender=xxxx;SMSCMsgId=2028eecc;Binary=1;DCS=8;Data=xxxxxxxxxxxxxx...2008-01-09 20:16:05,4784E338.req,10.1.1.26,xxxxxxxx,Retry Pending - ERROR: Timeout waiting for response from server or lost connection -- SMPP - xxxxxxxxxxx:xxxxx,Sender=xxxxx........
I may use regular expressions to extract the data, and maybe use a regular INSERT to put in the right table. Thus it seems like making a manual Bulk Insert(yeah and it may take much more time), it seems strange, can i use somehow some additional tool (in SQL package or external), to assist somehow.
Thanks and sorry if this is double posted !
Hello, I am doing a bulk insert using a XML Format file from a csv file. Most everything works just fine, but my delimiter is a , the problem is one of the column sets sometimes contains a , within "" like this:
value1,"value,2",value3
So when I do my insert it is distorting the column values because unlike excel it is not ignoring the comma within the quotes. Is there any way to set an attribute within the format file to prevent this from happening?
I Have a fixed width file where the format look like below:
f1 - 16
f2 - 64
f3 - 64..
....etc
and the format file that i created looks like:
8.0
20
1 SQLCHAR 0 16 "" 0 ExtraField
2 SQLCHAR 0 64 "" 0 ExtraField
3 SQLCHAR 0 64 "" 0 ExtraField
4 SQLCHAR 0 16 "" 1 DatabaseName
5 SQLCHAR 0 128 "" 0 ExtraField
6 SQLCHAR 0 24 "" 2 DelivaryDay
7 SQLCHAR 0 4 "" 0 ExtraField
8 SQLCHAR 0 3 "" 0 ExtraField
9 SQLCHAR 0 24 "" 0 ExtraField
10 SQLCHAR 0 3 "" 0 ExtraField
11 SQLCHAR 0 24 "" 0 ExtraField
12 SQLCHAR 0 64 "" 0 ExtraField
13 SQLCHAR 0 24 "" 0 ExtraField
14 SQLCHAR 0 24 "" 0 ExtraField
15 SQLCHAR 0 24 "" 3 CompleteDate
16 SQLCHAR 0 24 "" 0 ExtraField
17 SQLCHAR 0 24 "" 0 ExtraField
18 SQLCHAR 0 24 "" 0 ExtraField
19 SQLCHAR 0 24 "" 0 ExtraField
20 SQLCHAR 0 256 "" 0 ExtraField
I need to take only three coulmns from the file and the text file won't contain any delimeters between the fields.
I tried to execute this using Bulk..Insert and i am getting the error:
Cannot bulk load. Invalid column number in the format file "C:sampleXXXX.fmt"
Any one can help me what is the problem here?
I am using SQL Server 2000.
Hi ,
I was wondering if there was a way in a format file to load a host file data field to more than one column in a table?
Thanks