nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Converting XLS files to SQLite database to easily give QB demos

Daniel_Valente

Sharing something that I thought was very useful for a demo I was preparing that you may find useful too. Feel free to add notes/comments to this.

If you want to demo Query Builder with an ad hoc database for data (starting in JMP) that makes sense to have in a star schema-like database you can easily do it with the following online tool.

Convert Files To SQLite Online - Convert To SQLite

Simply convert your JMP data table into a single Excel file with multiple sheets. Then process it with this tool.

It gives you options to specify the fields and tables in your database:

8899_Screen Shot 2015-06-10 at 12.10.08 PM.png

When you are done you are left with a nice SQLite database.

8905_Screen Shot 2015-06-10 at 12.15.22 PM.png

I downloaded the ODBC driver: SQLite ODBC Driver

You can use the browse function to find the database you just made:

8903_Screen Shot 2015-06-10 at 12.12.27 PM.png

And it shows up ready to re-import into JMP as a data source using the Query Builder. Very easy and very slick, IMO.

8904_Screen Shot 2015-06-10 at 12.13.28 PM.png

Accepted answers

All comments

Byron_JMP

That was easy.

Thanks!

flo_kussener

exactly what I was looking for, but the website says me: the service is unavailable......

Phil_Kay

This is really useful. I've just been creating a new SQLite database for demoing Query Builder and Dashboard Builder. I have had some difficulty with converting date-time columns (what a surprise!). Anyone else? I got it to work by changing the columns in the JMP table to the number (e.g. 18 May 2015 => 3514753600) before saving to .xlsx and converting it as a float. Then I specify the date-time format that I want in Query Builder. It works but is not ideal.

Chris_Kirchberg

HI Phil,

Within a Database, there are Data Type Properties as well. I see that one can choose the Data Type within SQLExpress to Date and Datetime. Date seems to have the format of yyyy-mm-dd. Try changing the format to yyyy-mm-dd before export, then import into DB.

Also, I see that datetime is a format for the conversion tool as well. I think the key is export the data time in the format that the conversion tool will recognize.

Chris Kirchberg

Sr. Systems Engineer

JMP Global Enablement Team

SAS Institute, Inc.

Denver, CO

Tel: +1-919-531-9927<tel:%2B1-919-531-9927>

Mobile: +1-303-378-7419<tel:%2B1-303-378-7419>

E-mail: chris.kirchberg@jmp.com<mailto:chris.kirchberg@jmp.com>

Unsubscribe<http://www.sas.com/apps/jmp/email_preference.jsp>

Phil_Kay

Thanks, Chris. Unfortunately it doesn't seem to work. Query Builder recognises the column (e.g. Date_Login, below) as date-time but the column has no data.

I suspect the problem is in the online conversion tool. However, it probably doesn't help that Excel changes yyyy-mm-dd to dd/mm/yy. But even changing it back to yyyy-mm-dd (as I did with Date_Received) doesn't seem to help.

I've attached the JMP table in case anyone wants to have a go.

Regards,

Phil

Sample_formats.jmp

Phil_Kay

I've noticed that if there is a problem with the source data (e.g. spaces in column headers) it may not give you an error. It may just say "service not available" or something like that.

Chris_Kirchberg

Hi Phil,

Date issues are due to datetime format. Looking up what SQLite is expecting and what the conversion tool option are, I have found that if I use YYYY-MM-DDTHH:MM:SS within JMP and then save as .CVS, then select column type at datetime for those columns that have this format, then save, the date will show up properly.

Give that a try

Chris