How to Make a CSV with HTML content EXPORTABLE to MYSQL - CSV_LOADDATA [self solved]

[veer-india]

I could not solve the above task, and i wanted to post a query on excel forum. But then i thought lets try once more changing delimiter AND I CAME BACK WITH SOLUTION for MY OWN Task. Anyway here it is (what i typed here before).
-------------------------------------------------------------------------------------------------------------
How to save a CSV with big html content in its cells without enclosing with quotes.




I have very big excel data (have html content in its cells.) to be imported in mysql tables, For this i need to save the excel data in csv format. I can easily save it in utf8, but it doesnot have columns enclosed with quotes, when it try using vbs script to create a csv which have columns enclosed with qoutes i get error because my html also have qoutes. i tried the following vba (written by someone.)

Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
ListSep = Application.International(xlListSeparator)
  If Selection.Cells.Count > 1 Then
    Set SrcRg = Selection
  Else
    Set SrcRg = ActiveSheet.UsedRange
  End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
  CurrTextStr = ìî
For Each CurrCell In CurrRow.Cells
  CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
  CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

the file generated have all data gone here and there, because quotes also present in the html.
then i used th || system by removing """" with || in above code (i am not vba expert)

 CurrTextStr = CurrTextStr & "||" & CurrCell.Value & "||" & ListSep

again the file generated was disorriented.




here i attach two files.
------------------------------------------------------------------------------------------------------
Solution
--------------------------------------------------------------------------------------------------
In control panel regional settings set delimiters some very different character which doesnot appear in your html.
then run this vba.

Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
ListSep = Application.International(xlListSeparator)
  If Selection.Cells.Count > 1 Then
    Set SrcRg = Selection
  Else
    Set SrcRg = ActiveSheet.UsedRange
  End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
  CurrTextStr = ìî
For Each CurrCell In CurrRow.Cells
  CurrTextStr = CurrTextStr & "|" & CurrCell.Value & "|" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
  CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

It will use pipes instead of quotes. while the delimiter set from control panel will be some very different charachter.
i use ` it.
then use the same delimiters and line terminators, field terminators in the ssh command for importing csv in mysql.
for phpmyadmin use the same method. (and happy big importing).