?
Solved

import large csv file to sql 2000 using vb 2005

Posted on 2007-08-12
8
Medium Priority
?
328 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Marcusw
  • 4
  • 4
8 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19680789
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
0
 

Author Comment

by:Marcusw
ID: 19682560
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

0
 

Author Comment

by:Marcusw
ID: 19682598
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 19682603
Since you have 2005, here is a question that shows how to use the TextFieldParser to read a CSV file:

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21858837.html

Once you read the file, and get the data, you can use the SqlBulkCopy to write the data to the database.

Bob
0
 

Author Comment

by:Marcusw
ID: 19684129
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 19684194
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
0
 

Author Comment

by:Marcusw
ID: 19684199
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
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 2000 total points
ID: 19684534
I would read about 100-1000 rows, and then use SqlBulkCopy to write the records to the database.

Bob
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question