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
raceproretiredAsked:
Who is Participating?
 
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
 
John TsioumprisSoftware & Systems EngineerCommented:
An easy solution would be to save Excel as .csv and use MySql Workbench to do the import
1
 
raceproretiredAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John TsioumprisSoftware & Systems EngineerCommented:
Check this tutorial from MySQL.
It seems that HeidiSQL is much easier for this task ("Import/Export" -> "Import Textfile)
1
 
raceproretiredAuthor 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
 
raceproretiredAuthor 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
 
raceproretiredAuthor 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
 
raceproretiredAuthor 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
 
raceproretiredAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.