Import CSV data into a table in SQL Server 2005 CE

Question

Post reply

Import CSV data into a table in SQL Server 2005 CE

GAURAVKAUSHIK26

SSC Veteran

Points: 274
More actions
October 13, 2008 at 6:46 am

#192048

Hi,
I am trying to import data from csv file and create a table in sql server compact edition. I am writing code in VB.net.
I have been able to read text file and import it into datagrid but I don't know what needs to be done next. Is is possible to create a table from data grid into SQLCE(server).
Here is my code so far:
Imports System.IO
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Data.SqlServerCe
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objDataset1 As DataSet()
'Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Dim da As OdbcDataAdapter
Dim OpenFile As New System.Windows.Forms.OpenFileDialog ' Does something w/ the OpenFileDialog
Dim strFullPath As String, strFileName As String
Dim tbFile As New TextBox
' Sets some OpenFileDialog box options
OpenFile.Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*" ' Shows only .csv files
OpenFile.Title = "Browse to file:" ' Title at the top of the dialog box
If OpenFile.ShowDialog() = DialogResult.OK Then ' Makes the open file dialog box show up
strFullPath = OpenFile.FileName ' Assigns variable
strFileName = Path.GetFileName(strFullPath)
If OpenFile.FileNames.Length > 0 Then ' Checks to see if they've picked a file
tbFile.Text = strFullPath ' Puts the filename in the textbox
' The connection string for reading into data connection form
Dim connStr As String
connStr = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=" + Path.GetDirectoryName(strFullPath) + "; Extensions=csv,txt "
' Sets up the data set and gets stuff from .csv file
Dim Conn As New OdbcConnection(connStr)
Dim ds As DataSet
Dim DataAdapter As New OdbcDataAdapter("SELECT * FROM [" + strFileName + "]", Conn)
ds = New DataSet
Try
DataAdapter.Fill(ds, strFileName) ' Fills data grid..
DataGrid1.DataSource = ds.Tables(strFileName) ' ..according to data source
' Catch and display database errors
Catch ex As OdbcException
Dim odbcError As OdbcError
For Each odbcError In ex.Errors
MessageBox.Show(ex.Message)
Next
End Try
' Cleanup
OpenFile.Dispose()
Conn.Dispose()
DataAdapter.Dispose()
ds.Dispose()
End If
End If
End Sub
End Class

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

You're trying to reinvent the wheel. 🙂 Use BCP or Bulk Insert, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GAURAVKAUSHIK26 SSC Veteran Points: 274 More actions · Answer 2

Thanks for response. Can you please provide me any code or syntax that imports csv/txt file into SQL CE without SSIS/DTS.

charles.choukalos Valued Member Points: 59 More actions · Answer 3

Slightly off topic...

I haven't been a been able to get SSIS to import csv that contains escaped text strings. Ie I have data for one of the fields that contains a ,

- example header: "Qty","sku#","Comments","Company Name", "Revenue"

- example line: numdata,numdata,"some text","Company Bob, Inc.",3

However when I step through the text file import and tell it to do CSV with " as the text escape character it doesn't work on the company name field. Looking at the debug information it's parsing "Company Bob, Inc." as two fields: "Company Bob" and "Inc." which obviously doesn't fit in the destination table.

Any help? or is this just a bug in MSSQL 2005.

Thanks,

Chuck

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

GAURAVKAUSHIK26 (10/14/2008)
Thanks for response. Can you please provide me any code or syntax that imports csv/txt file into SQL CE without SSIS/DTS.

Sure... if the file has no company private or individual private info, attach a copy of the file and the CREATE TABLE statement for your target staging table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)