[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

advice needed

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
richard_gar
Asked:
richard_gar
  • 4
  • 2
  • 2
  • +1
1 Solution
 
0791882310Commented:
http://www.a1vbcode.com/snippet-3545.asp

this code will parse a csv file and return the items in an array
0
 
richard_garAuthor Commented:
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
 
srikanthreddyn143Commented:
what do you mean by 32 bits.Is it 32 items or what?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
richard_garAuthor Commented:
yeah 32 items
0
 
richard_garAuthor Commented:
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
 
srikanthreddyn143Commented:
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
 
käµfm³d 👽Commented:
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
 
käµfm³d 👽Commented:
Actually, the line should be
ReDim Preserve splitData(31)

Open in new window

0
 
richard_garAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now