asked on

import large csv file to sql 2000 using vb 2005

i need to import some data from a csv file to my sql server 2000 database

i am wanting to build an app in vb2005 to do this as it gets quite messy.

from this 1 file which is about 1gig is size i need to alter a lot of the data on import and insert in to various field in my table1
but i have code field that can have between 0 and 10 codes in it seperated by a space that i need to insert into another table as seperate records for each code with the recordid .

what is the best way to do this. i could import the table but my computers won't cope with the size of the file

thanks

Bob Learned

That is a very large question, and as this isn't that kind of place where you get code written, what kind of specific help do you want?

Bob

Marcusw

ASKER

i think all i really need to know is how to read a whole line from a stream reader and split it so i can manipluate and insert it into a database

i can make the streamreader connection and the database connection.

i should be able to manipulate the individual fields without any problems but i cannot seem to get just a line of the csv file at a time and then split it.

also some of the field will be within "" and others won't

Marcusw

ASKER

i have seen a number of answers that import the whole file and then work with it, but my file is so big, that is not really an option

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcusw

ASKER

thanks for that

just one last thing, is there a way to specify that the first row contains the field names so i can reference the name rather than a number?

thanks

Bob Learned

The TextFieldParser doesn't have any magic, so you would have to handle that case in your code. It just takes an input line, and breaks it into fields (string array), based on the delimiter.

Bob

Marcusw

ASKER

forgot to ask am i not better inserting 1 row at a time rather than bulkinsert so i don't have to hold all the data in the app

SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial