Importing text file into database

Hello Everyone,

I would like to import a text file which contains one string (a large integer) per line not separated by commas or anything else except a carriage return. Does anyone know of an easy way to store this in a database file? I'm open to suggestions if there is more than one way to save this kind of information within a database. I have SQL server 2005 developer edition if that helps in any way. I'm also starting to learn about Linq so if there is some other way you would store this information for that purpose I would love to hear about that as well. C# code is preferable, but I can use the automatic translators if that's all you have. By the way, I'm a newbie to this subject (if you couldn't tell). Thanks in advance.

Robert

check out

- BCP

- Bulk Insert

|||

Your answer looks promising after a review of the information about it. I'll try it out and if it works then close this post. Thanks for your suggestion.

Robert

|||

Hello Everyone,

I did try this and I was amazed to find out that you can import plain text files straight into excel or access. Obviously with an access database in hand using it with SQL server 2005 should be straightforward. Thanks for your help.

Robert

Importing Null Date Fields

I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.

I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.

Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.

Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.

Thanks for any help,

Jon

Hi,

I read your post and I can advice you the next:

1. Once you have date 12/30/1899 instead all NULL values in SQL Server => you may easy to sort that records, to mark them and place NULL value with “copy” and “paste”. It is easy and quickly.

2. You may write a program instead DTS Wizard /I don’t know that wizard/. That program will read from your CVS file and write to SQL server. From the program you will have full control on all fields. I personally prefer to write a program when I have some unusual case.

I hope that advices will solve the problem. But if you still can’t make NULL values – let me know.

Regards,

Hristo Markov

|||Off the top of my head, I'm thinking there is an option to "Keep Nulls" inside the wizard. I haven't run it and I'm not on my machine w/ SSIS installed so I can't verify it. If you do see such an option, that is how you tell SSIS to preserve the NULLs.

If this isn't an option, then you'll have to go one step deeper and either do custom transformations or write an EXECUTE SQL TASK that will go through and update any record of 12/30/1899 to be NULL.

Importing long XML documents.

Dear All,
I am using
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]
How do I pas a file or a long string into xmltext ?
(The file is more than about 300 Kbytes in size).
Using the a quoted text of the file at xmltext works.
So how do I get the file there, or a database field there ?
Ben Brugman"ben brugman" wrote:
> Dear All,
> I am using
> sp_xml_preparedocument hdoc OUTPUT
> [, xmltext]
> [, xpath_namespaces]
> How do I pas a file or a long string into xmltext ?
> (The file is more than about 300 Kbytes in size).
> Using the a quoted text of the file at xmltext works.
> So how do I get the file there, or a database field there ?
> Ben Brugman
>
Hi Ben
You don't say which version of SQL Server this is!
http://sqlxml.org/faqs.aspx?faq=61 suggests declaring a parameter and using
it as a variable. You can (possiblty) use BCP/BULK INSERT or OPENXML in
SQL2000 to load the data in. In SQL 2005 you can also use OPENROWSET to load
in the file. Examples are in Books Online.
John|||"John Bell" <jbellnewsposts@.hotmail.com> schreef in bericht
news:78EC2FA5-DE22-4DBF-A762-0B40B15E14EA@.microsoft.com...
> "ben brugman" wrote:
>> Dear All,
>> I am using
>> sp_xml_preparedocument hdoc OUTPUT
>> [, xmltext]
>> [, xpath_namespaces]
>> How do I pas a file or a long string into xmltext ?
>> (The file is more than about 300 Kbytes in size).
>> Using the a quoted text of the file at xmltext works.
>> So how do I get the file there, or a database field there ?
>> Ben Brugman
>>
> Hi Ben
> You don't say which version of SQL Server this is!
> http://sqlxml.org/faqs.aspx?faq=61 suggests declaring a parameter and
> using
> it as a variable. You can (possiblty) use BCP/BULK INSERT or OPENXML in
> SQL2000 to load the data in. In SQL 2005 you can also use OPENROWSET to
> load
> in the file. Examples are in Books Online.
I am using SQLServer 2000. The problem at the moment is that the file has
300Kbytes,
and a variable can only hold 8000, so this won't fit. And although
sp_xml_preparedocument can handle the text type, there is no variabele for
this. Using a stored procedure with a text parameter there is no way to fill
the parameter with the 300K bytes text, from a file or a table field.
That is if I get the file in a text field in the first place, haven't even
thought about that problem.
I followed your link to faq=61, (also
http://www.sqlxml.org/faqs.aspx?faq=42)and read some other questions as
well, and allthough I have seen the 'correct' questions, most of the
questions do not even get answered (or I am missing something). Even an
anwser that say it's not possible what I want is ok, but at the moment I am
thinking the only soluttion is complex very unelegant and risky and involves
a lot of coding.
I am supprised that MicroSoft supplies this functionality but then cripples
it to XML length's of only 8000 bytes. (Look like crippleware, or a demo
version, were you can taste the product, but have to buy it to enjoy the
product, except in this case it's the complete product).
Any suggestions are welcome.
Thanks for your time and attention,
Ben Brugman

Importing csv to sql database

Hi All,

Here is my code
Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader")
con = New SqlConnection(strCon)
con.Open()
Dim strselect As String = ""
Try
strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)"
strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')"
cmd = New SqlCommand(strselect, con)
cmd.ExecuteNonQuery()
con.Close()

i have defined connection string in web.config file and my csv file is inside C:\UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."

Regards

I've moved this to the SQL forum because this is not a FAQ. And the message you got isn't an error. It's simply a statement of what happened, why and how to fix it. Follow the instructions and you'll get past this message.

Jeff