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

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!
Who is Participating?
mankowitzConnect With a Mentor Commented:
There is some good answer on

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)

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.