how to read an excel column from vb.net, that have 2 types of data

Question

I have a problem: On my excel I have a column with integer and string. I need to read the column and fill a datatable with whatever I has.

the excel looks like this:

Ndedocumento

2.147.483.647,
959.224,
949.143,
530.949,

the first as a string, the next as integer.

My applicattion retturns:

Ndedocumento

null,
959.224,
949.143,
530.949,

I tried to post images so you can see it clearly but im not allowed. And it takes me off the enter between the numbers :(

Do you know any way to read all the values? Because on this way I get the value on nothing so I can't format it or anything that makes me catch the value.

here is my code to read the excel:

    conexion_te.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ruta_archivo + "; Extended Properties=Excel 12.0 Xml;HDR=YES;"

    Dim cmd As New OleDb.OleDbCommand
    Dim da As New OleDb.OleDbDataAdapter
    Dim dtExt As New DataTable

    cmd.CommandText = "Select * FROM [" + Me.Combo_Tabla_Hoja.SelectedItem + "]"
    cmd.Connection = conexion_te
    da.SelectCommand = cmd
    da.Fill(dtExt)

hi! i didnt realice that I cant post images yet. I will post with words — Yamila Graub, Apr 14 '20 at 16:25
Is `Ndedocumento` the "first". Where is the integer? In your country is `.` a thousands separator? You seem to give 4 values separated by comma? Is this a CSV? Does "next" refer to 3 values? Are you trying to read all 4 values as (string, int, int, int) or are you trying to read only two values (as string, int) — Caius Jard, Apr 14 '20 at 18:11
hello!Each number separated by comma, is on differents rows. I wanted to post it as the excel but it autommatically jooins on the same renglon here. Each number is a document number, so the dot is not for decimal, is just to separate numbers. They are not 4 numbers separated by comma, its one number with dots in the middle. Its a .xls. Next refers to other 3 different values on different rows all in the same column. Yes Im trying to read all 4 values as (string, int, int, int). They are all in the same column (Ndedocumento) each on different rows (one below the other) — Yamila Graub, Apr 14 '20 at 18:32
I formatted your post using code tags. Does it accurately reflect what you mean? You have 1 column with header of `Ndedocumento` and then 4 values, and your attempts to read the first value are failing but the other values read OK - true? — Caius Jard, Apr 14 '20 at 18:47
Post the code that reads the file. I suspect it is JET/ACE db driver — Caius Jard, Apr 14 '20 at 18:48

Caius Jard · Answer 1 · 2020-04-14T20:40:18.403

0

I would be tempted to switch away from using the ACE driver to read the data out of Excel and instead use EPPlus, as described in Tim Schmelter's answer here. Where he starts his first row as 2 or 1 you should start at 3 or 2.

Here's a VB version of his code:

    Public Shared Function GetDataTableFromExcel(path As String, Optional hasHeader As Boolean = True) As DataTable
        Using pck = New OfficeOpenXml.ExcelPackage()

            Using stream = File.OpenRead(path)
                pck.Load(stream)
            End Using


            Dim ws = pck.Workbook.Worksheets.First()
            Dim tbl = New DataTable();

            For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
                tbl.Columns.Add(If(hasHeader, firstRowCell.Text, String.Format("Column {0}", firstRowCell.Start.Column)))
            Next

            Dim startRow = If(hasHeader, 3, 2)

            For rowNum = startRow To ws.Dimension.End.Row Step 1
                Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
                Dim row = tbl.Rows.Add()
                For Each cell In wsRow

                    row(cell.Start.Column - 1) = cell.Text
                Next cell
            Next rowNum

            Return tbl
        End Using

    End Function

I think I've translated it correctly but if it acts strangely for you, step it with the debugger and look why. This will return a datatable where all the columns are of type string, which should mean your data is well represented. You can do conversions and formatting on it after

edited Apr 14 '20 at 20:40

answered Apr 14 '20 at 20:28

Caius Jard

72,509
5
49
80

I dont have OfficeOpenXml, do you now how to get it? – Yamila Graub Apr 14 '20 at 20:35
Sorry, should have mentioned; Right click on the name of the project in `Solution Explorer`, choose `Manage Nuget Packages`, click `Browse` at the top left, type `EPPlus`, and install the one with about 17 million downloads – Caius Jard Apr 14 '20 at 20:37
I would also have to open these files: .mdb, .xls, .xlxs, .dbf, and i donk know if your answer is useful for this – Yamila Graub Apr 14 '20 at 20:40
Well, in fairness to me your question DOES say "Excel" all over it. It's a bit awkward to turn round now and say "oh, it needs to open access db too".. But if it does need to open an Access DB there isn't any problem of data type, because an Access DB table column can't be both a string and a number! Do a google search for "TypeGuessRows" and see if editing your registry to make the db driver scan fewer/more rows helps with returning the data as a string rather than number, or consider using this for excel files and the jet driver for DB files – Caius Jard Apr 14 '20 at 20:42
why do you say it: Where he starts his first row as 2 or 1 you should start at 3 or 2. Does it depends if it has header or not? – Yamila Graub Apr 14 '20 at 20:58
yes, sorry we use more xls.. I wanted to know if I can use it on the other formats. – Yamila Graub Apr 14 '20 at 20:59
on this line: For Each firstRowCell.. I cant find what is the variable.. what type correspond to firstRowCell – Yamila Graub Apr 14 '20 at 21:05
Cells returns an ExcelRange – Caius Jard Apr 14 '20 at 22:18
But I'm not sure why you need to know the type; does your VB not work it out? Turn on Option Infer.. – Caius Jard Apr 15 '20 at 04:45
I have it ready, but now im executing and i get this error: please set the excelpackage.licensecontext property. Is it free?? – Yamila Graub Apr 15 '20 at 14:39
https://epplussoftware.com/LicenseOverview - free for noncommerical use. See https://www.epplussoftware.com/Developers/LicenseException or install v4 if you want an LGPL licensed version – Caius Jard Apr 15 '20 at 14:47

how to read an excel column from vb.net, that have 2 types of data

1 Answers1