Link to home
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

asked on

Adding field while exporting data from Excel to MySQL

While exporting data from Excel to MySQL I would like to add a field to the data which contains the tabname.

I can do it by adding a field in the worksheet but was wondering if there is a direct method so that I do not have to change all my sheets.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

I can do it by adding a field in the worksheet but was wondering if there is a direct method so that I do not have to change all my sheets.

some kind of automation need to be done, and below some of your considerations to determine what solution to be applied:

1) how frequent the additional field's data is changing?
2) Is this additional field's data can be retrieved from some other source system(s)?
Avatar of Saqib Husain

ASKER

I can do a macro myself. What I wanted to know is that if there is a command (sql or whatever) which can do it without adding a field.

If you read the question the field contains the sheet tab name.
even we can do it in MS SQL, we need to tell the engine what fields need to load with what sort of data.

in SQL Server, it depends on how frequent you want to import your data, you may consider to have these:

1) ETL (Extraction, Transform and Load) architecture, either you can write your importing scripts in pure SQL or in SSIS, for examples.

2) write some triggers to define the logic and then auto-populate the "additional" fields.

if you prefer to do the automation outside of MS SQL, then perhaps can try:

1) Macro (as what you have mentioned) + scheduled task
2) do it with RPA

EDITED: sorry as I misread your question as you mentioned it will be MySQL not MS SQL. my bad for this but some concept applied
Can it be done from within excel?

If not then SQL will work

I need to add a field (column), let's call it Sheet. The data for every row shall be the sheet name. The data does not contain this column.

I need to add a field (column), let's call it Sheet. The data for every row shall be the sheet name. The data does not contain this column.

how you actually doing the importing to MySQL? via some DBMS tool wizard, or a custom write program, etc?

the additional task of adding new column (with sheet's name) need to be done prior to this process.
"MySQL for Excel"
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
You know you can automate adding the sheet name and/or the workbook name using the Power Query option within Excel.  If you watch this video starting at about 2 minutes it will lead you through that process.  I am assuming this is an ongoing process with many tab names and that these change often.

https://www.youtube.com/watch?v=rSQwZ1d3b1g&list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1&index=5
Tom, Does this mean I have to add a column in excel sheet?
Not sure I get what you are trying to do, you can run
Alter table tablename and add a column, make sure to set a default value if not included to avoid issues when data is pushed from excel that does not have the column in question.

Excel is the front end interface to the MySQL data?
No, the adding is automated. Take a look at the video.
arnold,my sheet has (say) four fields. I need to send 5 fields where the value of the fifth field is the sheet name which I do not want to add to the data on the sheet.

Tom, automated or manual, it has to be done which is what I was trying to avoid. I can automate it myself much easily using VBA.
So when you do that with Power Query, the original sheet does not change.  It would still have 4 fields.  The output of the Power Query would have 5 fields shown in an Excel table.  If you do this over and over, the source data will only have the 4 fields, but the output (which I am guessing could be you source to MySQL would contain the 5 fields.
Tom, since I am not familiar with Power Query, I am uploading a faked sample file. Could you help me with the Power Query such that I can use it to export to MySQL? Note that every sheet has a different number of columns. Even the columns could be in a different sequence.

I thought I would be able to do it in VBA but although it can be done, I learnt that i would have to send the data line-by-line. I do not like this idea.

If Power Query is able to setup the data in a form that I could send the data table in one command then it would be a better option.

Xl2Mysql.xlsx
Syed - Are you wanting Sheet1,2,3 in a column along with the data?  Right?
Yes, the field names are in the first row. They are not always in the same order. So, Column1 data for all sheets should be in the same column. Note that the columns are not in the same order.
Much sends on your setup and what you are trying to achive.
It might be as your use has evolved, that excel is not an optimal solution for you.

It seems strange to me. OU are trying to add data on the excel side to then push the update into the DB MySQL in this case...
Never mind. I did it all manually.

The data was extracted from various web sites into excel. Since the data size is expected to be large, needed a way to conveniently send it to MySQL (which I have started to learn).
If you are scripting, powers he'll. Vbscript, python, pho, etc to then go and collect data and populate the data into MySQL..
Day late and a dollar short, but here is what can be done with Power Query with workbooks being saved in one folder.  The X12Mysql is your original file and the X12Mysql is the output with sheet name column.  Also added WorkbookName just to show it can be added.  If you were to do this, you could continue to add like workbooks to this folder and the Summary would capture all the workbooks in the folder.  Just in case you ever need something like this.
Xl2Mysql.xlsx
SummaryMySQL.xlsx