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.
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.
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.
I have to try that
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
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
Or ExcelReader from Einhuger.
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.
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.
Nice one. I’ll have to try this method.
Hi Alberto DePoo
Can you do more details on the topics
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)
- 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
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.
It only work(s/ed) on Windows
And you DO require Office (at least Excel)
It just simplified the automation set up