reg: Simple, Help me out

[Rama krishna]

Hi All:

I got a .txt file with five columns, it's named as in the first line with
double quotes of each column. And from the second line onwards, the real
data. So i would like keep the these data in their corresponding columns in
the sql database. I created a table with these 5 columns in the database,
now i want these data to be dumped into database. So initially iam trying to
read line by line, and spliting using "" as a delimiter. But am not able to
do it. Iam sending my code along with this data, pls help me out.

"Activity Name","Start Date","Finish Date","Resources Assigned","Notes per
Bar"

"HLD","7/2/03","7/30/03",,
"Interal Design","7/2/03","7/3/03","RamaKrishna, Kittu","hai"
"Interal Design of ATP","7/8/03","7/9/03","RamaKrishna, Richard",
"Sign-Off","7/9/03","7/9/03","RamaKrishna",



Dim objReader As New StreamReader("c:\TestData.txt")



'Inserting ProjectData into SQL Database

Dim cnDBConnection As New SqlConnection("Data Source=goutham;
Database=pubs;User ID=sa;Pwd=sa")

cnDBConnection.Open()

Dim sqlInsertCommand As New SqlCommand()

While objReader.Peek > -1

str = objReader.ReadLine

'Response.Write(str & "<br>")

strSplit = Split(str, """")

For i = 1 To UBound(strSplit) - 1

'Response.Write(strSplit(i) & "<br>")



arrStrColumnNamesAfterNew = Split(strSplit(i), ",")



Next

sqlInsertCommand.CommandText = "Insert into ProjectData([Activity
Name],[Start Date],[Finish Date],[Resources Assigned],[Notes Per Bar])
values('" & arrStrColumnNamesAfterNew(0) & "', '" &
arrStrColumnNamesAfterNew(1) & "' , '" & arrStrColumnNamesAfterNew(2) & "' ,
'" & arrStrColumnNamesAfterNew(3) & "',, '" & arrStrColumnNamesAfterNew(4) &
"')"

sqlInsertCommand.Connection = cnDBConnection

sqlInsertCommand.ExecuteNonQuery()



Pls help me out.

Waiting for ur reply.

Best regards,

RK

 

[Rama krishna]

Hi:
Tx for ur suggestions. But can it can be done. U got any code for that. Pls
let me know. Or is there any easy process, that if we give inout as .txt,
file automatically it'll dump into the database?

Waiting for ur reply.
Best regards,
RK

your data has commas within the data, and that is making the task more
difficult.

you need to accommodate the commas in the data, and possible quotation marks
in the data.
check out regular expressions, or try to read the data field at a time
instead of line at a time.

Hi All:

I got a .txt file with five columns, it's named as in the first line with
double quotes of each column. And from the second line onwards, the real
data. So i would like keep the these data in their corresponding columns in
the sql database. I created a table with these 5 columns in the database,
now i want these data to be dumped into database. So initially iam

trying

to
read line by line, and spliting using "" as a delimiter. But am not able to
do it. Iam sending my code along with this data, pls help me out.

"Activity Name","Start Date","Finish Date","Resources Assigned","Notes per
Bar"

"HLD","7/2/03","7/30/03",,
"Interal Design","7/2/03","7/3/03","RamaKrishna, Kittu","hai"
"Interal Design of ATP","7/8/03","7/9/03","RamaKrishna, Richard",
"Sign-Off","7/9/03","7/9/03","RamaKrishna",



Dim objReader As New StreamReader("c:\TestData.txt")



'Inserting ProjectData into SQL Database

Dim cnDBConnection As New SqlConnection("Data Source=goutham;
Database=pubs;User ID=sa;Pwd=sa")

cnDBConnection.Open()

Dim sqlInsertCommand As New SqlCommand()

While objReader.Peek > -1

str = objReader.ReadLine

'Response.Write(str & "<br>")

strSplit = Split(str, """")

For i = 1 To UBound(strSplit) - 1

'Response.Write(strSplit(i) & "<br>")



arrStrColumnNamesAfterNew = Split(strSplit(i), ",")



Next

sqlInsertCommand.CommandText = "Insert into ProjectData([Activity
Name],[Start Date],[Finish Date],[Resources Assigned],[Notes Per Bar])
values('" & arrStrColumnNamesAfterNew(0) & "', '" &
arrStrColumnNamesAfterNew(1) & "' , '" & arrStrColumnNamesAfterNew(2) &

"'

,
'" & arrStrColumnNamesAfterNew(3) & "',, '" &

arrStrColumnNamesAfterNew(4)

&
"')"

sqlInsertCommand.Connection = cnDBConnection

sqlInsertCommand.ExecuteNonQuery()



Pls help me out.

Waiting for ur reply.

Best regards,

RK