Transfer excel data to SQL

Hi,
What is the quickest way to transfer the following Excel data
to MYSQL. I have installed the MYSQL FOR EXCEL plugin.
Just under one million rows and 157 columns and size 138mb
Thanks
Ian
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
An easy solution would be to save Excel as .csv and use MySql Workbench to do the import
1
Ian BellretiredAuthor Commented:
Hi John,
Thanks for your reply.
I created a new folder named "dumps" in the required directory
and placed a csv file in it. Then in Workbench I clicked on 'load folder contents' button and got
the following  "There were no dump files in the selected folder" message
Can you please tell me where I am going wrong.
Many thanks
Ian
0
John TsioumprisSoftware & Systems EngineerCommented:
Check this tutorial from MySQL.
It seems that HeidiSQL is much easier for this task ("Import/Export" -> "Import Textfile)
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ian BellretiredAuthor Commented:
I haven't mentioned it but my knowledge of Excel is quite good but not so with SQL.
In fact I'm a complete novice so the Heidi link you sent mentioned it was a developers tool.
There is another way and that is to export it from Excel using the plugin but it seems limited
to so many rows and I have close to a million and would take me a fortnight to complete the task.
Surely the MYSQL import must work. Just a matter of finding out why I get the
"There were no dump files in the selected folder" message. Under what circumstances does
this message appear.?  The directory is correct and the file is a csv file
Thanks for your help John.
0
Ian BellretiredAuthor Commented:
I am very disappointed with the lack of progress in developing user friendly
software packages. It is a damn disgrace that simple products are not available
to carry out import and export of data. You need to be a programmer to even
have any sort of a chance. These products like mysql are free. But are a minefield
for non programmers and the interface is small and hard to follow. I'm happy to pay
good money for one that has more than the 2GB capacity of Access with a helpful
support team.
Meanwhile back to the question above. I still would like help if anyone has knowledge
why a file is not recognised.
0
John TsioumprisSoftware & Systems EngineerCommented:
Well ...lets take it from the start...i will take you to another route since you mentioned Access..
For start fire up Access and import Excel/csv...Access will ask you some simple questions and prepare for each worksheet a nice table..
[Not needed but is would be helpful] Go to the design view of each table and edit the field names to your liking (in case you have worksheets that get imported like field1,field2)...also delete anything that is empty but got imported.
After you have setup Access its time to move on.
Grab Bullzip's Access to MySQL and install it.Open it and select the Access file with it...answer the few questions that will ask you (credentials,name of the destination database...etc)...and that's all....it will take some time (a bit longer) but you will get the job done.
Because you mentioned about development  maybe if you give more info about what you need we can discuss a solution.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor Commented:
Hi John thank you for your continued help and support.
Here's the scenario.
I currently have an Excel flat file (csv) around 840,000 rows and 156 columns.
It is not suitable for use with Excel in it's current format so I have split it into
17 worksheets to which each sheet has an additional 120 or so columns filled with
formulas some very memory zapping. The number of rows in each sheet varies from
35,000 to 150,000.
Apart from Excel being over burdened thus causing continual 'not responding'
messages etc, it takes me several days to append to each of the 17 sheets and copy down
the formulas to accommodate the additional rows.
The idea I came up with was to store the large flat file data in a sql database and
somehow link it to Excel where the formulas are stored.
Within Excel there is an add-in called Solver which is an important part of my calculations.
I use Excel as I have worked with their formulas for many years. If I could write code
I would use  'R' or any similar program. I also have a registered copy of Stata but that
also is way above my level of comprehension.
The project is to establish probabilities in horse racing.
I hope this gives you some idea of what I am trying to achieve.
Cheers
Ian
0
John TsioumprisSoftware & Systems EngineerCommented:
I understand more than perfectly...:)...lets say i have a similar project....small world....well take the Access route and let me know of the progress...
1
Ian BellretiredAuthor Commented:
I'm renaming the fields now. I don't recall getting any questions prior to importing to Access. I just had to rename
the file to Excel workbook as no provision for csv in menu.
0
Ian BellretiredAuthor Commented:
Thanks John, I have succeeded in following your clear instructions.
Now for the work ahead of appending sorting etc
Ian
0
John TsioumprisSoftware & Systems EngineerCommented:
now is where the fun starts...:)
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.