Member Avatar for ninelg

I have a csv file that needs to be imported into sql server using vb.net.

I got it working with the following code with one exception:

Private Function ImportLeadFile(ByVal projectfile As String, ByVal sLeadFile As String, ByVal DATABASE As String) As Boolean Dim objConn As nsSqlClient.SqlConnection Dim ds As New DataSet Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;" objConn = New nsSqlClient.SqlConnection objConn.ConnectionString = m_strConnection objConn.Open() ' Make sure the .CSV file exists: If File.Exists(sLeadFile) Then Try ' ------ Load the data from the .CSV file: ---------- Dim strSQL As String strSQL = "Select * " & _ " INTO " & DATABASE & ".dbo.[List_staging] " & _ "FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\" & projectfile & "; Extensions=CSV; HDR=No;','SELECT * FROM at1008.csv') " Dim objCommand As nsSqlClient.SqlCommand objCommand = New nsSqlClient.SqlCommand(strSQL, objConn) objCommand.CommandText = strSQL objCommand.ExecuteNonQuery() objConn.Close() Catch ex As Exception sResultText = sResultText & "<BR>" & ex.Message End Try End If End Function

The csv file contains one column of phone numbers with no heading. When the file gets imported into a table the first phone number record is created as a column name. How can I get around this?

  • 4 Contributors
  • 4 Replies
  • 5K Views
  • 5 Years Discussion Span
  • Latest Post Latest Post by t_karthik
Member Avatar for Lord Soth

Hi,

I think you would need to write your own CSV parser using String.Split and not use OLEDB Text Driver (eventhough there might be a parameter for the connection string to fix this, check text driver's reference).

Loren Soth

Member Avatar for ninelg

How do I do that?

I'd really appreciate any help.

Member Avatar for jjthebig1

Ninleg,

Here is a sample:

I have a Categories.txt file in C:\ that has the following contents:

"Sushi", "All kinds of sushi rolls, including crab tempura roll" "Wine", "All kinds of wine, including some world famous Australian Shiraz"

To import it into Northwind, create the DataSet and TableAdapters.

Use the following code to import. See comments in code for explanation.

'Opening the file Using file As New IO.FileStream("C:\Categories.txt", IO.FileMode.Open, IO.FileAccess.Read) 'Creating a strem to read the file into text Using stream As New IO.StreamReader(file) 'Read the entire file, split it into line arrays by CRLF, and loop through it. For Each sLine As String In stream.ReadToEnd().Split(Environment.NewLine) 'You to use ", as the delimiter to ensure that commas inside a record are ignored. Dim sDelimiters() As String = {""","} 'Now split the line into individual records. Dim sRecords() As String = sLine.Split(sDelimiters, StringSplitOptions.None) 'Insert them into the database by referencing the individual string in the array, 'but be sure to replace the quotes with an emtpy string; otherwise, you will have quote in the record. 'The third argument, to which I'm passing a 'Nothing', is expecting a picture. CategoriesTableAdapter.Insert(sRecords(0).Replace("""", ""), sRecords(1).Replace("""", ""), Nothing) Next End Using End Using
  • Jacob Silberstein
Edited by happygeek because: fixed formatting
Member Avatar for t_karthik

When using Text Driver method, "Schema.ini" will be created in the application path.
In the file, specify the "ColNameHeader=False". Then it won't consider the first row as header.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.