CSV To SQL Thru VB.NET Bulk Copy
I'm using SQL Server 2005, VB.NET 2005. I want to be able to import a very large (600,000+ records) CSV file into VB.NET, and insert those records into a SQL table called "OLP_PFM"
I've done this by doing the following:
1. Use OpenFileDialog to select the CSV file
2. Importing the CSV file to a dataset
3. Matching the CSV headers to the SQL headers using listboxes (the CSV files all have different headers. One may say "Zip5", another may say "Zip Code", and another might say "Zip"...but they are all matched to the same "Zip" column in SQL). There is no real way to anticipate what the CSV column headers will be, which is way I'm allowing the user to match columns.
4. Rename the CSV dataset to their matching SQL headers.
4. Clicking an "Import" button that loops through the dataset and adds each record to SQL one-by-one.
All of the above works fine for small files, but for very large files it's slow (because it's loading the CSV into a dataset in memory?). My question is...what's the best way to accomplish all this efficiently?...Import a CSV, match/map columns from the file to the database, then quickly import the CSV into SQL Server. I might also want to do some padding of leading zeros to fields. For example, make sure a certain column's contents are in the format of "08829" instead of "8829" I can do this easily when I loop through the dataset now, but I'll need this ability if I use some other suggested method. THANKS!
I've done this by doing the following:
1. Use OpenFileDialog to select the CSV file
2. Importing the CSV file to a dataset
3. Matching the CSV headers to the SQL headers using listboxes (the CSV files all have different headers. One may say "Zip5", another may say "Zip Code", and another might say "Zip"...but they are all matched to the same "Zip" column in SQL). There is no real way to anticipate what the CSV column headers will be, which is way I'm allowing the user to match columns.
4. Rename the CSV dataset to their matching SQL headers.
4. Clicking an "Import" button that loops through the dataset and adds each record to SQL one-by-one.
All of the above works fine for small files, but for very large files it's slow (because it's loading the CSV into a dataset in memory?). My question is...what's the best way to accomplish all this efficiently?...Import a CSV, match/map columns from the file to the database, then quickly import the CSV into SQL Server. I might also want to do some padding of leading zeros to fields. For example, make sure a certain column's contents are in the format of "08829" instead of "8829" I can do this easily when I loop through the dataset now, but I'll need this ability if I use some other suggested method. THANKS!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not that up on the ins and outs of use bc.exe. I just know it's much faster and better for a number of reasons, including atomic inserts and how sql server will do locking while it runs (it shouldn't obstruct other queries).
On using a datareader, it's pretty simple. Rather than iterating through your dataset you'll do something like this:
While reader.Read()
InsertCmd.Parameters("Colu
InsertCmd.Parameters("Colu
InsertCmd.ExecuteNonQuery(
End While
On using a datareader, it's pretty simple. Rather than iterating through your dataset you'll do something like this:
While reader.Read()
InsertCmd.Parameters("Colu
InsertCmd.Parameters("Colu
InsertCmd.ExecuteNonQuery(
End While
ASKER
Hi, I tried your first suggestion of importing the top 1 row, allowing the user to map the columns. Then when the click "Import" it puts the CSV into a data reader, then does the insert command for each one.
I successfully got the sqlbulkcopy to work, but not for the whole file. I also wanted to format/check the data, and I couldn't figure out how to get sqlbulkcopy to do this. It was super fast though!! But I guess I'll just have to loop through the datareader for now. At least it works. I'll keep looking for a better solution. Thanks.
I successfully got the sqlbulkcopy to work, but not for the whole file. I also wanted to format/check the data, and I couldn't figure out how to get sqlbulkcopy to do this. It was super fast though!! But I guess I'll just have to loop through the datareader for now. At least it works. I'll keep looking for a better solution. Thanks.
ASKER
Andy