VBA code splits source VALUES in different columns destination

[Panos]

I am trying to figure out how can I use 1 column SOURCE from csv and fill 2 columns in the same Access table

The code right now remove the HTML tags from ‘DESCRIPTION’ source and send data to column also called ‘DESCRIPTION’ .



I want to send data in the next column called ‘DESCRIPTION_html’ without removing any value from the source .

Source:
‘DESCRIPTION’(contain HTML)

Destination:
‘DESCRIPTION’(without HTML)
‘DESCRIPTION_HTML’(with HTML)

[Welshgasman]

If you can calculate somèthing, you should not generally store it.

[June7]

Could have code save with HTML tags into long text field formatted for rich text and use PlainText() function to display without HTML tags.

[Panos]

I don’t know how to adjust the code to do that .
I have query mismatching error .
Can I share the code in somewhere?

[June7]

Can post code here. Use CODE tags - the # icon on edit menu.

Can also attach files. Follow instructions at bottom of my post.

[Panos]

Option Compare DatabasePublic Function qryInsertIntoProducts_csv(csvFile As String) As String  sql = ""  sql = sql & " INSERT INTO Products_csv"  sql = sql & "   ( ID"  sql = sql & "   , NAME"    sql = sql & "   , IMAGE1"  sql = sql & "   , IMAGE2"  sql = sql & "   , IMAGE3"  sql = sql & "   , IMAGE4"  sql = sql & "   , IMAGE5"  sql = sql & "   , IMAGE6"  sql = sql & "   , IMAGE7"  sql = sql & "   , IMAGE8"    sql = sql & "   , CATEGORY_1"  sql = sql & "   , CATEGORY_2"  sql = sql & "   , CATEGORY_3"  sql = sql & "   , PVD"  sql = sql & "   , BRAND"    sql = sql & "   , EAN13"    sql = sql & "   , WIDTH"  sql = sql & "   , HEIGHT"  sql = sql & "   , DEPTH"  sql = sql & "   , WEIGHT"    sql = sql & "   , ATTRIBUTE1"  sql = sql & "   , ATTRIBUTE2"  sql = sql & "   , VALUE1"  sql = sql & "   , VALUE2"  sql = sql & "   , DESCRIPTION"  sql = sql & "   , CONDITION"  sql = sql & "   )"    sql = sql & " SELECT"  sql = sql & "     ID"  sql = sql & "   , NAME"    sql = sql & "   , IMAGE1"  sql = sql & "   , IMAGE2"  sql = sql & "   , IMAGE3"  sql = sql & "   , IMAGE4"  sql = sql & "   , IMAGE5"  sql = sql & "   , IMAGE6"  sql = sql & "   , IMAGE7"  sql = sql & "   , IMAGE8"      sql = sql & "   , Left(CATEGORY,4) AS CATEGORY_1"  sql = sql & "   , Mid(CATEGORY,6,4) AS CATEGORY_2"  sql = sql & "   , Right(CATEGORY,4) AS CATEGORY_3"  sql = sql & "   , PVD"  sql = sql & "   , BRAND"    sql = sql & "   , EAN13"    sql = sql & "   , WIDTH"  sql = sql & "   , HEIGHT"  sql = sql & "   , DEPTH"  sql = sql & "   , WEIGHT"    sql = sql & "   , ATTRIBUTE1"  sql = sql & "   , ATTRIBUTE2"  sql = sql & "   , VALUE1"  sql = sql & "   , VALUE2"  sql = sql & " , "  sql = sql & " Trim("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace([DESCRIPTION],'<br>',' ')"  sql = sql & " , '<b>',' ')"  sql = sql & " , '</b>',' ')"  sql = sql & " , '<ul><li>',' ')"  sql = sql & " , '</li><li>',' ')"  sql = sql & " , '</li></ul>',' ')"  sql = sql & " , '</li>',' ')"  sql = sql & " , '<p>',' ')"  sql = sql & " , '</p>',' ')"  sql = sql & " , '<strong>',' ')"  sql = sql & " , '</strong>',' ')"  sql = sql & " ,'<p dir=""ltr"">',' ')"  sql = sql & " )"  sql = sql & " "  sql = sql & "   , CONDITION"  sql = sql & " FROM"  sql = sql & "   " & csvFile    qryInsertIntoProducts_csv = sqlEnd Function

[Welshgasman]

Option Compare DatabasePublic Function qryInsertIntoProducts_csv(csvFile As String) As String  sql = ""  sql = sql & " INSERT INTO Products_csv"  sql = sql & "   ( ID"  sql = sql & "   , NAME"    sql = sql & "   , IMAGE1"  sql = sql & "   , IMAGE2"  sql = sql & "   , IMAGE3"  sql = sql & "   , IMAGE4"  sql = sql & "   , IMAGE5"  sql = sql & "   , IMAGE6"  sql = sql & "   , IMAGE7"  sql = sql & "   , IMAGE8"    sql = sql & "   , CATEGORY_1"  sql = sql & "   , CATEGORY_2"  sql = sql & "   , CATEGORY_3"  sql = sql & "   , PVD"  sql = sql & "   , BRAND"    sql = sql & "   , EAN13"    sql = sql & "   , WIDTH"  sql = sql & "   , HEIGHT"  sql = sql & "   , DEPTH"  sql = sql & "   , WEIGHT"    sql = sql & "   , ATTRIBUTE1"  sql = sql & "   , ATTRIBUTE2"  sql = sql & "   , VALUE1"  sql = sql & "   , VALUE2"  sql = sql & "   , DESCRIPTION"  sql = sql & "   , CONDITION"  sql = sql & "   )"    sql = sql & " SELECT"  sql = sql & "     ID"  sql = sql & "   , NAME"    sql = sql & "   , IMAGE1"  sql = sql & "   , IMAGE2"  sql = sql & "   , IMAGE3"  sql = sql & "   , IMAGE4"  sql = sql & "   , IMAGE5"  sql = sql & "   , IMAGE6"  sql = sql & "   , IMAGE7"  sql = sql & "   , IMAGE8"      sql = sql & "   , Left(CATEGORY,4) AS CATEGORY_1"  sql = sql & "   , Mid(CATEGORY,6,4) AS CATEGORY_2"  sql = sql & "   , Right(CATEGORY,4) AS CATEGORY_3"  sql = sql & "   , PVD"  sql = sql & "   , BRAND"    sql = sql & "   , EAN13"    sql = sql & "   , WIDTH"  sql = sql & "   , HEIGHT"  sql = sql & "   , DEPTH"  sql = sql & "   , WEIGHT"    sql = sql & "   , ATTRIBUTE1"  sql = sql & "   , ATTRIBUTE2"  sql = sql & "   , VALUE1"  sql = sql & "   , VALUE2"  sql = sql & " , "  sql = sql & " Trim("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace("  sql = sql & " Replace([DESCRIPTION],'<br>',' ')"  sql = sql & " , '<b>',' ')"  sql = sql & " , '</b>',' ')"  sql = sql & " , '<ul><li>',' ')"  sql = sql & " , '</li><li>',' ')"  sql = sql & " , '</li></ul>',' ')"  sql = sql & " , '</li>',' ')"  sql = sql & " , '<p>',' ')"  sql = sql & " , '</p>',' ')"  sql = sql & " , '<strong>',' ')"  sql = sql & " , '</strong>',' ')"  sql = sql & " ,'<p dir=""ltr"">',' ')"  sql = sql & " )"  sql = sql & " "  sql = sql & "   , CONDITION"  sql = sql & " FROM"  sql = sql & "   " & csvFile    qryInsertIntoProducts_csv = sqlEnd Function

Don't put all the code on line.
Worse than useless like that.

Also start using Option Explicit in every module.
Tools/Options/Require variable declaration.
However that will only work on newmodules, you need to add manually for existing modules that do not have it.

[June7]

Why is code all on one line and weird font? Copy/Paste from VBA should not do that.

If you don't want to lose the HTML tags, don't do the Replace() operation. All this:

sql = sql & " Trim(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace(" sql = sql & " Replace([DESCRIPTION],'<br>',' ')" sql = sql & " , '<b>',' ')" sql = sql & " , '</b>',' ')" sql = sql & " , '<ul><li>',' ')" sql = sql & " , '</li><li>',' ')" sql = sql & " , '</li></ul>',' ')" sql = sql & " , '</li>',' ')" sql = sql & " , '<p>',' ')" sql = sql & " , '</p>',' ')" sql = sql & " , '<strong>',' ')" sql = sql & " , '</strong>',' ')" sql = sql & " ,'<p dir=""ltr"">',' ')" sql = sql & " )" sql = sql & " " sql = sql &

can simply be:

sql = sql & " [DESCRIPTION]"

On the other hand, if you do want to lose HTML tags, don't use Replace(), use PlainText() function.

sql = sql & " PlainText([DESCRIPTION])"

Adjust your code to save either or both.

[Micron]

It's no better when done the right way. I've done that and posted here but I have to get back to work. I'll leave the commentary about the code to others. Then again, maybe the way it got posted doesn't reflect how it really looks, but if there is a sql = sql line for every variable or field, that's just nuts.
I've removed many double and quadruple spaces at the ends of the lines (and maybe a few in between) but that's all I've got time for. I have to get back to work.

Option Compare Database

Public Function qryInsertIntoProducts_csv(csvFile As String) As String 
sql = ""
sql = sql & "INSERT INTO Products_csv"
sql = sql & "  ( ID"
sql = sql & "   ,NAME"
sql = sql & "   ,IMAGE1"
sql = sql & "   ,IMAGE2"
sql = sql & "   ,IMAGE3"
sql = sql & "   ,IMAGE4"
sql = sql & "   ,IMAGE5"
sql = sql & "   , IMAGE6"  
sql = sql & "   , IMAGE7"  
sql = sql & "   , IMAGE8"    
sql = sql & "   , CATEGORY_1"
sql = sql & "   , CATEGORY_2"
sql = sql & "   , CATEGORY_3"
sql = sql & "   , PVD"
sql = sql & "   , BRAND"
sql = sql & "   , EAN13"
sql = sql & "   , WIDTH"
sql = sql & "   , HEIGHT"
sql = sql & "   , DEPTH"
sql = sql & "   , WEIGHT"
sql = sql & "   , ATTRIBUTE1"
sql = sql & "   , ATTRIBUTE2"
sql = sql & "   , VALUE1"
sql = sql & "   , VALUE2"
sql = sql & "   , DESCRIPTION"
sql = sql & "   , CONDITION"
sql = sql & "   )"
sql = sql & " SELECT"
sql = sql & "     ID"
sql = sql & "   , NAME"
sql = sql & "   , IMAGE1"
sql = sql & "   , IMAGE2"
sql = sql & "   , IMAGE3"
sql = sql & "   , IMAGE4"
sql = sql & "   , IMAGE5"
sql = sql & "   , IMAGE6"
sql = sql & "   , IMAGE7"
sql = sql & "   , IMAGE8"
sql = sql & "   , Left(CATEGORY,4) AS CATEGORY_1"
sql = sql & "   , Mid(CATEGORY,6,4) AS CATEGORY_2"
sql = sql & "   , Right(CATEGORY,4) AS CATEGORY_3"
sql = sql & "   , PVD"  sql = sql & "   , BRAND"
sql = sql & "   , EAN13"    sql = sql & "   , WIDTH"
sql = sql & "   , HEIGHT"  sql = sql & "   , DEPTH"
sql = sql & "   , WEIGHT"    sql = sql & "   , ATTRIBUTE1"
sql = sql & "   , ATTRIBUTE2"  sql = sql & "   , VALUE1"
sql = sql & "   , VALUE2"  sql = sql & " , "  sql = sql & " Trim("
sql = sql & " Replace("
sql = sql & " Replace("
sql = sql & " Replace("
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace(" 
sql = sql & " Replace([DESCRIPTION],'<br>',' ')" 
sql = sql & " , '<b>',' ')" 
sql = sql & " , '</b>',' ')" 
sql = sql & " , '<ul><li>',' ')" 
sql = sql & " , '</li><li>',' ')" 
sql = sql & " , '</li></ul>',' ')" 
sql = sql & " , '</li>',' ')" 
sql = sql & " , '<p>',' ')" 
sql = sql & " , '</p>',' ')" 
sql = sql & " , '<strong>',' ')" 
sql = sql & " , '</strong>',' ')" 
sql = sql & " ,'<p dir=""ltr"">',' ')" 
sql = sql & " )" sql = sql & " " 
sql = sql & "   , CONDITION" 
sql = sql & " FROM" 
sql = sql & "   " & csvFile  
qryInsertIntoProducts_csv = sql

End Function

[Minty]

Wow - that's very long winded to say the least, and I'm a fan of the strSQL = strSQL & ".... construct rather than continuation characters.

That replace statement is just ridiculous.
Why not just create a function to do that with an array of values to look up? There are a ton of examples out there.

[June7]

As already noted, alternative to Replace() is PlainText() and no additional VBA. But since OP wants HTML tags, don't do anything in the import to modify. Save data with HTML tags and use PlainText() in query or textbox.