Jump to content
#FlattenTheCurve ×
Paul-D

Convert excel to sql

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (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 by Paul-D

Share this post


Link to post
Share on other sites

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. 

DataUpload.jpg

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.