Replace the apostrophes while importing

[elkhazak]

[SOLVED] Replace the apostrophes while importing

Hello !
I have a table Technicians contains : Name | Code tr | Nb tr
I filled it by importing Excel files ; with the following code:

Private Sub cmdImportNoDelete_Click()
    'Unset warnings
    DoCmd.SetWarnings False
 
    'Import spreadsheet
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Techniciens", selectFile, True
 
    DoCmd.SetWarnings True
 
End Sub

I want to use the Instr function to replace the apostrophes if they exist in names with a (blank) or some other letter!
The Technician Name field belongs to the C column!

 

[sneuberg]

Consider the replace function http://www.techonthenet.com/access/functions/string/replace.php

 

[CJ_London]

you can't do it with transferspreadsheet unless you edit the excel file instead

instead, create a linked table to the excel workbook/worksheet, then have an append query where you can use the replace function (not the instr function) to replace single quotes with 2 x single quotes e.g.

newfield:replace([names],"'","''")

to append data from the linked table to the destination table

 

[elkhazak]

Consider the replace function

I founded that and also the Instr function ! The problem is that i'm a new in Access and i don't how to use it in thise case !

 

[elkhazak]

you can't do it with transferspreadsheet unless you edit the excel file instead

instead, create a linked table to the excel workbook/worksheet, then have an append query where you can use the replace function (not the instr function) to replace single quotes with 2 x single quotes e.g.

newfield:replace([names],"'","''")

to append data from the linked table to the destination table

Look i appreciate ur help but i'm a new in Access ! Can u explain more !?

 

[CJ_London]

what do you know at the moment?

Do you know how to link to an excel file? - if not, here is a link https://www.youtube.com/watch?v=exE5pHLIZwQ
Do you know how to write an append query? - if not, here is a link
http://www.databasedev.co.uk/append_query.html

if you know these, then it should be straightforward, if not, once you have linked to the excel file and created an append query, come back with the name of the linked table and the fields plus the name of the destination table and its fields plus the sql for your append query we can take it from there.

You are probably using the query builder at the moment. This is just an interface to generate the sql code. The sql code can be found by selecting the sql view (on a dropdown to the left of the ribbon) - copy and paste this into your post

 

[elkhazak]

what do you know at the moment?

Do you know how to link to an excel file? - if not, here is a link
Do you know how to write an append query? - if not, here is a link

if you know these, then it should be straightforward, if not, once you have linked to the excel file and created an append query, come back with the name of the linked table and the fields plus the name of the destination table and its fields plus the sql for your append query we can take it from there.

You are probably using the query builder at the moment. This is just an interface to generate the sql code. The sql code can be found by selecting the sql view (on a dropdown to the left of the ribbon) - copy and paste this into your post

1- I didn't find how to link to an excel file in Access 10 !
2- Can u just give a virtual exemple virtual names !
it's hard to me to understand tecniques stuff in english i speak french !

 

[MarlaC]

1- I didn't find how to link to an excel file in Access 10 !
2- Can u just give a virtual exemple virtual names !
it's hard to me to understand tecniques stuff in english i speak french !

Votre message n'est pas poli. Utiliser Google Translate si vous ne comprenez pas. Voici est la version 2010: https://www.youtube.com/watch?v=HiLu6icfuhM

 

[elkhazak]

Votre message n'est pas poli. Utiliser Google Translate si vous ne comprenez pas. Voici est la version 2010:

What did i say to be impolite !!
And the reason for askin you that is i want to keep the code of importing not to do it manually everytime when i want to import a new Excel file !

 

[CJ_London]

1- I didn't find how to link to an excel file in Access 10 !

see here

for append queries

 

[elkhazak]

see here


for append queries

U mean i should that everytime when i would import a new Excel file ?

 

[CJ_London]

No - review the links I provided

 

[elkhazak]

No - review the links I provided

To link an Excel file u should keep the same directory for the file !!

 

[CJ_London]

if a table is linked it is linked to file with the specified name and in the specified directory. If you move the file to another directory or want to link to another file with the same structure, you can relink.

All this can be done in code but you need to understand the basics first

 

[MarlaC]

What did i say to be impolite !!
And the reason for askin you that is i want to keep the code of importing not to do it manually everytime when i want to import a new Excel file !

Shouting!! is impolite. Demanding is impolite. People here are helping without being paid for their time.

CJ_London is being very helpful. Saying "merci" goes a long way.

You could also achieve what you are trying to do by running the Replace function in an update query on your data after the import.

 

[arnelgp]

call this function before importing, it will replace single apostrophe with space, if found in column c of your workbook. just pass the fullpath and filename+extension of your excel file to import:

also make reference to Microsoft Excel XX.X Object library.

Sub ReplaceApostropheInExcelFile(strExcelFile As String)

    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    
    Set oExcel = New Excel.Application
    oExcel.DisplayAlerts = False
    Set oBook = oExcel.Workbooks.Open(strExcelFile)
    Set oSheet = oBook.Sheets(1)
    
    With oSheet
            .Columns("C:C").Select
    .Range("C:C").Replace What:="'", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
    oExcel.DisplayAlerts = True
    oBook.Save
    oBook.Close
    oExcel.Quit
    Set oSheet = Nothing
    Set oBook = Nothing
    Set oExcel = Nothing
End Sub

 

[elkhazak]

if a table is linked it is linked to file with the specified name and in the specified directory. If you move the file to another directory or want to link to another file with the same structure, you can relink.

All this can be done in code but you need to understand the basics first

The problem is this app will not be mine it will be for multiusers and i want avoid manual operations !
And Thank you a lot for your time and help ( if i said something rude or impolite i didn't mean to say it )

 

[elkhazak]

Shouting!! is impolite. Demanding is impolite. People here are helping without being paid for their time.

CJ_London is being very helpful. Saying "merci" goes a long way.

You could also achieve what you are trying to do by running the Replace function in an update query on your data after the import.

I didn't mean to be impolite or say something rude, maybe is my langage did !
I'm sorry for u and for CJ_London and thank u both !
The problem is i don't know how to use those functions in sql queries ! if u can help

 

[elkhazak]

call this function before importing, it will replace single apostrophe with space, if found in column c of your workbook. just pass the fullpath and filename+extension of your excel file to import:

also make reference to Microsoft Excel XX.X Object library.

I didn't understand this ! (just pass the fullpath and filename+extension of your excel file to import)
+
u mean i make reference to Microsoft Excel XX.X Object library + do this :

Private Sub cmdImportNoDelete_Click()
    'Unset warnings
    DoCmd.SetWarnings False
 
    'Import spreadsheet
[B][COLOR="Red"]Call ReplaceApostropheInExcelFile[/COLOR][/B]
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Techniciens", selectFile, True
 
    DoCmd.SetWarnings True
 
End Sub

 

[elkhazak]

It's only one ligne did all the operation :

CurrentDb.Execute "UPDATE Techniciens SET Techniciens.Nom = Replace([Nom]," & Chr(34) & "'" & Chr(34) & ",'" & "" & "');