How to programmatically import CSV with quotation marks fields into SQL 2005 Compact Edition.

Posted on 2007-07-31
Medium Priority
Last Modified: 2013-11-26
Dear experts,

Sorry I'm fairly new to database coding. I need some help with VB.NET codes to import a CSV file into SQL Server 2005 Compact Edition. I read the solutions posted, but I can't seems to find any that address my type of CSV. Here's a sample:

Name, Address, Tel
"Johnson", "25 Century Street, Century Plaza, #02-02", "1234567"
"Judy", "23 Club Street, Plaza Club, #03-06", "2234567"

Notice that each fields of data (columns) are enclosed by quotation mark, and within them, there's also commas, which makes it not so straightforward as it's also delimited by commas. This quotation mark signifies that the field can also contain commas but not considered as delimited. Yet, the 1st column is without quotation marks.

I believe it's a common standard of CSV, but  I tried import via DTS, it seems they don't have facility to cope with this. Anyway, I now look for coding method, and hope so of you can help pls.

As I'm not sure what type of database method u may suggest, so I thought I mention how I initialised my databse:
   Sub New()
        ' This call is required by the Windows Form Designer.

        ' Add any initialization after the InitializeComponent() call.

        ' find out path to this application on the device
        m_sPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        ' create a connection string
        m_sConnectionString = "Data source = " + m_sPath + "\PropData.sdf"
        ' create a connection to the database
        m_Connection = New SqlCeConnection(m_sConnectionString)
        ' create a command for SqlCe server
        cmdText = "SELECT * FROM PropDetails"
        m_Command = New SqlCeCommand(cmdText, m_Connection)

    End Sub

Many thanks for those who try to help!
Question by:les51
  • 2
LVL 24

Accepted Solution

mankowitz earned 375 total points
ID: 19601831
There is some good answer on http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20936016.html

Basically, what you want to do after you parse each line is to do this

cmdText = "INSERT INTO PropDetails (field1,field2,field3) VALUES " & value1.....
m_Command = New SqlCeCommand(cmdText, m_Connection)

LVL 24

Expert Comment

ID: 19601837
Actually, unless you really really trust the data in the CSV files, it would be smarter to make a prepared statement with placeholders and insert each field as a parameter.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

809 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