By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,642 Members | 2,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,642 IT Pros & Developers. It's quick & easy.

Parsing a text file and pick and dump into sql database

P: 10
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
Nov 21 '06 #1
Share this Question
Share on Google+
8 Replies


Expert 5K+
P: 8,434
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.
Nov 21 '06 #2

P: 10
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.
Nov 21 '06 #3

100+
P: 267
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..
Nov 21 '06 #4

Expert 5K+
P: 8,434
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 21 '06 #5

100+
P: 267
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 21 '06 #6

Expert 5K+
P: 8,434
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.
Nov 21 '06 #7

100+
P: 267
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
Nov 21 '06 #8

P: 10
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
Nov 25 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.