Parsing a text file and pick and dump into sql database

[pradeepss]

Guys,

I have a text file which is comma delimited and information. Each information is ended by end of line and started again with comma delimited i.e.

1,2,3,a,4
2,s,4,5,6,7,8,h
2,3,5,7,f,h,j,d,d

What i need to do is pick a line and then put this in a array. then reference it like this:

ar(1)=1
ar(2)=2
.
.
.
ar(5)=4

now i dont want all the data to dumped into sql table.

i pick 1,3 and 5 to dumped into a sql table.

can some tell me how to do this in a very professional and optmized way.

Resources:
Text file comma delimited
Visual Basic project
SQL server database

[Killer42]

Well, taking one thing at a time, splitting the input into an array is very simple using the Split statement, if you don't have commas in your data.

So, does your data contain commas (inside the fields, I mean)?

Or to be more precise, can you data contain commas? You have to be able to cope with what's possible, not just what shows up in one sample.

If you do have commas in your fields (which is perfectly acceptable in CSV format) then you can still split it up, obviously - it'll just require a bit more programming effort.

[pradeepss]

Yes its a comma delimited file....let me how to split them into array. then pick what i want to push it into a SQL table. I am using SQL server 2000.

Well, taking one thing at a time, splitting the input into an array is very simple using the Split statement, if you don't have commas in your data.

So, does your data contain commas (inside the fields, I mean)?

Or to be more precise, can you data contain commas? You have to be able to cope with what's possible, not just what shows up in one sample.

If you do have commas in your fields (which is perfectly acceptable in CSV format) then you can still split it up, obviously - it'll just require a bit more programming effort.

[albertw]

Yes its a comma delimited file....let me how to split them into array. then pick what i want to push it into a SQL table. I am using SQL server 2000.

hi

that's relative simple

say:

strInput="2,s,4,5,6,7,8,h"
Arr=Split(strInput,",")

then....
Item1=Arr(0)
Item2=Arr(1)
etc..

[Killer42]

Yes its a comma delimited file....let me how to split them into array. then pick what i want to push it into a SQL table. I am using SQL server 2000.

The point I was making is that if your data is comma delimited, then it complicates things if a comma appears as part of the data. Think about it.

Here's a quick example to illustrate what I mean. Let's say you have three fields:
ID: 12345
Name: Nurk, Fred
Age: 53
If that is placed in a comma delimited file, you might have:

12345,Nurk, Fred,53

If you then use Split to pull the record apart at each comma, you will end up with 4 fields, not 3.

There are ways of dealing with this, of course. I was just trying to ascertain whether they might be required.

[albertw]

The point I was making is that if your data is comma delimited, then it complicates things if a comma appears as part of the data. Think about it.

Here's a quick example to illustrate what I mean. Let's say you have three fields:
ID: 12345
Name: Nurk, Fred
Age: 53
If that is placed in a comma delimited file, you might have:

12345,Nurk, Fred,53

If you then use Split to pull the record apart at each comma, you will end up with 4 fields, not 3.

There are ways of dealing with this, of course. I was just trying to ascertain whether they might be required.

if you know which fields (array-members) they are, you can always glue them together with a comma.

[Killer42]

if you know which fields (array-members) they are, you can always glue them together with a comma.

True. But only if you know. In this example, what if the name didn't have a comma? What if there were 18 fields which might or might not contain commas? It would be better to separate them using a method which doesn't break until the end of the field.

CSV format handles it all, with text delimiters and so on. It's nothing particularly difficult, just not worth bothering about if the data in this case is all just single-character stuff (as in the sample), with no in-field commas possible. In that case, the simple Split statement is definitely the way to go.

[albertw]

True. But only if you know. In this example, what if the name didn't have a comma? What if there were 18 fields which might or might not contain commas? It would be better to separate them using a method which doesn't break until the end of the field.

CSV format handles it all, with text delimiters and so on. It's nothing particularly difficult, just not worth bothering about if the data in this case is all just single-character stuff (as in the sample), with no in-field commas possible. In that case, the simple Split statement is definitely the way to go.

absolutely
but in this case it seems a simple split-function will do
as the first message indicates a comma separated input of characters.

1,2,3,a,4
2,s,4,5,6,7,8,h
2,3,5,7,f,h,j,d,d

[pradeepss]

hi

that's relative simple

say:

strInput="2,s,4,5,6,7,8,h"
Arr=Split(strInput,",")

then....
Item1=Arr(0)
Item2=Arr(1)
etc..

great,

this definetely help? now how do i push the info in SQL table. i am using sql server 2000.

say i have a table: test in database: main

txt: 1,2,3,dump,E3929H,-12,0,0,0

usnig your method i have them in array.

now how do create a connection to database, and push the info in the table test?

a sample code will definetely help