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!

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

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.

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

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

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

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!

Edited 7 Years Ago by phoenix_dwarf: n/a

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!

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

This question has already been answered. Start a new discussion instead.