468,321 Members | 1,734 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,321 developers. It's quick & easy.

Parsing a text file and pick and dump into sql database

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
8 3746
Killer42
8,434 Expert 8TB
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
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
albertw
267 100+
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
Killer42
8,434 Expert 8TB
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
albertw
267 100+
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
Killer42
8,434 Expert 8TB
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
albertw
267 100+
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
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.

Similar topics

4 posts views Thread by Josef Wolf | last post: by
15 posts views Thread by Ethan | last post: by
4 posts views Thread by news | last post: by
3 posts views Thread by Pir8 | last post: by
7 posts views Thread by Eric Wertman | last post: by
5 posts views Thread by Luis Zarrabeitia | last post: by
8 posts views Thread by =?Utf-8?B?TTFpUw==?= | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.