0

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!

4
Contributors
28
Replies
32
Views
7 Years
Discussion Span
Last Post by vuyiswamb
Featured Replies
0

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

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!

0

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

0

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

0
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

0

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

0

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

0

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?

0

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

0

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

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 by phoenix_dwarf: n/a

0

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!

0

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.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.