• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Help with creating string variable from excel file using VB.NET

Hi,

How do you create a string varisble s =  'item1','item2',Item3','item4' if my excel file contains the following 4 records :

NSN
item1
item2
item3
item4

I need to create a string variable for all values from NSN column in a workbook (Sheet1) to run a query.

Thanks,

Victor
0
vcharles
Asked:
vcharles
  • 7
  • 6
1 Solution
 
Fernando SotoRetiredCommented:
Please show the code you use to read the rows from the excel file.
0
 
vcharlesAuthor Commented:
Hi Fernando,

I don't  have access to the application  right now, can send it tomorrow. The code that i have can convert the data from xls to xml, if possible to create the string from an xml file that would also work.
Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Do you want the results to be one string of comma separated values, csv, like this
Dim s = "item1,item2,Item3,item4"

Open in new window

Or a array of the values something like this.
Dim s() As String = { "item1","item2","Item3","item4" } 

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
vcharlesAuthor Commented:
Hi,

I managed to find the cold from an old post. I need the data in the following format:

s = 'item1','item2','Item3','item4'

For example will be using NSN IN (S) to search for the items in the NSN field.


Below is the code I will use to upload the xls file and convert it to xml.


Private Sub Button23_Click_1(sender As System.Object, e As System.EventArgs) Handles Button23.Click
        OpenFileDialog1.Title = "Please Select a File"

        OpenFileDialog1.InitialDirectory = "C:temp"
        OpenFileDialog1.ShowDialog()
        Dim ds1 As DataSet = makeDataTableFromSheetNameUpload()
    End Sub
Private Function makeDataTableFromSheetNameUpload() As DataSet
        '  MsgBox("A1")
        'Exit Function
        '    MsgBox("KKKKKKKKKKKKK")
        Try
            Dim ds As DataSet = New DataSet()
            Dim DestinationPath As String = IO.Path.Combine(Application.StartupPath + "\AOP40XYZA.xls")
            '  MsgBox("llllllllllllllllllll")
            conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " & "data source='" & DestinationPath & "';" & "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"" ")
            conn.Open()

            Dim dt As New DataTable()
            adap = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", conn)
            adap.Fill(dt)
            ds.Tables.Add(dt)
            conn.Close()
            ds.Tables(0).WriteXml(Application.StartupPath + "\AOP40XYZA.xml")
            Return ds
        Catch
            MsgBox(Err.Description)
        End Try
    End Function

Open in new window


Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
This is not a valid Visual Basic statement.

Dim s = 'item1','item2','Item3','item4'
0
 
Fernando SotoRetiredCommented:
Hi Victor;

Getting the information you need from the XML file.

'' Load The XML document into memory
Dim xdoc As XDocument = XDocument.Load("C:\Working Directory\File1.xml")

'' After the query below is executed the string you want is in 
'' the variable nsnString
Dim nsnString As String = (From node In xdoc.Root.Descendants("NSN")
                           Select node.Value).Aggregate(Function(ByVal current, ByVal nextVal) current & ", " & nextVal) 

Open in new window

0
 
vcharlesAuthor Commented:
Hi,

I need to create string s with values from the excel file to use with IN () statement.

Victor
0
 
vcharlesAuthor Commented:
Hi, Thanks for the code, how do you add the single quotes?
0
 
vcharlesAuthor Commented:
Fernando,  

If not possible  to  enter them with this code, i can use another code to modify the string, recall doing it in the past.

Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Hi Victor;

Use this query to get the results you are looking for.

Dim nsnString As String = (From node In xdoc.Root.Descendants("NSN")
                           Let strVal = "'" & node.Value & "'"
                           Select strVal).Aggregate(Function(ByVal current, ByVal nextVal) current & ", " &nextVal ) 
 

Open in new window

0
 
vcharlesAuthor Commented:
Got it.

Thank You.

Victor
0
 
vcharlesAuthor Commented:
Thank You.
0
 
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now