Upload txt data to MS SQL Database (Using C# and ASP.NET)

[phoenix_dwarf]

Hi, it's me agian... I was asked to do reasearch on how to upload a txt file's data into a MS SQL Database (corresponding columns etc.) I've searched a bit on the internet but don't find what i'm looking for... I just want to ask does anyone have an idea of where i can go and look for information on this. (I'm still very new to C# and ASP.NET programming) (It's so that i can present this topic in class and implement it in my class project) I would really appreciate any suggestions, and i thank you in advance!

[vuyiswamb]

I think you didnt Search enough, look at this

http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html

http://nathanbuggia.com/post/Import-a-CSV-File-into-SQL-Server-BULK-INSERT.aspx

http://blogs.techrepublic.com.com/datacenter/?p=317

Kind Regards

Vuyiswa Maseko

[phoenix_dwarf]

Lol tnx i just got that page before i saw your reply tnx!

[vuyiswamb]

Am glad you got your Solution . Mark your Post as Resolved

Kind Regards

Vuyiswa Maseko

[phoenix911]

i still dont think its solved... XD

[vuyiswamb]

What is the Problem ?

[phoenix_dwarf]

Oky i've got it partially working now. My only question now is will i be able to have a autonumber column aswell in the database table so that the autonumber keeps on adding numbers as i upload my data into the table? (i hope so but i know that it will most probably only work with an insert statement? right or wrong?)

I saw that to skip the first row you can simply say FIRSTROW = 2 and so i tried FIRSTCOLUMN = 2 but doesn't work. Here is my code:

BULK INSERT dbo.testTBLUpload 
FROM 'C:\Users\GOgGA\Desktop\upload1.txt'
WITH (
		FIRSTROW = 2,
		FIELDTERMINATOR = '|',
		ROWTERMINATOR = '\n'
	 )

[phoenix911]

problem stated above :D

[phoenix_dwarf]

O snd sorry for this little bit of SQL code in the C# section on the forum. But this is for my C# class so yea!

[vuyiswamb]

Remove the First Column and let SQl add it for you as an identity Column.

[phoenix911]

Remove the First Column and let SQl add it for you as an identity Column.

hmmm that actually sounds the obvious ... but there wasnt a column in the textfile which has numbers for the autonumber field

[vuyiswamb]

Can you Post your TextFile and a Create Script of your SQl Table and i will give you an Exact code to write.

kind Regards

Vuyiswa Maseko

[phoenix911]

itll be posted now, give a sec plz...

and thanx

[vuyiswamb]

ok

[phoenix_dwarf]

CREATE TABLE mytable
(
   /*id INT IDENTITY(1,1) PRIMARY KEY,*/
   Fname text,
   Surname text,
   Tel text,
   Salary DECIMAL(18,2)
);

text file:
Hein|Oosthuyzen|0123313470|15489.62
Johan|du Toit|0126547896|13240.27
Nico|Fourie|0124784532|14500.20
Shaun|Venter|0123546987|16320.12
Burnabe|Jordaan|0124872134|12480.80

[vuyiswamb]

Here is your Solution

SqlConnection con =new SqlConnection(@"User id=sa;Password=oops;Server=VUYISWA\SQLEXPRESS;Database=Vuyiswa");
        
        SqlCommand cmdinsert = new SqlCommand();
        
        cmdinsert.CommandText=@"BULK INSERT mytable FROM 'C:\WebSite29\Data.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' );";

        cmdinsert.CommandTimeout = 0;

        cmdinsert.CommandType= CommandType.Text;

        cmdinsert.Connection = con;

        try
        {
            con.Open();

            cmdinsert.ExecuteNonQuery();

        }
        catch(SqlException ex)
        {

            Label1.Text = ex.Message.ToString();
        }   
        finally
        {
            if(con != null)
            {
                con.Close();
            }
        }

Kind Regards

Vuyiswa

[phoenix_dwarf]

tnx i'll try it out and let you know!

[vuyiswamb]

No Problem and dont Forget to mark the post as resolved and give me my reputation. am going home now enjoy your weekend

Kind Regards

Vuyiswa Maseko

[phoenix_dwarf]

Sorry i thought it worked but i see now that i still can't have a autonumber column in my database...? the bulk insert works from the first column onwards and i need to have the autonumber column...Any further suggestions?

[DdoubleD]

Sorry i thought it worked but i see now that i still can't have a autonumber column in my database...? the bulk insert works from the first column onwards and i need to have the autonumber column...Any further suggestions?

Seems to me you could add/insert an autonumber column after the bulk insert completes: How to: Create an Autonumber DataColumn

[phoenix_dwarf]

yeah tnx didn't think of that thanx for all the help! ;-)

[vuyiswamb]

Your SQL Table Must have a Identity Column first change the Defination of your table to this

CREATE TABLE mytable
      (
      id INT IDENTITY(1,1) PRIMARY KEY not null,
      Fname text,
      Surname text,
      Tel text,
      Salary DECIMAL(18,2)
    );

[phoenix_dwarf]

K I tried to do it like this but it still isn't working. here is the error i get:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (Salary).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (Salary).

And this is what i'm uploading:

0123313470|Hein|Oosthuyzen|15489.62
0126547896|Johan|du Toit|13240.27
0124784532|Nico|Fourie|14500.20
0123546987|Shaun|Venter|16320.12
0124872134|Burnabe|Jordaan|12480.80

NOTE: I moved the Tel to infront of the Fname column!

[phoenix_dwarf]

Right it's working! Thnx vuyiswamb, all i had to do is instead of

0124784532|Nico|Fourie|14500.20

i had to

|0124784532|Nico|Fourie|14500.20

then it skips the first column and goes on. Thnx for all the help, sorry for any inconvenience!

[vuyiswamb]

Mark the Post as Resovled and give Reputation if i deserve it :)

Kind Regards

Vuyiswa Maseko

[phoenix_dwarf]

You deserve it thanx! XD

[vuyiswamb]

You are Welcome

[phoenix_dwarf]

I'm sorry for the long wait to mark this thread as solved (been really busy)... My apologies!

[vuyiswamb]

NO Problem