Convert excel to sql

[Paul-D]

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.

 

[ginerjm]

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.

[Barand]

By far the fastest way is to use the SQL statement LOAD DATA INFILE

Specify

FIELDS TERMINATED BY '\t'

 

[Paul-D]

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

[Paul-D]

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. 

[Barand]

Does it accept "\t"?

[Paul-D]

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.