We help IT Professionals succeed at work.

Importing text holding links fra a CSV file to MySQL

Peter Kroman
Peter Kroman asked
on
125 Views
Last Modified: 2018-12-14
Hi,

I have an Excel CSV file with appr. 40.000 lines. This file contains in one column text holding links which I need to use in SQL.
For example:
Sk-rmbillede-2017-11-18-12.58.32.png
When I import the CSV file to MySQL this column is imported as raw text. The field type i use for this column is VARCHAR.

My question is if there is any way that I can import this file in a way so the links embedded in the text gets active and working?
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>> My question is if there is any way that I can import this file in a way so the links embedded in the text gets active and working?

Links active means ? - it will be simple text only. If you select the data from the table it will simple text only.
Peter KromanSales Executive

Author

Commented:
Links active means that the links are working as links. And my question is if there is any way I can avoid to manually edit appr. 40.000 lines
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>>Links active means that the links are working as links.

No. This is not possible. If you just select the data

And my question is if there is any way I can avoid to manually edit appr. 40.000 lines

Not clear ?? Where are you editing - in the database ?? With proper access one can edit the column.
Peter KromanSales Executive

Author

Commented:
I am editing in the Excel file and importing it to MySQL.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Yes we can edit the file since it is in our hands. If you dont want anyone else to edit the you can protect that using password protection.
And if you dont want anyone else to edit the column in DB then you can have to handle its access.
Peter KromanSales Executive

Author

Commented:
I think we are talking in opposite directions. This is not about access limitation ot about where things are edited. This is about avoiding the need to manually edit appr. 40.000 lines, no matter if the editing is in the database or in a CSV file which is imported afterwords :)
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What editing you need ?
Peter KromanSales Executive

Author

Commented:
I need to change the text to a link. For example:

In the field is says: Sk-rmbillede-2017-11-18-14.10.23.pngEmbedded in that text is this link:
https://www.sa.dk/ao-soegesider/da/billedviser?bsid=10706

and it is the link I need to use and not the text.
CERTIFIED EXPERT

Commented:
You can add this function and extract address from hyperlink:
Function extractURL(cell As Range) As String
    extractURL = cell.Hyperlinks(1).Address
End Function

Open in new window

extract_url.xlsm
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Peter KromanSales Executive

Author

Commented:
Thanks to Als315 and Kumar,

@Kumar - your excel makro was just what I was searching for. It works perfectly. Thanks a lot :)
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome. Glad to help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.