How Read File CSV File In Remote Server Using Bulk
Mar 24, 2008Hi 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.......
ADVERTISEMENT
How To Read A Remote CSV File Into SQL SERVER Using Bulk Insert Command..
Mar 24, 2008Hi,
I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system.
Please help me.....
How Read CSV File In Other Server Using Bulk Inser
Mar 24, 2008Hi All,
Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
how do i insert the file....
please help me.....
How Read CSV File In Other Server Using Bulk Insert Command In Sql Server In Different Server...
Mar 24, 2008Hi All,
Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
how do i insert the file....
please help me.....
Read A File Name From Network Folder Automatically For A BULK INSERT
Jul 18, 2007Hi again all,
Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.
ddave
BULK INSERT From A Text (.csv) File - Read Only Specific Columns.
Apr 23, 2008I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.
bulk insert Orders
FROM 'C:Users*******DesktopDownloadURL123.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2,
ROWTERMINATOR = ''
)
So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.
I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
The data file contains greater or fewer columns than the table or view.
The columns are in a different order.
The column delimiters vary.
There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
BULK INSERT AccessDenied On Remote Text File.
Feb 5, 2007OK, Ive read many posts on this problem but have seen no resolution.
Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file.
Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head.
When anyone runs a Bulk Insert command that accesses a text file that is on a remote server, they get an "Access Denied". Now, I did a lot of testing and found that when the users executes the Bulk Insert command from the SQL Management studio on their desk top, they connect to the SQL box with their credentials (OK, that's correct right?), SQL then runs the Bulk Isert command which then reaches out to the remote file server but gets the "Access Denied". I check the logs and it shows that "Anonymous" was trying to accesss the file.
Why is Anonymouss coming over as credentials for SQL on a Bulk Insert? I'm no idiot but this sounds like a big crappy bug tha M$ will not fess to. I followed many suggestions, made sure NTFS and Share level permissions were correct (That was the first thing...), made sure the account that was running as SQL Server within the cluster on both nodes in the cluster was the same, that wasn't it, I even created a SPN for SQL to run and automatically register in AD with the credentials that SQL runs as. NOTHING!!!
Has anyone gotten their bulk insert to work when inserting from a file that is NOT local to the SQL box? Any help is appreciated, but putting the text files on the local SQL box IS NOT AN OPTION.
Thanks
Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)
Nov 26, 2007OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
XML Format File For Bulk Insert Of Text File With Fixed Length Columns
Jan 2, 2008Hey 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.
Read Text File From Flat File Connection Manager SSIS
May 13, 2008Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
SQL 2012 :: How To Do Selective Read Of File Stored In File Table
Jul 2, 2015I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
Read UNIX File From SQL Server
Feb 6, 2004Is it possibel to run a DTS job to read a file from unix server?
View 4 Replies View RelatedHow To Open Or Read SQL Server Log File .ldf
Mar 26, 2008
Hi all,
How to Open Or read SQL Server log file .ldf
When ever we create database from sql server, it's create two file. (1) .mdf (2) .ldf.
I want to see what's available inside the .ldf file.
Thanks,
Ashok
MDF File Cannot Be Read By SQL Server 2005
Feb 10, 2006Newbee question: I am trying to use some of the "101" samples in VS 2005 with SQL Server 2005. These samples use SQL server express MDF files.
The error is: "... you cannont open a database that is incompatible with this version of sqlservr.ext. You must re-create the database."
Do I need to install SQL server express? What are the issues with installing express when I am already running SQL Server 2005?
How do I update or "re-create" the express MDF database file in SQL Server 2005?
Thanks!
Read XML File From File System?
Jan 8, 2002Anyone reading XML disk-files into SQL Server?
I have a process that I may want to do this with.
It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?
Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.
I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...
Thoughts?
Can I Read Transaction Dumps (*.TRN) File In SQL Server 7.0
Jul 28, 2000our users believe that we lost some valid data, but no one knows who did it,
I thought I can find it from the transaction dumps I take every hour
so ,Can I read Transaction Dumps (*.TRN) file in SQL server 7.0 or Can I get this information through other means.
Thanks for your help
DP
Need A SP, XP Or USP To Read The Contents Of A File (i.e. Any OS Or SQL Server Logs )
Jun 11, 2002hello,
I am working on some maintenance process. I need a Stored Procedure to reda the contents of a file. (i.e. OS or SQL server log files). plz help me in sort out this problem.
regards,
venkat.
Which Is The Best Way To Read From A Txt File And Insert Data Into Sql Server?
Mar 21, 2008Hi, I was wondering which is the best way to read data from a txt file and insert each row into sql.
OLE DB Command could be? It will be necesary to work with variables?
My txt file will have a defined width (if it is necessary to know). I will have many rows with many columns. I have to map eah column from the txt file to it's corresponding column in sql server and insert data into it for each row.
Thanks for your help!
Bulk Load XML File To SQL Server (Express) Table
Sep 29, 2006Hi All,I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?Thanks,Claude.
View 3 Replies View RelatedSQL Server 2008 :: Bulk Insert With A Format File?
Mar 5, 2015I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.
View 3 Replies View RelatedHow To Read And Store A Large File From SQL Server 2000?
Feb 23, 2006hi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.
Can Sql Server Read An Outside File And Insert The Contents Into The Database?
Aug 8, 2000hello!
i'm an sql server beginer. i was wondering if some of you guys can help
me out. i need for the sql server to be able to read an outside file (just text) and be able to run a script that will insert it in the database. it's a dcc output file. we've tried running this script:
DROP TABLE tests
go
DECLARE @SQLSTR varchar(255)
SELECT @SQLSTR = 'ISQL -E -Q"dbcc checkdb(master)"'
CREATE TABLE tests (Results varchar(255) NOT NULL)
INSERT INTO tests EXEC('master..xp_cmdshell ''ISQL -E -Q"dbcc checkdb(master)"''')
and it's running good but the problem is the results of the dbcc here did not come from a file but directly after executing the dcc command. is there a way to do it?
thank's for the help!
How To Read CSV File In SQL Server 2005 Using OpenRowSet Function
May 4, 2007Hi
i want to access a CSV file using OpenRowSet function in SQL Server 2005.
Anyone having any idea; would be of great help.
Regards,
Salman Shehbaz.
SQL Server 2012 :: Bulk Insert Error With XML Format File?
Nov 9, 2014I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
SQL Server 2014 :: Bulk Insert A Fixed Width File
Jul 29, 2015I wasn't sure where to put this topic so I put it here since I figured it is a question that would apply to virtually any version even though I am using SQL Server 2005.
We have a vendor that sends us a fixed width text file every day that needs to be imported to our database in 3 different tables. I am trying to import all of the data to a staging table and then plan on merging/inserting select data from the staging table to the 3 tables. The file has 77 columns of data and 20,000+ records. I created an XML format file which I sampled below:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="RetNo" xsi:type="CharFixed" LENGTH="6"/>
[Code] ....
The data file is a fixed width file with no column delimiters or row delimiters that I can tell. When I run the following insert statement I get the error below it.
BULK INSERT myStagingTable
FROM '.........myDataSource.txt'
WITH (
FORMATFILE = '.........myFormatFile.xml',
ERRORFILE = '.........errorlog.log'
);
Here is the error:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Read Data From A Csv File And Insert It In A Sql Server Databse Table
May 25, 2007Hai Everbody,
for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance.
Thanks and regards
Biju.S.G
SQL Server 2012 :: Bulk Insert Using UNC Path Of File Table Directory
Jul 22, 2013Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.
Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.
Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.
Jul 20, 2006I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS
Nov 11, 2014I have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.
Copy File From Remote Server
Mar 13, 2002Hi!
What should I do to copy backup file from remote server directory to my machine?
Thank you,
Elena.
SQL Server 2008 :: Can Bulk Insert Only Desired Column From A Flat File To Table
Mar 18, 2015Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that. But not sure if SSIS will let me do that.
BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express
May 24, 2006I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.
The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "
Looking with a hex editor, all the above whitespace are 20's.
From the documentation, I've constructed the following table...
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)
To Bulk Insert this, I've written the following...
BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)
and written the following format file...
9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""
However... actually running this gives the following error...
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".
Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.
It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.
Even the following trivial example doesn't work for this field of data...
"7999163 "
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)
and
9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""
or
9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
which give this error...
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.
or
9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""
which gives this error...
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Also - there was the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,
Any thoughts on this would be greatly appreciated...
Thanks!
BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express
May 24, 2006I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.
The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "
Looking with a hex editor, all the above whitespace are 20's.
From the documentation, I've constructed the following table...
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)
To Bulk Insert this, I've written the following...
BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)
and written the following format file...
9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""
However... actually running this gives the following error...
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".
Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.
It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.
Even the following trivial example doesn't work for this field of data...
"7999163 "
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)
and
9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""
or
9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
which give this error...
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.
or
9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""
which gives this error...
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,
Any thoughts on this would be greatly appreciated...
Thanks!