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

Posted on 2007-07-31
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
    LVL 24

    Accepted Solution

    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)

    LVL 24

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now