Converting XLS files to SQLite database to easily give QB demos<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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:
When you are done you are left with a nice SQLite database.
I downloaded the ODBC driver: SQLite ODBC Driver
You can use the browse function to find the database you just made:
And it shows up ready to re-import into JMP as a data source using the Query Builder. Very easy and very slick, IMO.
Re: Converting XLS files to SQLite database to easily give QB demosWorks a treat! Thanks, Chris.
To create a multi-table database there is an extra step: you need to recombine the separate .csv files for each table into a single .xslx workbook.
Re: Converting XLS files to SQLite database to easily give QB demosHi 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
Re: Converting XLS files to SQLite database to easily give QB demosI'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.
Re: Converting XLS files to SQLite database to easily give QB demosThanks, 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
Re: Converting XLS files to SQLite database to easily give QB demosHI 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<>
Mobile: +1-303-378-7419<>
E-mail: chris.kirchberg@jmp.com<>chris.kirchberg@jmp.com>
Unsubscribe<http://www.sas.com/apps/jmp/email_preference.jsp>
Re: Converting XLS files to SQLite database to easily give QB demosThis 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.
Re: Converting XLS files to SQLite database to easily give QB demosexactly what I was looking for, but the website says me: the service is unavailable......
Re: Converting XLS files to SQLite database to easily give QB demosThat was easy.
Thanks!
