Solved

importing CSV to MS SQL table

Posted on 2008-06-17
13
1,387 Views
Last Modified: 2013-11-27
Hi

I am using VS 2008 EXPRESS EDITION and MS SQL 2005 Express edition.
Why is text file handling so frustrating in vb.net?

I have been trying to look up an example of code that would let me import CSV file to MS SQL table.
I want to create a form through which i can enter column names and then by clicking a button i can import respective csv file fields into sql table.

As a beginner i just got lost in the mumbo jumbo of connection strings etc. I could import a csv file in MS SQL using BCP. But how to do it in a vb windows application form by clikin a button in vb.net??

I tried to look up in books , on net i just cudnt find one complete example....as a beginner its hard to understand incomplete examples.
0
Comment
Question by:rogersam
  • 7
  • 5
13 Comments
 
LVL 5

Expert Comment

by:cwickens
ID: 21814024
use SQL management studio express (http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en) and then you can import much easier into SQL express...

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 21814326
Probably for the same reason you are not getting inundated with responses - it gets kinf of open ended, and never ending...

It has been a while since I have needed to write any such code, normally just call a stored procedure in SQL and let it handle it. That might be an option. Pretty sure bcp is considered part of the SQL engine, so would be part of the express version as well (yep, you get the full "engine" in express).

Interesting allowing the column names to be entered - trying to create a mapping of sorts. There in lies part of the challenge. You will have to be able to read the CSV file first and display the columns as "quazzi" names, then allow mapping to "real" names, otherwise how are you going to link it up.

Assume the form is not the issue - if nothing else, it is just a table of two columns - one is populated with the names of the CSV file, the other is where you enter the real names (and destination table as well)...

So, continuing on a theme of not providing the full answer, There is a reasonable code snippet for handling the CSV file itself :
Have you seen : http://www.a1vbcode.com/snippet-3545.asp there is an option there to email the author as well...

The biggest difficulty is not all CSV's are created equal, so there are various degrees of success given the variety of combinations available - especially when you consider type casting as well.

There are more examples in C# - have you thought of that ? They can co-exist (with VB.Net), often find the intellectual "tricky" stuff has more examples in c#

Can write you a stored procedure to unpack into a temporary table - maybe the first 10 records, and return the columns, but not so good with the VB.Net these days...

0
 

Author Closing Comment

by:rogersam
ID: 31468017
Hi thanx for replying. I will try the stored procedure way.
The CSV files i will be dealing with will be pretty well defined. So that should not bew a proble.

I have to use express editions only thats a condition. I wish I cud use C# , but have to use VB only , thats anoother condition. Damn
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21814486
OK, I will keep a watch, my e-mail is on my bio, or post back here...

just found another link on codeproject - that is where I normally go for inspiration - there are a few examples there as well... http://www.codeproject.com/KB/vb/DataGridView_and_CSV.aspx

0
 

Author Comment

by:rogersam
ID: 21814589
cant I use BCP?

i COULDNT FIND A WAY TO INSERT bcp CODE IN MY  vb.net APPLICATION  code. It should work in the express editions.
0
 

Author Comment

by:rogersam
ID: 21814609
Also I need a relational database, thats one more condition coz i need to manipulate it.
and yea, as a beginner, code snippets make no sense to me atall....may be am missin something
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 21814728
Yep, BCP is fine... Could also use openrowset and other bulk tools...
0
 

Author Comment

by:rogersam
ID: 21816196
CAN U enlighten me how to use BCP in a vb.net code ?!
0
 

Author Comment

by:rogersam
ID: 22008090
Alright heres the update, I am using (as conditions) VS 2008 express edition and SqlCe 3.5.

Now the toughest thing is, SSMS 2005 doesn't work with SqlCe 3.5. It gets better, SqlCe doesnot support BULK INSERT either.

So I am just importing the schema from a text file, creating a temp table and then importing data into it record by record. Then I modify the table according to the needs.

Lets see how succesful it is, I am not sure about formatting the text file, have to look into that.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22008310
SQLCE or SSCE 3.5 ? they both use sdf, if created on mobile, then might be out of date.  Should be able to use (if latest build) on both desktop and mobile device - do you have all the latest downloads ?

SSMS 2005 will not support - might later, but need to load either VS 2008 or SQL2008 - the latter, you just need the management console, and would strongly suggest getting that.

However if your "design/ development" platform is in SQL 2005, then use that space to design in SQL 2005 and then migrate to SSCE 3.5 via  http://www.codeproject.com/KB/database/SqlCompactCoptUtility.aspx

Also have a look at (with connecting to 3.5) : http://msdn.microsoft.com/en-us/library/aa983341.aspx

Now, SQL Server 2008 Management Studio, is meant to be available and fully supports 3.5, but not yet as a seperate download. They have release the SQL 2008 Express with Advanced Services which does include it :

goto http://www.microsoft.com/downloads/details.aspx?FamilyId=35F53843-03F7-4ED5-8142-24A4C024CA05&displaylang=en  
or https://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

and all the way down the bottom there is a link to 2008 Express with Advanced Services. Alternatively, get the eval version of SQL Server 2008 and just install the client tools... Please note all this is still pre-release...

Also... Check http://www.microsoft.com/downloads/details.aspx?FamilyId=7849B34F-67AB-481F-A5A5-4990597B0297&displaylang=en and the links down the bottom...

1. SQL Server Compact 3.5 and Synchronization Services for ADO.Net v1.0 for Windows Desktop:
Installs the files for SQL Server Compact 3.5 and Microsoft Synchronization Services for ADO.Net for Windows Desktop platform

2. SQL Server Compact 3.5 Server Tools:
Installs the Server Tools on the IIS server enabling merge replication and remote data access (RDA) with SQL Server 2005 and later.

3. SQL Server Compact 3.5 Books Online and Samples:
The download contains SQL Server Compact 3.5 Books Online, Online Read Me and Samples. SQL Server Compact 3.5 Books Online do not install with Visual Studio 2008. The Books Online are only available on the Web.

4. Synchronization Services for ADO.Net v1.0 Books Online:
Synchronization Services provides the ability to synchronize data from disparate sources over two-tier, N-tier, and service-based architectures. The Books Online has more information about Synchronization Services. The forum for Synchronization Services provides technical Q&A about using Synchronization Services.

 

0
 

Author Comment

by:rogersam
ID: 22011020
Do I have to uninstall MS SQL 2005 EXPRESS EDITION and SSMS 2005 before I download MS SQL 2008 EXPRESS and SSMS 2008?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22012855
no. think there is a link in MSDN that discusses it - might be worth doing a search in there...
0
 

Author Comment

by:rogersam
ID: 22051849
thanx
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql help 5 49
Stored procedure 4 26
Poor Man's .NET Framework Certification 2 43
SQL query to summarize items per month 5 28
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now