Import Excel file to DB

Getting Started
1

Hi
I need to import Excel file to DB but the problem is in some row have more the one item and i need to import like one row and one col.
Example

all this is one row and 4 col

123 | mmm | uuu | 12.95
1222 | | |
2223 | | |

all this have to go to 3 diferentes records in the DB.

2

Have a look at the Split function or the String.Split (2019R2). Since there are multiple lines in one Excel row you might have to split first by an end of line delimiter. Then, you can iterate through the array and use split again with the column delimiter, the | in your example.

3

ok
I have to try that
thanks

4

what format is the data in ?
in a spreadsheet ? text file ? or what ?

if youre on windows you can open and read the spreadsheet using the built in excel classes for windows
Eugene has written a book on how to use them which you can get on Xdev’s site

5

is spreadsheet

6

then split etc probably wont help
use the http://documentation.xojo.com/api/windows/excelapplication.html classes
and you may want to get the Ecel books from Eugene Dakin from http://www.xdevlibrary.com

7

Or ExcelReader from Einhuger.

8

On Mac I use copy/paste, Xojo Clipboard and split EOL for rows and tabs for columns.
Pass the info to a listbox for visualization and if all is ok, then parse the listbox into the db.
I don’t need more because I do this once in a while.

If you constantly need to do this, follow other comments.

9

Does the plugin even work anymore with 2019? I have this in my plugin folder and it never loads so I just end up writing classes myself.

10

Nice one. I’ll have to try this method.

11

Hi Alberto DePoo

Can you do more details on the topics

thanks

12

Something really simple (API 1.0 code):

  • select the data from Excel and hit Copy
  • use the clipboard in Xojo Dim c As New Clipboard
  • Split the information using EndOfLine Split(c.Text, EndOfLine.Macintosh) don’t remember why used EOL.Macintosh
  • for each row in that array split using Chr(9) excel_columns = excel_rows(i).Split(Chr(9))
  • load that to a listbox (row by row) Listbox1.AddRow(excel_columns)
  • check that everything looks good on the listbox
  • connect to the db and go from 0 to listbox1.ListCount -1 executing a SQL statement to add each row/record to the db table
13

Yes, it works fine. I’m using it for several of my personal applications. Even apps ported from API1 to API2 worked (in this regard) without needing modification.

14

It only work(s/ed) on Windows
And you DO require Office (at least Excel)
It just simplified the automation set up

15

ok
thanks for the info