[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

advice needed

Posted on 2009-05-15
9
Medium Priority
?
198 Views
Last Modified: 2013-11-26
I have a CSV file that gets sent to my application once a day and once a day it runs an import now each line will always have either 32 or 33 items of data to insert.

This is my CSV file:

1,"1","Sinatra","Frank","The Lodge","Manor Drive","Sandown","Isle of Wight","PO30 998","01983 822008","","","","","",0,0,0,0,"","",0,0,0,0,0,0,0,"","","",""
2,"2","JORDAN","ROSE","52, DODNOR LANE","DODNOR IND EST","NEWPORT","I.O.W.","PO30 5XD","01983 822008","","","","","",0,0,0,1,"","",0,0,0,0,0,0,0,"","","","", ""

Line 1 has 32 columns of data to insert.
Line 2 has 33 columns of data to insert.

Now all I want is the 32 bits of data forgetting anything extra on each line.

How can I achive that in vb.net


//This is my insert code
 Dim _strInput As String = GetValueToInsert(_impData)
                If String.IsNullOrEmpty(_strTableColumns) Then Continue For
 
                Dim _strInsertCmd As String = "INSERT INTO " & _tableName & " (" & _strTableColumns & ") VALUES(" & _strInput & ");"
 
                Dim _cmdInsert As New SqlCommand(_strInsertCmd, _conn)
 
                _cmdInsert.ExecuteNonQuery()
 
//This is how I find out what columns I have
Private Shared Function GetTableColumns(ByVal Connection As SqlConnection) As String
            Try
                Dim _strColumns As String = "SELECT Column_Name FROM information_schema.COLUMNS WHERE TABLE_NAME = '" + _tableName + "';"
                Dim _cmdColumns As New SqlCommand(_strColumns, Connection)
                Dim _strColumnNames As String = String.Empty
 
                Using _reader As SqlDataReader = _cmdColumns.ExecuteReader()
                    If _reader IsNot Nothing AndAlso _reader.HasRows Then
                        While _reader.Read()
                            If _reader("Column_Name").ToString() = "ident" Then
                                Continue While
                            End If
                            _strColumnNames = _strColumnNames & _reader("Column_Name").ToString() & ", "
                        End While
                    End If
                End Using
                Return RemoveLastOccurance(_strColumnNames, ", ")
            Catch ex As Exception
                'log exception and set status flag to false
                Logger.LogImportError(_tableName, ex)
                Return String.Empty
            End Try
        End Function
 
'I wrap each item of data in ''
        Private Shared Function GetValueToInsert(ByVal input As String) As String
            Try
                Dim _arrInput() As String = input.Split(",")
                Dim _output As String = String.Empty
 
                For i As Integer = 0 To _arrInput.Length - 1
                    _output = _output & "'" & _arrInput(i).Replace("""", String.Empty) & "', "
                    Debug.Print(_output)
                Next
 
                Return _output
            Catch ex As Exception
                'log exception and set status flag to false
                Logger.LogImportError(_tableName, ex)
                Return String.Empty
            End Try
        End Function

Open in new window

0
Comment
Question by:richard_gar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:0791882310
ID: 24397581
http://www.a1vbcode.com/snippet-3545.asp

this code will parse a csv file and return the items in an array
0
 

Author Comment

by:richard_gar
ID: 24397594
I canalread parse the CSV file I need to know a way of knocking out the last "" if  it is over 32 bits of data.
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24398251
what do you mean by 32 bits.Is it 32 items or what?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:richard_gar
ID: 24398309
yeah 32 items
0
 

Author Comment

by:richard_gar
ID: 24398325
I mean I have a table with 32 columns and a CSV file which can be either 32 or 33 items each row but I only want to insert 32 items. How can I tell it to ignore the 33 item.
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24398363
Split all into an array as you already know.

Then make a for loop to have 32 iterations and add it to other array like this

arrOld is the Parsed CSV items
for i = 0 to 31

arrNew(i) = arrold(i)
i++
Next
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24399029
How about the following--it will give you back an un-parsed line consisting of 32 elements.
Private Function Get32(ByVal dataLine As String) As String
    Dim splitData() As String = dataLine.Split(","c)
 
    ReDim Preserve splitData(32)
 
    Return String.Join(",", splitData)
 
End Function

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24399049
Actually, the line should be
ReDim Preserve splitData(31)

Open in new window

0
 

Accepted Solution

by:
richard_gar earned 0 total points
ID: 24450551
found it to be a problem if I had some data like so 'testing, 123' the , within the data was think it was for a new column.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

649 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