How to programmatically import CSV with quotation marks fields into SQL 2005 Compact Edition.
Posted on 2007-07-31
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:
' 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)
Many thanks for those who try to help!