.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Post New Web Links

Importing data to SQL!!!

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 
Hi guys I'm having a serious trouble trying to import data in a textfile into an SQL database table: I tried the following:

USE MyDatabase
GO
BULK INSERT register1 FROM 'C:\myfile.txt'
WITH 

FIELDTERMINATOR = ',' 
)
GO

And I get the following error:

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 2. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

I also tried the DTS/Import Wizard and I got the following error:

Error: Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description: Too many columns found in the current row; non-whitespace characters were found after the last defined column's data
Context: Error calling GetNextRows. Your provider does not support all the interfaces/methods required by DTS

He's my sample textfile and the original file is 97.4MB

M1999028576, SUPERIOR TRUCK HIRE AND DISTRIBUTION SERVICE
M199902857


View Complete Post


More Related Resource Links

Importing SharePoint List Data into Project Server 2007 Custom Fields

  
Learn how to use the programmability features of Project Server 2007 and Windows SharePoint Services 3.0 to import SharePoint list data into an enterprise custom field.

Importing xml data with bcp issue

  
I am trying to transfer one table's data from one server to another. The table structure on both servers are identical: CREATE TABLE [dbo].[_CachedQueriesArchive]( [id] [int] NULL, [statement_text] [varchar](max) NULL, [execution_count] [bigint] NULL, [avg_logical_reads] [bigint] NULL, [last_logical_reads] [bigint] NULL, [min_logical_reads] [bigint] NULL, [max_logical_reads] [bigint] NULL, [plan_handle] [varbinary](64) NULL, [query_plan] [xml] NULL, [cursor_type] [varchar](max) NULL ) I am using bcp DBNAME.._CachedQueriesArchive out e:\temp\cq.dat -N -T statement to export data. And bcp DBNAME.._CachedQueriesArchive in e:\temp\cq.dat -N -T to import. All records were imported successfuly. The issue is that column "query_plan" (of "xml" type) is filled in source DB in all 22 records. But in target DB it is filled only in 2 (two!) records. Other columns were imported perfectly. Tried bcp with -e option. The error file was empty. Tried BULK INSERT [_CachedQueriesArchive] FROM 'e:\temp\cq.dat' WITH (DATAFILETYPE='widenative') for import - same result. Tried bcp with -w option instead of -N. No success. Tried importing file on the source server - everything was fine (all 22 records was imported with their "query_plan" data). The problem occurs only on target server. Servers have different versions: source - 9.0.3257 target - 9.0.4053

Error message when importing data

  
Goodday , I get this error message when trying to import data from xls format.  Any suggestions Thanks Rob TITLE: SQL Server Import and Export Wizard Could not retrieve table list. ADDITIONAL INFORMATION: Cannot generate SSPI context SQL Server Network Interfaces: The Local Security Authority cannot be contacted (Microsoft SQL Server Native Client 10.0) BUTTONS: OK  rh

Importing data from recursive XML elements

  
The requirement is to import data (to SQL Server 2005) from a recursive xml schema. The problem is, the element names are positioned recursively. For example, the element would have another element and so on. The depth of occurences are not known.  This data has to be imported and stored in a flattened form Here is the sample xml: <ROOT id="1"> <TIME>2010-02-24 22:28:20</TIME> <REGION code="SW">South West</REGION> <DOMAIN code="LEGL" seq="1">LEGAL</DOMAIN> <YEAR>2009</YEAR> <NOTICE id="1"> <SECTION lo="3" hi="3" seq="680" code="3">SECTION 3</SECTION> <CASE id="79524D7A-D55D-11DD-BF1B-36609DFF4B22" level="1" placeholder="N" linkable="N">Level 1 Section 3 case 1.<CASE id="79538DA2-D55D-11DD-BF1B-36609DFF4B22" parent_id="79524D7A-D55D-11DD-BF1B-36609DFF4B22" level="2" placeholder="N" linkable="N">Level 2 Section 3 case 1.<CASE id="7957BA94-D55D-11DD-BF1B-36609DFF4B22" parent_id="79538DA2-D55D-11DD-BF1B-36609DFF4B22" level="3" placeholder="N" linkable="N">Level 3 Section 3 case 1.<CASE id="7954B7EA-D55D-11DD-BF1B-36609DFF4B2

Help resolve an issue for importing data

  
Hello All, What is the best way to get info from a directory on machine to import into SQL?  I basically need the file path and file size of a certain directory.  I tried using the command prompt and print to a text file, but I can't find a way to get JUST file path and file size.    I'm looking into some free software online like "Treesize" but I'm not sure that'll do it. I know I can do this prgrammatically, but I'm just curious to see if there is a faster way because I am dealing with large directories. Any suggestions would be appreciated. Thanks!

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

  
I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

Importing Excel 2007 data into SQL 2005 database

  
I have a SQL 2005 cluster installation without SSIS and without management studio (I didn't install it). Now we need to import data from an excel 2007 file into a specific table in a database. I have tried doing this from a remote management studio, BI development studio and DTSwizard but I can't seem to make it work. The error message from BIDS is that "the data source is on a remote computer..." Does any of you have a step by step  guide that works from a remote location. BR Rasmus    

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

  
Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

Importing Data from Excel to SQL Server2008

  

Hi Experts,

 I have a table with 100 records. I need to import extra 50 records in the same table. So from 101 to 150 records should insert the data which is present in excel Simply I want to import 50 records from Excel to Table without disturbing the first 100 records.

Please suggest me


Importing data from a flatfile

  

Hi Guys,

Just a quick question regarding import of data from a flatfile.

1st Run.

I loaded 10k worth of data to a table, and run a Select statement. It gives a ascending recordset then truncated the table.

2nd Run.

I loaded 20k worth of data to a table and run a Select statement. Now it give a non-ascending the recordset then truncated the table.

3rd Test Run.

I loaded 30k worh of data to a table and run a Select statement. the same results, it return a non-ascending recordset then truncated the table.

4th Test Run.

I again loaded 30k worth of data to a table and run a Select statement, and suprisingly the records are sorted.

Can somebody tell me why the my data non-ascending in some test?

Thanks,


Importing Data

  

Can I setup my ms sql server 2005 to import data automatically everynight?

I know how to do it manually and how to do it via ms access/task scheduler, but there must be a "better" way.

Thanks

:)


Importing Data into Sharepoint Lists from External Sql Server

  
Hi,

I am looking to import data from  a Relational SQl Server Database that is hosted externally by a 3rd Part vendor. My task is to import all the data that is entered by the external customers into the Sharepoint lists. The external users are presented with a free form Registration page where they enter their data. For the internal users, we want to pull that data in real time and display in lists.

So inother words as soon as the data is entered on the form and user submits it, the database and the sharepoint list should reflect changes simultaneously.

Please give direction as to how can this possible.

SSIS - Data Flow Task Reads Fast But Importing the Output,Update & Deleting Extremely Slow & runs lo

  

Good day, I need help please.

In my source I read about 4mil records in a matter of seconds But Importing the Output to the Same Table takes about an 1 hour to update.

My Source Read is the Same Table to which i'm Importing, Updateing,Deleting

Importing: Table Lock & Check Constraints are checked

Please any ideas & suggestions will be great.

Thank You


Importing Data to SQL Server Compact Database

  

Hi there.  I am newb to Microsoft SQL.  I have used Access for a couple of years.  I created a new database and a table.  I have two questions.  1.) Is there a way to add data manually to a table I built in SQL?  2.) I created some VBA to pull data in and I get Major Error 0x80040E14, what does this mean?

Thanks for your help and patience.


Importing data

  

Hi all, is there any (foc) way to import data from excel files into my application?

I'm using ms visual developer express edition 2008.


Thanks in advance!


Quickly: Importing Data from a file?

  

Hello,

Thanks for reviewing my question.  I would like to know if its possible in SQL to programmatically import a file (excel or .csv) into its own table to an existing table?

We have a website and I would like to offer the ability to upload a file to the website which will then get imported into the SQL server.

Or should I write an Excel add that imports?

Many Thanks

Peter


Importing Data to SharePoint Wiki from a Word or Excel doc.

  

 

Is it possible to import data directly from a Microsoft Word or Excel or .csv or .txt file directly to a SharePoint Wiki?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend