SEARCH YOUR SOLUTION HERE  

Issues importing a text file (tab delimited) to a SQL table

I have a text file I am trying to import to a table. This text file is in a tab delimited format. I am using DTS to import the data to a new table I made. The fields are varchar and are set to allow nulls & allow 8,000 characters per field.

The error I am getting is that the data exceeds the allowed amount (or something like that) in col4.

Now I have checked everything in column 4 and nothing exceeds 5,000 spaces/characters combined. I have checked the entire sheet (in excel) for that fact, and there is not one single column/row/cell that exceeds 5,000 spaces/characters combined.

What the heck could be causing SQL to tell me I am trying to import too much data in one column when there is nothing that even comes close to 8,000 characters & spaces combined

Posted On: Friday 26th of October 2012 12:02:31 AM Total Views:  246
View Complete with Replies




Related Messages:

across server update and select issues   (116 Views)
everybody. I am attempting the following relatively simple SQL. UPDATE Server.db.dbo.table1 SET Value = @Value WHERE Id IN (SELECT Id FROM table2) This update is taking up to 1 minute. However if I remove the select and replace with actual values the update is completed instantly ie UPDATE Server.db.dbo.table1 SET Value = @Value WHERE Id IN (id1, id2, id3) The select is also instant if executed in isolation. But when these two statements are combined time taken is too long Please note that the update is occurring on a different server and that table1 does contain update triggers. Any ideas on why this is happening Cheers!
Database Context Changed to   (130 Views)
I am in a bind here.. I am getting the message (after do any sort of query) Database context changed to I am using PHP as the p language. Does anyone have any clue on why it is doing that.. Recently my host upgraded there PHP to version 4.3.10 and before the upgrade the queries were working fine. Im on a windows server that runs PHP and im using a MSSQL database. I've checked the bug reports on PHP and didn't come up with anything as well as googling the error.. IF you have any insite on this problem please let me know.. It may be a stupid error that I am overlooking on my part.
How to insert text that contains an apostrophe??   (86 Views)
, I have an access 2000 db and in one of my tables I need to store some text that sometimes contains an apostrophe. My problem is, everytime my program tries to insert text containing an apostrophe, the program crashes... The insert statments I am using look like this: CurrentDb.Execute "INSERT INTO myTable (Text) VALUES ('" & stringContainingApostrophe & "')" I've tried checking the text for apostrophes and replacing them with an ecape character + apostrophe ("\'") but that doesn't work either... It seems like I should be able to store text that contains an apostrophe... Am i missing something here Does anyone have any ideas
Fulltext index key issue   (168 Views)
my stored procedure to create a table: Code: CREATE PROCEDURE [dbo].[sp_AddContents] @strDB varchar(50) AS BEGIN DECLARE @strSQL nvarchar(4000) SET @strSQL = 'CREATE TABLE ' + @strDB + '.dbo.contents ( [idkey] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [contents] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [tags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [userID] [int] NULL, [hiddenTags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [title] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [theDate] [datetime] NULL, [timekey] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_contents] PRIMARY KEY CLUSTERED ( [idkey] ASC ) )' EXEC sp_executesql @strSQL END and this is my code so far to enable fulltext indexing: Code: 'enable new database for fulltext indexing dbConn.Execute("exec sp_fulltext_database 'enable'") 'create full text indexing on contents 'sqlFullContents = "exec sp_Fulltext_Contents '" & serverName & "'" 'dbConn.Execute(sqlFullContents) catName = serverName & "_contents_catalog" dbConn.Execute("exec sp_fulltext_catalog '" & catName & "','create'") dbConn.Execute("exec sp_fulltext_table @tabname='contents', @action='create', @ftcat=[" & CatName & "], @keyname=[PK_idkey]") my error: Code: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]'fullID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key. C:\WEBSITES\GHAAP\../global/connections/dbCreate.asp, line 106 line 106: Code: dbConn.Execute("exec sp_fulltext_table @tabname='contents', @action='create', @ftcat=[" & CatName & "], @keyname=[PK_idkey]") I don't have clue what to do to fix the error, as i don't even understand it Shem
Write Stored Procedure in text file   (73 Views)
Hi How to write the Stored Procedure output in text file
SQLXMLBulkLoad: Data not importing   (223 Views)
Hi , I need to use to SQLXMLBulkLoad to import loads of XML files, at the moment I have done some coding, its not giving any errors, its just not importing any data into the I_Album table. It stays empty after running the script here is the table structure: Code: CREATE TABLE [I_Album] ( [AlbumID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MusicType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Title] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [KeyTitle] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MainReleaseID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ProductFormAttributeID] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [p_MainGenreAttribute] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Performer] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PerformerCount] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OriginalReleaseDate] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Rank] [int] NULL , [Rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [n_Attributes] [int] NULL , [n_Associations] [int] NULL , [n_Releases] [int] NULL , [n_Tracks] [int] NULL , [n_Documents] [int] NULL , [r_Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO the xsd file: Code: and a short extract of the XML Code: xxxxx MW0000000109 A P In a Dancing Mood In a Dancing Mood MR0000904322 MA0000001928 MA0000002674 Dave Brubeck 1 2001- - 0 5 3 1 2 0 A MW0000000213 A P Take Five Take Five MR0000904151 MA0000001928 MA0000002674 Dave Brubeck 1 2001- - 6 4 1 1 8 0 A and this is the script Code: Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.Execute "D:\xxxxx\Music\Album_Map.xsd", "D:\xxxxx\Music\Album.xml" thanks for any help you can provide, this is the first time I am working with this, have NO clue why its not importing.
Problem while importing data into table (format file)   (120 Views)
, I try to import data from text file into table, created with this query: Code: USE AR; CREATE TABLE dbo.InitTransactions ( [TransactionID] VARCHAR(50) NOT NULL , [TransactionDate] VARCHAR(50) ) GO I don't have primary key here, I don't need it. I need to import only 1st and 6th columns of text file into 1st and 2nd table columns respectively. This is example of data in text file (this is ONE row, which consists of 23 fields, delimited with $, last field is also terminated with $): Code: 5045669$6071557$F$$5045669-6$20060213$20060616$20060707$EXP$BR-ABBOTT-06P-020-0336330-00$ABBOTT$$$M$Y$$$20060707$$20060611$$$IRELAND$ so the first field "TransactionID" here is 5045669 and the sixth field "TransactionDate" is 20060213 (we use format YYYYMMDD) I use the following format file: Code: 8.0 23 1 SQLCHAR 0 50 "$" 1 TransactionID "" 2 SQLCHAR 0 8 "$" 0 Extra "" 3 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 50 "$" 2 TransactionDate "" 7 SQLCHAR 0 8 "$" 0 Extra "" 8 SQLCHAR 0 8 "$" 0 Extra "" 9 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 8 "$" 0 Extra "" 13 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 8 "$" 0 Extra "" 17 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 8 "$" 0 Extra "" 19 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 20 SQLCHAR 0 8 "$" 0 Extra "" 21 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 22 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS 23 SQLCHAR 0 50 "$\r" 0 Extra SQL_Latin1_General_CP1_CI_AS I use the following command (bcp) Code: bcp AR.dbo.InitTransactions IN C:\AR_project\LHT_data\WorkData\DEMO06Q4.TXT -f C:\AR_project\LHT_data\FormatFiles\Transactions.fmt -S -T I obtain this output in command string: Code: 1000 rows sent to SQL Server. Total sent: 71000 1000 rows sent to SQL Server. Total sent: 72000 1000 rows sent to SQL Server. Total sent: 73000 1000 rows sent to SQL Server. Total sent: 74000 1000 rows sent to SQL Server. Total sent: 75000 1000 rows sent to SQL Server. Total sent: 76000 1000 rows sent to SQL Server. Total sent: 77000 1000 rows sent to SQL Server. Total sent: 78000 1000 rows sent to SQL Server. Total sent: 79000 1000 rows sent to SQL Server. Total sent: 80000 1000 rows sent to SQL Server. Total sent: 81000 1000 rows sent to SQL Server. Total sent: 82000 1000 rows sent to SQL Server. Total sent: 83000 83229 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1141 Average : (72943.91 rows per sec.) BUT when I open my table AR.dbo.InitTransactions, I see that NOT ALL values of the second field "TransactionDate" are inserted. I mean that in some rows I still have value NULL in this field. At the same time data in other rows is inserted correctly. Like this: Code: TransactionID TransactionDate 5045669 20060213 5047244 NULL 5047315 NULL 5049307 NULL 5049687 20051114 5050538 20060101 5050583 20060615 It is really strange. I tried to "play" with format file (changing data type) but it doesn't work. I still think that import is incorrect because of data types...but I don't I also thought about wheather I typed correct datatypes for Extra fields, I guess it doesn't matter, because this fields are not imported into table, but still I checked, and it seems that everything is correct. Help me, please. I need to obtain correct values in corresponding table columns
[Help] Network SQL text   (96 Views)
I need help i have 2 tables, 1. profile 2. friends now i need to show my friends network until level 3 sample: i have 3 friends and all of my friend have 3 another which is maybe the same person (me) (Steven) (Olive) (Andrew) (Joy) (Olive) just like that i hope there someone can help me Best regards William
Capture output from T-SQL PRINT to text file   (100 Views)
Does anyone have a method for capturing the output of the print command within t-sql and dumping it to a text file Some of my output is > 256 chars as well. I can see the results of the print statements in the messages tab in Query Analyzer, but I am not able to see the output interactively using osql.
importing online data in local database   (106 Views)
i want to import online data in local database when i click on the alltask >> import >> then i have select the server but it shows me the local server only in the list . how i can add the online database server there. please help me. my local server is shalimarserver and i want the online databtase server xxxx to be add in the list when i import the data in shalimarserver.
How to use noise words in full text search   (69 Views)
This an old post but I just had a similar problem and managed to get it sorted... noise.enu is for English (United States), noise.eng English (United Kingdom) and noise.dat is the default. Check your Word Breaker setting matches the noise file you have changed. Alternatively, change all the noise files to be the same.
text and ntext   (85 Views)
bit of a newbie question here, but I've not found an adequete answer in my google searches. I can't decide whether to use ntext, nvarchar fields in my database as I don't know exactly what characters "text" and "varchar" do and does not store. For example, do text and varchar support accent characters Do they support copyright, cent and tilide characters Or are the only circimstances I'd need to use these fields if I need to store Japanese or Hebrew
Redirecting query results in UNIX to a text file.   (81 Views)
How can I save the large outputs of a query to a text file from the command window
Export from text files   (116 Views)
I'm new on SQL server and I need some help. I'm building a VB6 front end with SQL 2000 as a back end. I've built my tables but the information that will be feeding them It will be received weekly on text files and then I have to export manually into the tables. I woul like to know if is possible to create a batch file to do it for me. I hope you understand what I need and
Have form element as criteria, eg text field   (79 Views)
hi , in my stored procedure i'm trying to access my form to get a value to search for. ie my access form will have a txtjobnumber textfield to be = 2 and i want to find where jobnumber = txtjobnumber on the form in my query. =[Forms]![frmInvoiceEmail]![txtJobNumber] having this as a criteria just gets converted to =N'[Forms]![frmInvoiceEmail]![txtJobNumber]' and prints out =[Forms]![frmInvoiceEmail]![txtJobNumber] as text, not the value
Large Scale Database issues   (75 Views)
We are designing a large-scale database application. The current one will receive information from many inputs constantly. We expect it to grow in one year to 60GB (on average of 4-5GB/Mo). The database then (after one year) will have a cleaning procedure that will delete information and the size of the database will not increase in such a way. Here are a few questions: 1. What are the guide lines to design and implement such a database Where can I find information on the web on such cases 2. From database creation point of view - should we use a few files that will create the database or should we use only one If we use a few files, what happends to clustered indexes Where can I find more reading material 3. The database has the management tables of the application and the data tables. On data table will hold most of the information in that database. Do you think I need to divide te table if so, how do we do it how do you build efficient queries How do you manage the data in the separate tables How do you manage indexes and again where can I find material on this TIA!! Noam
Having issues with Microsoft SQL Server 2008   (69 Views)
This is the error I get: Microsoft OLE DB Provider for SQL Server error '80004005' [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection. /lamps/mn-resources/JLLServer.asp, line 4 This is my code: Code:
Sql Express 2005 - any issues?   (79 Views)
We are looking at upgrading some installations of MSDE to sql express, for a variety of reasons. Number one on the list is that at least one of our databases is approaching the 2 GB limit, and also we have some issues with how unstable MSDE has become lately - possibly could be fixed with sp4, but I'd rather just move to Express and be done with it. So if anyone has any experience with this, I'd like to hear it!
Query: datetime dataType issues   (171 Views)
Hi. I have a table with a datetime DataType. The datetime is stored like such: 11/23/2004 11:23:45 AM If I try to query my data by a specific date '11/23/2004' no records are returned. If I try to do a date range BETWEEN '11/23/2004 12:00:01 AM' AND '11/23/2004 11:59:59 PM' my webpage throws back and error.
SQL Report Service printing issues   (85 Views)
I created a report using SQL Reporting services, In the page header we i have a rectangle and in the rectangle i have a Text box that get data from the report and changes in the fly. The report come outs nice but the problem is when i print the report from the tool itself i get an error in the Text "#Error", Any one knows what is the issue or if this is a known issue by Microsoft. Any help will be greatly appreciated. I can only print the report perfectly if i export to Web Archive and then print it.