Paul-D Posted March 12, 2020 Share Posted March 12, 2020 Hi. I have a large amount of data in an excel spread sheet. I have isolated 3 rows containing a date (yyyy-mm-dd plus two decimal values). I saved this as a tab delimited text instead of a comma separated csv. What I want to do is read each line of a text file and split it up into 3 and construct an INSERT INTO sql. the problem is this is not separated by comas for a split function. Can I split on a tab instead? I have used commas in the past only. TIA Desmond. Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/ Share on other sites More sharing options...
ginerjm Posted March 12, 2020 Share Posted March 12, 2020 AS part of reading in the text file you can easily explode the row on those tab chars and then create your query input values. Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575390 Share on other sites More sharing options...
Barand Posted March 12, 2020 Share Posted March 12, 2020 By far the fastest way is to use the SQL statement LOAD DATA INFILE Specify FIELDS TERMINATED BY '\t' 1 Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575391 Share on other sites More sharing options...
Paul-D Posted March 13, 2020 Author Share Posted March 13, 2020 (edited) No sorry don't understand this. I have opened a text file for reading before. I have a table CREATE TABLE IF NOT EXISTS `History` ( `EntryDate` date DEFAULT NULL, `Value1` decimal(6,2) NOT NULL DEFAULT '0.00', `Value2` decimal(6,2) NOT NULL DEFAULT '0.00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I create this using PHP myadmin. I can upload a text file to the server 2020-03-01 8100.7 1212 2020-03-02 8113.1 1213 2020-03-03 8126.1 1214 How am I going to get the database table loaded with this? I do not work locally as I am using a windows stand alone PC and do not have my computer set up with all the PHP and MySQL bells and whistles. I can read the file and break it up into it,s component parts and compile an SQL query and execute it. I can't do it like you do locally and then upload the table. This is how I have to do things. The hard way would be to create a exe using microsoft's Visual Studio reading and writing text files and run the resultant text in PHP Myadmin. Very long winded way of doing it. Edited March 13, 2020 by Paul-D Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575396 Share on other sites More sharing options...
Paul-D Posted March 13, 2020 Author Share Posted March 13, 2020 Having looked into PHP MyAdmin. There is an import tab. I can upload a txt file but I can't workout how to inport a txt file with tab formatting. See attached. Any help would be gratefully received. Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575400 Share on other sites More sharing options...
Barand Posted March 13, 2020 Share Posted March 13, 2020 Does it accept "\t"? Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575403 Share on other sites More sharing options...
Paul-D Posted March 15, 2020 Author Share Posted March 15, 2020 Thanks this works. Also I managed to convert an excel to a comma seperated (CSV). The only problem was the date was always'0000-00-00'. Seems that when converting from .XLSX to .CSV it forgets the formatting of the date. It has to be re-trained. PHP MyAdmin has a CSV upload which works with an excel.CSV and I can format the date as 'YYYY-MM-DD'. This makes it much easier than turning it into a TXT file and copying it into the SQL text box. Thanks again both solutions work fine. Quote Link to comment https://forums.phpfreaks.com/topic/310280-convert-excel-to-sql/#findComment-1575453 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.