Importing Text Files And Converting Datatypes

Oct 8, 2004

Hi ya... About to tear my hair out.

I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.

I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)

I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.

Please help! :mad:

Thanks

View 5 Replies


ADVERTISEMENT

Problems Importing Text Files With Double-quotes As Text Qualifier

Jul 14, 2006

I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:

The column delimiter for column "Column 1" was not found.

Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp

View 15 Replies View Related

Importing Text Files

Apr 10, 2001

Hi,

I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc.
Can anybody tell me, How I can achieve this.

Thanks

View 4 Replies View Related

Importing Text Files

Jul 7, 2000

Has anyone written a SQL script to import ASCII comma or space delimited?

I am trying to load a PROGRESS exported file (Unix) into SQL SERVER 6.5 (NT).

Much appreciated,

Nickd

View 1 Replies View Related

Importing Text Files

Sep 19, 2001

I have to import 18000 text files into a sql database.
Each file contains 10 fields and around 5000 records.
I am currently doing this with DTS.

What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.

I would appreciate the benefit of somebody elses experience when dealing with this type of thing.

Cheers,

Brookesy

View 2 Replies View Related

Importing Text Files To Sql Server Using Asp.net/Vb.net

Sep 20, 2004

Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.

Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.

View 5 Replies View Related

Help Needed With Importing Text Files

Jun 6, 2005

I have a load (180,000+) of text files whose contents need to go into a SQL server database.Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.

View 1 Replies View Related

Importing Text Files - DTS Help Needed

Oct 31, 2006

Hi allCould someone help me with the following problem? Hours of googlingyesterday couldn't get me the answer. I'm using SQL 2000 and DTS andtrying to import a huge fixed width text file.File is >1m rows and >200 columns and is defined by a proprietory (i.e. notbcp produced) format specification of the formName Start LengthFld1 0 20Fld2 19 5Fld3 24 53and so on.Tbe only way I've found to define the columns so that DTS can import thefile properly is to go through the wizard and click on the starts of eachcolumn. I don't want to use bcp if possible (I did enough of that onSQL7) - but surely there's a way to get DTS to read from a format file so Idon't have to click 200 times (with all the ensuing errors I could make).Any help greatly appreciated.CheersRob

View 2 Replies View Related

Importing Text Files Into SQL Server - Some Problems

Mar 22, 2007

Hi all,

Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.

My problem is two fold.

Scenario:

I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)

I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.

Running the files through my DTS produced errors on all files. The error was :
Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.

The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.

Basically i need to look at these text files and find Rows that are "incomplete"

the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.



any ideas?



thanks,

mcm

View 4 Replies View Related

Importing Text Files With Different Number Of Columns

Oct 6, 2007

Hi!

I have a bit of a problem with importing (DTS Package) from a text file into an existing DB.

The file has no column names and every file only contain 1 row. This row varies in number of columns.

My first thought was to create a table with the maximum numbers oc columns that can appear in the text file.
An example:

Table with 4 columns; Col001, Col002, Col003 and Col004

The first text file:
123,Peter,Ocean Street,NY

The second text file:
356,John,Park Avenue


My aim is that the second file should append to the table, leaving Col004 as (NULL)

Any ideas how to get this to work?

Expekt

View 3 Replies View Related

How Do I Automate Importing All Text Flat Files Into SQL 7 Table????

Jan 19, 2000

How do I automate importing "All Text Flat Files" into a SQL 7 table. The key is that there is no validation neccessary for the data and I do not want to manually import the data. I just to delimited the data and import it using either a script or a schedular of some type that can do it for me. Some Please Help

View 1 Replies View Related

Problem Importing Text Files With Binary Zeros (0x00) Via SSIS(SQL2005). It Is All Fine When Using DTS(SQL2000) Though.

Apr 24, 2008

Hi.

There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following:
A17 123.17 first row
BB29 493.19 second
ZZ3 18947.1 third row is longer
And in hex format:
00: 41 31 37 20 20 20 20 20 ”‚ 31 32 33 2E 31 37 20 20 A17 123.17
10: 66 69 72 73 74 20 72 6F ”‚ 77 0D 0A 42 42 32 39 20 first row™ª—™BB29
20: 20 00 20 34 39 33 2E 31 ”‚ 39 20 20 73 65 63 6F 6E 493.19 secon
30: 64 0D 0A 5A 5A 33 20 20 ”‚ 20 20 20 31 38 39 34 37 d™ª—™ZZ3 18947
40: 2E 31 20 74 68 69 72 64 ”‚ 20 72 6F 77 00 69 73 20 .1 third row is
50: 6C 6F 6E 67 65 72 ”‚ longer

I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).

The table is:

CREATE TABLE eng (


src varchar (512)

)

When i upload this file into SQL2000 using DTS or Import wizard, the table contains:

select src, substring(src,9,8), len(src) from eng
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 493.19 22
ZZ3 18947.1 third row 18947.1 35

As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.

When i upload this file into SQL2005 using SSIS or Import wizard the result is following:
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 4
ZZ3 18947.1 third row 18947.1 25

This time table is half-empty - all characters behind binary zeros in respective rows are lost.

I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...

View 8 Replies View Related

Converting Datatypes

Jun 9, 2000

Two datatype problems: (1) Datetime conversion (2) Foreign Currency format.

The first problem is that I am trying to convert source data from varchar to datetime. The source data is in CSV format and is displayed as follows - '1111999052349' to represent 1-Nov-1999 05:23:49. Have converted to a numeric value and then tried to convert to datetime but this just returns the following message 'Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.' However you can convert to a timestamp but the value returned is not meaningful (e.g. 0x0E0000013DFA4EE8).

Problem 2 concerns the French currency. The source data is in CSV format and is displayed as follows - '00000001,9700' to represent Fr1.97. Need to convert this to a numeric field or alternatively get SQL 7 to recognise it as a money field.

Any suggestions would be musch appreciated.

Many Thanks.

View 2 Replies View Related

Converting Datatypes, Not Sure If This Is Right?

Sep 28, 2007

New to developing. We are working on a project using asp.net and c# where data is pulled out of an sql database via multiple tables and displayed via a details view. The user can then update the information and submit it back to the database.

The problem we are encountering is:

Within the database there are multiple tables joined by a FK for example there is a Service Group ID column with the Records main table linked to the Service Group table which holds an ID column and a Service Group column so ID1 = Service Group A. Any records within the main table which has an ID of 1, is shows as Service Group A in the details view.

What we wold like to do is when a user updates the Service Group via a drop down, the ID goes into the main table rather than the Service Group name so if they select Service Group B, an ID of 2 goes into the Records main table. We aren't sure how to go about doing the conversion of Service Group B to an ID 2? We have been looking at converting datatypes but don't think it is right for what we are trying to do,

Any suggestions would be gratefully recieved Apologies if this is in the wrong forum.

thanks

View 1 Replies View Related

Converting Datatypes:

Sep 25, 2006

Hi All,

how do you convert from a date to an int ? as well as converting from Varchar to and Int

in SQL server 2000 ?

I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e

DECLARE @DateValue AS VARCHAR(20)

SELECT @DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'


print convert(int,@DateValue) - 5
print GetDate() - 35


SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @DateValue) - 35 )
ORDER BY xrate_date

now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @DateValue Variable, but the conversion throws a syntax error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.

what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!

thanks

regards

View 7 Replies View Related

Error Converting Datatypes

Jul 16, 2007

Hello,



Firstly, i need to work out why I cannot change my datatypes(please see query)






Code Snippet

SELECT * FROM (

SELECT top 10

ref,

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM (

SELECT 1 seq,

'ref' ref,

'RecordDate' RecordDate,

'TransactionID' TransactionID,

'TransactionStatus' TransactionStatus,

'StatusChangedTimeStamp' StatusChangedTimeStamp,

'PartyTransactionStatus' PartyTransactionStatus,

'BadDeliveryReason' BadDeliveryReason,

'TradingDaysRef' TradingDaysRef

UNION ALL

SELECT 2 seq,

cast(ref as bigint),

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM dbo.ParticipantTradeStatusChange

) x

order by seq, RecordDate

) y



The error returned is:



Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.



The reason for me doing this, is exporting both column names & data to a xls file.



Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do



SELECT top 10 * FROM (

SELECT *



It falls over and dies



Help much appreciated

thanks,

i

View 3 Replies View Related

Question About Converting Column Datatypes In Order To Save Space

Nov 6, 2007

All,

I've converted datatypes in the past to something more appropriate in order to save space, and speed up the database, to great success. I'm in the middle of another such change, this one table in particular I am converting from [text] columns to varchar(max)'s, and varchar(1)'s to char(1)'s. I've made a duplicate of the table in question (which has about 25 text columns, and 20 varchar(1) columns), ported over the data, and ran 'sp_spaceused' and got the following results:

-- new table
name | rows | reserved | data | index_size | unused
Response | 39920 | 15384 KB | 15168 KB | 208 KB | 8 KB

-- old table
name | rows | reserved | data | index_size | unused
Response | 39920 | 124128 KB | 123696 KB | 200 KB | 232 KB

I didn't expect results quite this dramatic. Now to my question: Did I miss something measuring the difference? Is there something else, another method I should employ to determine the size differentials?

If this is right, it's outstanding, just unexpected. I would have been happy losing 10 or 20 MB.

I appreciate any insight anyone might be able to provide.

Best,
B.

Just fyi - here are the table schemas:

-- old table
CREATE TABLE [dbo].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [varchar](1) NULL,
[ItemComments1] [text] NULL,
[ItemAnswer2] [varchar](1) NULL,
[ItemComments2] [text] NULL,
[ItemAnswer3] [varchar](1) NULL,
[ItemComments3] [text] NULL,
[ItemAnswer4] [varchar](1) NULL,
[ItemComments4] [text] NULL,
[ItemAnswer5] [varchar](1) NULL,
[ItemComments5] [text] NULL,
[ItemAnswer6] [varchar](1) NULL,
[ItemComments6] [text] NULL,
[ItemAnswer7] [varchar](1) NULL,
[ItemComments7] [text] NULL,
[ItemAnswer8] [varchar](1) NULL,
[ItemComments8] [text] NULL,
[ItemAnswer9] [varchar](1) NULL,
[ItemComments9] [text] NULL,
[ItemAnswer10] [varchar](1) NULL,
[ItemComments10] [text] NULL,
[ItemAnswer11] [varchar](1) NULL,
[ItemComments11] [text] NULL,
[ItemAnswer12] [varchar](1) NULL,
[ItemComments12] [text] NULL,
[ItemAnswer13] [varchar](1) NULL,
[ItemComments13] [text] NULL,
[ItemAnswer14] [varchar](1) NULL,
[ItemComments14] [text] NULL,
[ItemAnswer15] [varchar](1) NULL,
[ItemComments15] [text] NULL,
[ItemAnswer16] [varchar](1) NULL,
[ItemComments16] [text] NULL,
[ItemAnswer17] [varchar](1) NULL,
[ItemComments17] [text] NULL,
[ItemAnswer18] [varchar](1) NULL,
[ItemComments18] [text] NULL,
[ItemAnswer19] [varchar](1) NULL,
[ItemComments19] [text] NULL,
[ItemAnswer20] [varchar](1) NULL,
[ItemComments20] [text] NULL,
[EssayQuestionAnswer1] [text] NULL,
[EssayQuestionAnswer2] [text] NULL,
[EssayQuestionAnswer3] [text] NULL,
[EssayQuestionAnswer4] [text] NULL,
[EssayQuestionAnswer5] [text] NULL,
CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- new table
CREATE TABLE [Test].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [char](1) NULL,
[ItemComments1] [varchar](max) NULL,
[ItemAnswer2] [char](1) NULL,
[ItemComments2] [varchar](max) NULL,
[ItemAnswer3] [char](1) NULL,
[ItemComments3] [varchar](max) NULL,
[ItemAnswer4] [char](1) NULL,
[ItemComments4] [varchar](max) NULL,
[ItemAnswer5] [char](1) NULL,
[ItemComments5] [varchar](max) NULL,
[ItemAnswer6] [char](1) NULL,
[ItemComments6] [varchar](max) NULL,
[ItemAnswer7] [char](1) NULL,
[ItemComments7] [varchar](max) NULL,
[ItemAnswer8] [char](1) NULL,
[ItemComments8] [varchar](max) NULL,
[ItemAnswer9] [char](1) NULL,
[ItemComments9] [varchar](max) NULL,
[ItemAnswer10] [char](1) NULL,
[ItemComments10] [varchar](max) NULL,
[ItemAnswer11] [char](1) NULL,
[ItemComments11] [varchar](max) NULL,
[ItemAnswer12] [char](1) NULL,
[ItemComments12] [varchar](max) NULL,
[ItemAnswer13] [char](1) NULL,
[ItemComments13] [varchar](max) NULL,
[ItemAnswer14] [char](1) NULL,
[ItemComments14] [varchar](max) NULL,
[ItemAnswer15] [char](1) NULL,
[ItemComments15] [varchar](max) NULL,
[ItemAnswer16] [char](1) NULL,
[ItemComments16] [varchar](max) NULL,
[ItemAnswer17] [char](1) NULL,
[ItemComments17] [varchar](max) NULL,
[ItemAnswer18] [char](1) NULL,
[ItemComments18] [varchar](max) NULL,
[ItemAnswer19] [char](1) NULL,
[ItemComments19] [varchar](max) NULL,
[ItemAnswer20] [char](1) NULL,
[ItemComments20] [varchar](max) NULL,
[EssayQuestionAnswer1] [varchar](max) NULL,
[EssayQuestionAnswer2] [varchar](max) NULL,
[EssayQuestionAnswer3] [varchar](max) NULL,
[EssayQuestionAnswer4] [varchar](max) NULL,
[EssayQuestionAnswer5] [varchar](max) NULL,
CONSTRAINT [PK_Test_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

View 5 Replies View Related

Text Datatypes

Feb 19, 2001

Hi,

Is anybody can tell me why I can't have more than 255 characters in a field with text datatype?

The insert and the retreive is done directly using ISQL...

Thanks a lot for your assistance

Patrick

View 2 Replies View Related

Can You Concatenate Text Datatypes???

Sep 8, 2000

I would like to concatenate text datatypes. Is this possible??
I'm using field1 + " " + field2
I cannot convert to varchar since the size is larger than 8000.

Thanks, Vic

View 1 Replies View Related

Datatypes That Preserve Rich Text

Feb 1, 2007

Hey there - I have a textbox (C# 1.1 OR 2.0) that I want to take it's contents and post to a datatype of some sort in SQL2k5 and have it preserve the breaks (return keystrokes) so when the data is presented, it can hold a paragraph form. Is this possible?something like this: <asp:TextBox id="txtContent" runat="server" TextMode="MultiLine" Rows="10"></asp:TextBox>Right now, I'm using nvarchar(4000) as the datatype but if I were to type, say 3 paragraphs, the data returned in the datagrid I bind that data to shows only one long string of data. For the time being, I have been puttting HTML formatting tags in the content I'm posting to the DB, but ultimately don't want to have to do that. I don't want to have to use a Text Editor (like FTB or FCKeditor) if necessary because they seem very much involved for the simple need I have which is to preserve paragraph formatting.So, is there another datatype in SQL2k5 that I should be using? Or are there simple code snippets I can utilize that will recognize a carriage return as a </p><p> tag?I'm trying to migrate to 2.0 as much as possible, so any C# code would be greatly appreciated in 2.0!Thanks for your help!iSheahan 

View 1 Replies View Related

Integration Services :: Converting RTF Files To PDF Files?

Jun 25, 2015

I have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?

View 6 Replies View Related

Task Converting Xls Files To Csv Files ?

Jan 21, 2006

Is there a way to do this with some sort of task ?

View 7 Replies View Related

Importing DBF Files

Feb 16, 2007

With DTS it was easy to import a DBF file, but its a huge pain with SSIS. Is there any way on the horizon to import DBF files as easily as DTS did?

View 1 Replies View Related

Importing CSV Files

Jul 23, 2005

Hello,I need to import a bunch of .csv files. The problem I am having is the"non data" information in the files creating bogus rows and columndefinitions. Here is an example of the csv file.CBOT - End-of-Day Futures Bulk Download 2001.2 Year U.S. Treasury Notes FuturesDateSymbolMonth CodeYear CodeOpen20010103ZTH2001102.0937520010104ZTH2001102.0312520010105ZTH2001102.28125In this case, there are bogues rows created with the text at thebeginning of the file, and also the column names get placed into a rowas well. My question is; how do you import the file and strip out the"non-data" data? so that only the actual data gets inserted into the db?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Importing .prt Files To SQL

Jul 20, 2005

What is the best way to import a structured .prt export file from anaccounting package into SQl Server??Tabs, Fixed col length???

View 1 Replies View Related

Importing .MDF And .LBF Files

Sep 14, 2006

I have the original copies of the .MDF and .LBF of another database that has a bunch of data in that I'm trying to important into MSSQL Express for a project I'm working on. I no longer have a running version of this database just the .MDF and .LBF files and I have no clue what version of MSSQL they were originally created with. If anyone knows a way to import this database please help.

View 1 Replies View Related

Converting Text To Proper Text In SQL

Jul 23, 2005

Given a string it should convert it to a proper text.Example: if you passed a string 'Cat in the hat', I want 'Cat In TheHat'Curious about few things, Does sql have Instr OR Split(like VB)functionalityAnybody can help??

View 3 Replies View Related

Importing CSV Files With SqlBulkCopy

May 2, 2007

I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this:   using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited""))
{
using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn))
{
try
{
dconn.Open();

using (OleDbDataReader dreader = dcmd.ExecuteReader())
{
try
{

using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd"))
{
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2))
{
try
{
dconn2.Open();
bc.DestinationTableName = "dbo.mytable";
bc.WriteToServer(dreader);
}
finally
{
dconn2.Close();
}
}
}
}
finally
{
dreader.Close();
}

}
}
finally
{
dconn.Close();
}
}
}
A couple of the columns for the destination table use a bit datatype.  The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean.   at System.Boolean.Parse(String value)   at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)   --- End of inner exception stack trace ---   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata)   --- End of inner exception stack trace ---   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata)   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)   at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans.  The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior?  I discovered if you change the "1" to "true" and "0" to "false" in the  CSV file it will accept them.  

View 3 Replies View Related

One More Problem With Importing Csv Files

Sep 14, 2007

Hi,
I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:
...,Bearing Double "D" Flange,...
And the result is: ... | Bearing Double | null (all following columns are null)
The code is as following:
string strCsvConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
using (OleDbConnection cn = new OleDbConnection(strCsvConn))
{
string strSQL = "SELECT * FROM " + strFileName;
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cn.Open();
 using (OleDbDataReader dr = cmd.ExecuteReader())
{while (dr.Read())
{
string str = Convert.ToString(dr[8]);
}
// Bulk Copy to SQL Server
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))
//{
// bulkCopy.DestinationTableName = strSqlTable;
// bulkCopy.WriteToServer(dr);
//}
}
}
Any idea is highly appreciated.
shz

View 2 Replies View Related

Problems Importing TXT Files Using DTS

May 10, 2004

Hello,

I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

What am I doing wrong? Should I be using something else than DTS?

Any suggestions are greatly appreciated.

Thanks,
JB

View 5 Replies View Related

Importing Files With Different Extensions

Apr 25, 2008

Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number

xbouns.A17
xbouns.B17
xbouns.C17

I want to import these files into a database table. So I've create a foreach loop and select the foreach file enumerater but am not sure if this is the right way to go about it some help woth be great thanks.

View 7 Replies View Related

Importing Multiple Files To SQL

Aug 5, 2005

I have over three hundred text files that I need to import to SQLServer.Each is in the exact same format.I want to import tham as seperate tables.Is there any way to do it in one process?Regards,Ciarán

View 5 Replies View Related

Importing Files With Different Extensions

Apr 28, 2008

Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number

xbouns.A17
xbouns.B17
xbouns.C17


I'd like to do this within a SSIS package some help in getting this to work would be great.
Thanks

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved