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
vcharlesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Got it.

Thank You.

Victor
0
vcharlesAuthor Commented:
Thank You.
0
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.