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!

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

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

Kind Regards

Vuyiswa Maseko

i still dont think its solved... XD

What is the Problem ?

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'
	 )

problem stated above :D

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!

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

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

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

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

and thanx

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

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

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

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

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?

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

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

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)
    );

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!

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!

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

Kind Regards

Vuyiswa Maseko

You deserve it thanx! XD

You are Welcome

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

NO Problem

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.