Visual Basic

Re: How to get JSON data into array

16-Jan-17 2:31

You'll need Excel locally installed if you want to open stuff using it. What have you tried, and what is the problem exactly?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Message Closed

15-Jan-17 4:41

Live for Coding

15-Jan-17 4:41

Message Closed

modified 16-Jan-17 6:15am.

Richard MacCutchan15-Jan-17 22:59

Richard MacCutchan

15-Jan-17 22:59

You cannot deserialise that JSON data to a System.Object, you need to specify the type of object that will contain the data, i.e Customer. You may need to talk to whoever creates it to use a better type name for the customer data.

Excel open as ADODB.Connection

hansoctantan12-Jan-17 2:24

12-Jan-17 2:24

Hello Everyone,

Can you please advice on why I cant connect to excel file without saving it on my machine.
I tried using these to connection string but still got an error External table is not in the expected format.

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=Excel 8.0;"

When I try to open and save as the spreadsheet I can successfully connect. The file is on FTP and will be downloaded by automation and get information. I cannot make it automatic if I need to open the excel file and save it.

The terminal I will deploy the program dont have office installed so I need to use a different tool to get data. Any suggestion appreciated thanks in advance.

- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.

Dave Kreskowiak12-Jan-17 2:45

Dave Kreskowiak

12-Jan-17 2:45

Because you cannot use it over an application protocol connection, like FTP or HTTP. That isn't supported and, frankly, is not a reliable connection.

The file MUST be on a drive letter or UNC path.

If the extension of the Excel file ends in .XLSX, you have to use the ACE engine. If it's .XLS you can use Jet.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

hansoctantan12-Jan-17 2:57

12-Jan-17 2:57

I download the file first from FTP to local files then connect locally.

I tried this

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"

but still got the error.

When I open the file and saved locally then retry to use the above connection string, I can now connect.

Dave Kreskowiak12-Jan-17 6:33

Dave Kreskowiak

12-Jan-17 6:33

I'm confused. You never mentioned anything about any error message in your OP nor this post.

You also posted connection string making it appear as though you can't open a connection to the database.

So, what's the problem and what's the error message?

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

hansoctantan13-Jan-17 2:24

13-Jan-17 2:24

Ok so I have a spreadsheet which has a .xlsx extension and downloaded this on a FTP site. As I search in the internet you need to use this connection string for .xlsx files using ADODB.

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"

But this gives error on conn.Open, this is the error External table is not in the expected format..

When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.

Note: On my machine I have office installed but on the machines where my program will be deployed don't so I need to use ADODB to read the contents.

Eddy Vluggen13-Jan-17 2:51

13-Jan-17 2:51

hansoctantan wrote:
When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.

Excel, the application, is very, very forgiving in terms of which data it accepts; but sounds like the original file may already be in an invalid format.

In that case, ADODB will fail to read it, as it is not as forgiving as Excel. On the plusside, it is also not as big as the entire Excel-package.

Run your file through a validator, or unzip it and take a look at the contents.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

hansoctantan13-Jan-17 3:01

13-Jan-17 3:01

Really appreciate your help but the excel file came from our client so I cannot put it in any site as a sample.

What do you mean by run it on a validator?

Eddy Vluggen13-Jan-17 3:21

13-Jan-17 3:21

To see if it is a valid zipped XML file. Excel even accepts CSV files with the wrong extension.

hansoctantan wrote:
What do you mean by run it on a validator?

Something like this[^]. If the file is not valid then the connection will not succeed, but it might still be "good enough" for Excel.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Dave Kreskowiak13-Jan-17 5:04

Dave Kreskowiak

13-Jan-17 5:04

An Excel .XLSX file is really a .ZIP file with a bunch of XML files in it. Open it in WinZip or 7ZIP and take a look around.

If it isn't a .ZIP file, it's not really a .XLSX file and cannot be opened by the ACE engine as your connection string has specified.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

hansoctantan17-Jan-17 3:31

Losing lines when writing to RichTextBox

17-Jan-17 3:31

Wow didn't know this. Yeah I can open it on 7Zip.

jkirkerx6-Jan-17 11:25

Re: Losing lines when writing to RichTextBox

6-Jan-17 11:25

So I'm on the other end now. I seem to have a prefect string complete with line feeds, formatted to look great as HTML.
I have multiline set to true.

Scenario 1:
I write to RichTextBox.text and I get 1 line

Scenario 2:
I write to RichTextBox.Lines, in which I have 78 lines and I get 2 lines, Line1 with text and Line 2 empty.

Somethings bombing the control to load lines, but I can't figure out what it is.
This is my last try, I tried about 10 things

Private Sub Load_RTB(ByVal p As String)

        Dim newLines() As String = p.Split(vbCrLf)
        RichTextBox1.Multiline = True

        For Each line As String In newLines
            line = line.Replace(vbCrLf, "")
        Next

        RichTextBox1.Lines = newLines

    End Sub

From the clipboad, I had to do this

Private Sub PasteAction(ByVal sender As Object, ByVal e As EventArgs)

        If Clipboard.ContainsText(TextDataFormat.Rtf) Then

            RichTextBox1.SelectedRtf = Clipboard.GetData(DataFormats.Rtf).ToString

        ElseIf Clipboard.ContainsText(TextDataFormat.Html) Then

            RichTextBox1.SelectedText = Clipboard.GetText(System.Windows.Forms.TextDataFormat.Html)

        ElseIf Clipboard.ContainsText(TextDataFormat.Text) Then

            RichTextBox1.SelectedText = Clipboard.GetData(DataFormats.Text.Replace("\n", "\r\n")).ToString

        End If

    End Sub

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

Eddy Vluggen8-Jan-17 2:58

Re: Losing lines when writing to RichTextBox

8-Jan-17 2:58

jkirkerx wrote:
Dim newLines() As String = p.Split(vbCrLf)

This line cuts up your text into lines. There will not be a CRLF in those lines. If you want to make a "text" out of your lines, you'd join them with a CRLF.

Dim test As String = "1,2,3,4"
Dim items As String() = test.Split(",") '// there will not be a "," in the items!
Dim result As String = String.Join(Environment.NewLine, items)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jkirkerx9-Jan-17 7:22

Re: Losing lines when writing to RichTextBox [Solved]

9-Jan-17 7:22

I'm starting to think it's the Tab char that's preventing the next line to appear.
I have 78 lines, your code example gives me the full first line now. But the next line has a Tab at the beginning.
I'll do some research on this.

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx9-Jan-17 8:29

Losing Linefeeds when writing text from RichTextBox

9-Jan-17 8:29

I ditched my code and threw it in the trash.

I used the RichTextBox controls Load and Save instead, and now it works fine.

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx6-Jan-17 8:24

Re: Losing Linefeeds when writing text from RichTextBox [solved]

6-Jan-17 8:24

I want to preserve the linefeeds from the RichTextBox to the file I'm writing to the disk drive.
I can see the line feeds in Debug before I write the file, but after writing, there gone.

I suspect it's the method I wrote to write the file.

Using fileStream As New FileStream(m_path, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite)

    Dim htmlLen As Long = htmlText.Length
    Dim fileData As Byte() = Encoding.UTF8.GetBytes(htmlText)
    fileStream.Write(fileData, 0, htmlLen)

End Using

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx6-Jan-17 9:45

Paste Action on ContextMenu using RichTextBox

6-Jan-17 9:45

That took some thinking for me, first time I've done this. Changed over to StreamWriter for this

Using streamWriter As New StreamWriter(m_path, False, Encoding.UTF8)

    Dim charArray As Char() = htmlText.ToCharArray()
    Dim charLine As Char() = New Char() {}
    For cdx As Integer = 0 To charArray.Length - 1

        If Not (charArray(cdx) = Chr(10) Or charArray(cdx) = Chr(13)) Then

            Array.Resize(charLine, charLine.Length + 1)
            charLine(charLine.Length - 1) = charArray(cdx)

        Else

            Dim newLine As Char() = charLine
            streamWriter.WriteLine(newLine)

            Array.Resize(charLine, 0)
            cdx += 1

        End If

    Next

    streamWriter.Close()

End Using

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx6-Jan-17 7:26