June 20, 2013 at 6:30 pm #13006
A lot of the data many of us have to work with comes to us in spreadsheets (e.g. MS Excel, libreOffice ODS), since that is what most people use in place of a proper database. Esp. when these are massive (e.g. a dozen sheets inside, tens of thousands of rows, scores of columns), importing them is a drag.
We need a better import wizard than can open the common file format (xls, xmls, ods) used, let us indicate if the first row has data or is the name of the data, figure out what data types we might want and count the number of characters in the various columns to figure out the right size varchar to use, and let us indicate which columns in the sheet need to be indexed, and which columns in different sheets are FKs.
There are a only a few common patterns of use that I see, and it should not be that difficult to automate.
As a separate request, I also end up importing several different “flat file” formats, including tab delimited files with .txt endings, and some other formats (e.g. there is a *.rrf format I deal with all the time which is pipe delimited, 'nix EOL, UTF8 file–and every industry likely has some of its own conventional format). it would be great to have options to view imported files that could be extended beyond just *.csv! I would also like to be able to define a file type specific import pattern so I don't have to re-enter the various delimiters, EOL, etc. and then go to the extra step of changing the *.csv filtered view of a directory to *.* just to find the files.
Thanks and keep up the good work.
June 21, 2013 at 9:17 am #34357
Well .. SQLyog depends on other 'software modules' when importing external data (ODBC drivers, MySQL 'LOAD DATA).
Can I summarize your request to two points:
1) When using ODBC import for data ssoruces that are not 'real databases' (and there is no schemata) SQLyog should identify data lengths?
2) Support for more file types/extensions when importing data from 'flat files'.
Please confirm that I understand!
I think the answers will be (but please reply to the question above anyway):
As regards 1) it will only be able to to find the max data length after data has been imported. You cannot query the data source about it. In other words we need to create DDL before DML. I don't see any other option. SQLyog and we at Webyog are not magicians.
As regards 2) this is a limitation with the text ODBC driver from Microsoft. It identifies files with specific extensions as (possible) data files. However if you use LOAD DATA syntax file extension does not matter.
You must be logged in to reply to this topic.